Sum a range only if certain text in another

Closed
Destro - Aug 27, 2009 at 08:31 AM
 RR - Feb 14, 2012 at 02:53 PM
Hello,
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:
LFIP
FIP
ERM
CRM
CIP
Any help would be of great appreciation

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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

=SUM(IF(H8:H12="CRM",I8:I12))
This is ana rray formula so INVOKE THIS FORMULA BY HITTING
CONTROL+SHIFT+ENTER.
43
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.
0
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))
0