Sumif with multiple criteria of text containing certain words

Hi all!

I am currently working on a document where I would like to sum the monthly Google searches for certain group of words. For example, for a particular topic like brands, I have a long list of different brands and would like to add the volume searches of all of them.
I am currently using the following formula:

=SUM(SUMIF(A3:A1510,{"*best*","*reviews*","*top*","*good*","*recommended*","*effective*"},B3:B1510))

As some of my lists are very long, I would like to avoid having to type each keywords.
Also, these are not exact match but I am looking for all cells containing those words or expressions.
Attached, you will find my file in case I did not explain it clearly enough!

As I did not find the answer to my question while doing research online, I was hoping maybe you could help?

Many thanks in advance!

Try this for F51
=SUMPRODUCT(SUMIF(A3:A1510,"*"&F3:F20&"*",B3:B1510))

Note, there cannot e any blanks in F3:F20
Unfortunately, that means you need to aqjust that range for the next formula in G51
=SUMPRODUCT(SUMIF(A3:A1510,"*"&G3:G9&"*",B3:B1510))

Thank you so much Jonmo1 , works perfectly!!!!!

This is going to save me so much time!

Originally Posted by dflak

Here's my candidate entered as an array formula: =IF(ISBLANK(F3),"",SUMPRODUCT((ISNUMBER(FIND(F3,$A$3:$A$1510))=TRUE) * 1))

Hi dflak!

Thanks a lot for helping!
If I understand well, this one is to count the frequency of each of the expressions right?

You're welcome.

Hi,
Jonmo1 's formula sums up the same value several times
Capture1.JPG

Try this CSE formula

Formula:

Please Login or Register  to view this content.

where "criteria" name is defined as follows: select cell F2 then, in Formulas TAB ►Name Manager ►Refers to: enter this

Formula:

Please Login or Register  to view this content.

Now, you can drag to right without changes


see attached (in F2 my formula, in F51 Jonmo1 's formula)
Columns C and D are only for verification, it can be removed

@TudyBTH
The formula you show as Jonmo1's formula is NOT the formula I posted.
I think you've misunderstood what the OP wanted the formula to do, and what the formula I posted does.

@Jonmo1
Hi Jonmo1,
Open the file attached, in F51 I put your formula, the same formula of message #2

Try this for F51
=SUMPRODUCT(SUMIF(A3:A1510,"*"&F3:F20&"*",B3:B1510))

because we obtained different results, I created two columns for verification, see columns C & D in attacehed file (msg #8).

I see your point now.
Though you had to change the OP's original data in A to reveal the issue.
This may not be an issue in the OP's real data, given the original formula posted is stated to work as desired.

So yes, it would count/sum a row multiple times if multiple criteria appear in that cell.
However, we don't know if that's undesirable or not. Maybe the OP would want that?