Sum a range only if certain text in another

Destro - Aug 27, 2009 at 08:31 AM
 RR - Feb 14, 2012 at 02:53 PM
I need a formula to sum the values in a range of cells but only for the cells that have a specific text in a completely different range of cells.

For example,

sum the values for H8:I12
but only if the text in C8:C12 EQUALS "CRM"

I have 5 different text values in order of importance:
Any help would be of great appreciation

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 27, 2009 at 09:25 PM
your information is not complete
I assume the five text strings are in H8 to H12
and in I6 to I12 there are some numbers

now use this formula

This is ana rray formula so INVOKE THIS FORMULA BY HITTING
Destro77 Posts 1 Registration date Wednesday August 26, 2009 Status Member Last seen August 28, 2009
Aug 28, 2009 at 01:09 AM
Thank you very much!

You know I was sure I tried that. Must have made a dumb error in the formula somewhere. It was 4am my time.
I know... excuses, excuses. Knew it was an easy one. Thanks again, much appreciated.
The suggestion above didn't work for me either. Ideally all of the items you add should be in a single column (not H and I) but if you keep things as is try the following:
=sum(vlookup("CRM",C8:i12,5, false),vlookup("CRM",C8:i12,6, false))