hLookup

[Chris3177]

I want excel to look up a certain value in a row and for each cell that has the value I want it to sum the corresponding numerical values.

For example:
C/R A B C D E F
1 Director CRA Mng CRA CRA Mng
2
3
4
5
6 1.4 1.3 1.0 .08 .98 .78

The formula should look up "CRA" in row 1 and then sum all of the corresponding values in row 6 i.e. 1.3+.08+.98 = 2.36

I tried a hlookup, but don't seem to know how to use the formula to look for multiple values of the same and then add them together???

Thank you!

 

[markmzz]

I've loooked up and found this solution which seems similar to whatI'm chasing...
Below is my version which doesn't work either and I cant figure why?
Bare in mind that my formula is spread over two different pages in my spreadsheet.
At a glance, do you see anything that sticks out that could be incorrect?

Thanks for your help.

=SUMIF(('BOQ List'!$O6:$O13006),("'Module List'!$D5"),('BOQ List'!$BI6:$BI13006))

Look at this (" - in Red) in your formula:

=SUMIF(('BOQ List'!$O6:$O13006),("'Module List'!$D5"),('BOQ List'!$BI6:$BI13006))

Markmzz

 

[racingsnake2000]

Hey mark,
Thanks for your help anyways.
Sorry, haven't been in here for a while.
I ended up doing it the long way in the end. What was the idea with the " being red anyways?

 

[markmzz]

Look at this (" - in Red) in your formula:

=SUMIF(('BOQ List'!$O6:$O13006),("'Module List'!$D5"),('BOQ List'!$BI6:$BI13006))

Markmzz

Hey mark,
Thanks for your help anyways.
Sorry, haven't been in here for a while.
I ended up doing it the long way in the end. What was the idea with the " being red anyways?

It's a type error in your formula. Look at this:

Your formula (with the error):

=SUMIF(('BOQ List'!$O6:$O13006),("'Module List'!$D5"),('BOQ List'!$BI6:$BI13006))

The correct formula (without the error):

=SUMIF(('BOQ List'!$O6:$O13006),('Module List'!$D5),('BOQ List'!$BI6:$BI13006))

Markmzz