Help needed for lookup and sum

[dzabhi]

Please does anyone have a forumula to perform the below actions in the attached photo spreadsheet:

Lookup the word "Dedert" in column B and then add up the values of the CPMU in D2, D5, D8, etc.
This will also need to work for other names in column B.

Any help would be appreciated!

Capture.PNG

[Jdevil]

A picture does not help much. Could you instead attach a file?

Also, are those merged cells?

It's hard to help since we don't know where you're taking your information, etc

[daffodil11]

=SUMIF(B2:B14,"dedert",D2:D14)
=SUMIFS(D2:D14,B2:B14,"dedert",C2:C14,"CPMU")
=SUMPRODUCT((B2:B14="DEDERT")*(D2:D14))
=SUMPRODUCT((B2:B14="DEDERT")*(C2:C14="CPMU")*(D2:D14))

I'm hoping to find a non-array solution to coerce values with N. Where's Snorlax when you need him?

[nigelbloomy]

This could work if you can get rid of those error cells.

Please Login or Register  to view this content.

[dzabhi]

Sorry about that. Do you need the full file or will a cut and paste of the necessary section suffice? Yes they are merged cells.

[daffodil11]

Maybe a brighter mind can come up with something non-array. I need coffee.

=SUMPRODUCT(--IFERROR((B2:B20="DEDERT")*(C2:C20="CPMU")*(D2:D20),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[dzabhi]

This worked a treat! Thank you so much for your help!

[dzabhi]

Sorry to bother you again.. I've tried to alter the formula slightly to get the same result but with "Complaints" (in C column) instead of "CPMU" and it just returns a 0 figure. Am I missing something or is it not that simple?

Thanks in advance.

[daffodil11]

SUMPRODUCT works by multiply like-amount arrays against one another to check criteria.

You'll need to adjust the array by one cell to achieve the same result.

=SUMPRODUCT(--IFERROR((B2:B20="DEDERT")*(C3:C21="Complaints")*(D3:D21),0))

As long as the ranges contain the same number of cells for this type of comparison, it doesn't matter that they are offset.

B2:B20 = 19 cells
C3:C21 = 19 cells

I've attached a tutorial on SUMPRODUCT to illustrate.

[dzabhi]

You're very kind. Thanks again!