how to search for a word then add the column next to it in excel

Question

I have an invoice which I must add up different job descriptions that charge certain amounts. What I'm trying to figure out is, if there is a way to search for a certain word then make it add the row next to all those words in my document together. For example:

>   Date  Description  Start Time  End Time Staff THRS   Rate  Amount
>
> 1/5/15    LABOUR        06:30      17:00    3   31.5   $5.50 $173.25
> 1/5/15    FORK          07:00      17:00    1    10    $8.80 $88.00

I want to be albe to search the Rate then tell excel to add the cell next to it on the left (total hours) for that job. As it's a month's invoice so people work many different hours/get paid differently. Then automatically add all the hours the labourer does just by telling excel to search for the rate and adding every cell to the left of it gotether. Which will send that formula to a cell down the bottom called Total hours for forklift/labourer etc. Then the same for the amounts section. I assume it's something to do with SUMIF but cannot work it out.

Thanks in advance, it's greatly appreciated.

Answer 1

I'm not entirely sure I understand you question, but here is my best guess. If you had a lookup table that mapped Job Description to Rate somewhere outside your main spreadsheet working area you could use the Lookup() function to populate the rate for each row.

LOOKUP(lookup_value, lookup_vector, [result_vector])

For your example

LOOKUP(<cell job description is in>, <column containing job descriptions in lookup table>, <column containing rates in lookup table>)

Your lookup table would look like this:

LABOUR | $5.50
FORK   | $8.50
...

Here is the official documentation for lookup: http://support.office.microsoft.com/client/446D94AF-663B-451D-8251-369D5E3864CB?lcid=1033&query=&NS=EXCEL&Version=15&ShowNav=false&helpid=xlmain11.chm60076