Solved

# SUMIF help

Posted on 2012-08-10

Looking for some help with a SUMIF formula

Given:

A B

--------------

This 10

That 11

Or 15

The 20

Other 25

I have a formula that counts the number of times a string (H2) appears within a range (using the CTRL+SHIFT+ENTER array formula):

{=IF(H2<>"",SUM(IF(ISNUMBER(SEARCH("*"&H2&"*",A:A)),1,0)),"")}

This works fine. If I enter "the" in H2, it returns a count of 2 (cells A4 & A5 contain this string). A search value of "th" returns a count of 4 (A1, A2, A4 & A5).

What I'm looking for help with is a related formula. I want to sum the values found in column B if a cell is included in the count above. So, for the first example - a search value of "the" would return a sum of 45 (20+25). A search of "th" would return a sum of 66 (10+11+20+25).

I tried the following, but I'm guessing the syntax isn't correct:

{=SUMIF($A$2:$A$250000,ISNUMBER(SEARCH("*"&H2&"*",A:A))&"=TRUE",$B$2:$B$250000)}

I'd prefer not to use VBA if possible, but can if that's the only way.