SumIf for multiple criteria with text string

[soccer504]

Hi,
I am trying to use a SumIF statement (in excel 2003) for multiple criteria where one criteria is a string. I'm trying to sum column C if column A contains the word "car" and column B contains any text with any part of the word "blue". I've tried sumproduct, but I can't get it to recognize my string ("*blue*"). Thanks! =)

 

[somnath_it2006]

Try Sumifs:

=SUMIFS(C1:C6,A1:A6,"car",B1:B6,"*blue*")

 

[Aladin Akyurek]

Hi,
I am trying to use a SumIF statement (in excel 2003) for multiple criteria where one criteria is a string. I'm trying to sum column C if column A contains the word "car" and column B contains any text with any part of the word "blue". I've tried sumproduct, but I can't get it to recognize my string ("*blue*"). Thanks! =)

Try...

=SUMPRODUCT(--($A$2:$A$100="car"),--ISNUMBER(SEARCH("blue",$B$2:$B$100)),$C$2:$C$100)

 

[soccer504]

Thank you! The isnumber search worked! =)

 

[Try Harder]

Thank you Aladin!!! I was ready to jump out the window before the ISNUMBER solution worked for me too.