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

Solved

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.

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(ISNUMBE

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,ISN

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

23 Comments

name the ranges A:A and B:B as named ranges:

or reduce the range to just the rows with data:

this will reduce the "calculating time"

Is there any text or #NA in A:A or B:B?

Which version are you on?

A:A is all text. B:B has text header

This works in the first cell I place it, but if I drag it down, I get a Circular Reference error:

{=IF(H2<>"",SUM(IF(ISNUMBE

Have to leave off on this for a short bit. I'll check back in a few hours. Thanks for having a look.

I am not sure why you would get a circular reference.

Will test it here, see if I get the same.

Attached is workbook I am testing.

I am not sure why you would have circular references.

Book1.xlsx

Hey Patrick!

In fact there's no problem using a whole

regards, barry

kicking myself for just "fixing" the original formula and not trying to use sumif :)

live and learn.

Should the starting formula just be a simple Countif... wonder why it isn't.

It appears that I neglected to include a criteria that appears to exclude the SUMIF solution. The reason I was using the ISNUMBER(SEARCH()) combination was to make the search criteria case insensitive.

A little more backstory - I don't think it has any real bearing on the solution, but....

I'm involved in a product data taxonomy/schema development project in order to develop a unified web presentation across several companies within a division of our parent. One factor that we want to weight heavily is the "voice of the consumer" (i.e., how do customers want and expect to see products listed). One method to do this is to analyze search logs from our existing web site structures. This is where this issue picks up.

I have 12-month list of search terms, along with a frequency (count) that they have been entered. I'm trying to build a Search Term Aggregator within the spreadsheet that will let me develop a clearer picture of how customers are searching. Column A contains the search terms. Column B contains a count of the number of times a particular search term was entered. E.g., A18: "SOCKET HEAD CAP SCREWS"; B18: 556)

In column H, I'm listing one or more search terms (e.g., H2: "Socket head"; H3: "socket+head", etc.). In Column I, I'm displaying the count of the number of cells that the contain the search term (case insensitive). In Column J, I want to show the sum of the values in B for each cell that matches the search term.

I'll post a scrubbed version of the spreadsheet shortly.

Works: =IF(H2<>"",SUM(IF(ISNUMBER

Fails: =IF(H2<>"",SUM(IF(ISNUMBER

At this point, I think I'll just chunk up the arithmetic into 125,000 row segments and add the results and award full points to Barman (unless anyone has any objections or other ideas).

Still curious as to why the formula breaks down after 125,000 records, but moving on.

Thanks for the attention and help.

Mark

I'm not really clear why SUMIF wouldn't be the best solution here as per my suggestion

=IF(H2="","",SUMIF(A:A,"*"

SUMIF, like most Excel functions (SUBSTITUTE, FIND, EXACT and possibly others excepted) is

If you were dealing with numeric values then SUMIF with wildcards might be problematic but as you seem to be dealing with text values I don't see a downside.

Note that with this formula:

=IF(H2<>"",SUM(IF(ISNUMBER

the wildcards in SEARCH are superfluous (SEARCH searches for the search value anywhere in the cell in any case), so this version should be sufficient

=IF(H2<>"",SUM(IF(ISNUMBER

regards, barry

As you indicate, it does not have a problem with processing the entire range A:A and B:B.

Point noted concerning the case-insensitivity of SUMIF (and the other functions you noted). Thanks for the additional education in this regard.

I'll request a points adjustment.

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Calculating tiered commission based on plan name and amount | 7 | 32 | |

copy and paste multiple rows on autofiltered data not working | 4 | 25 | |

vba code not trapping out null filters | 5 | 27 | |

List unique text values in a column | 1 | 10 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!