Sumif with multiple criteria of text containing certain words

[lavidalocas]

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!

[Jonmo1]

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))

[lavidalocas]

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

This is going to save me so much time!

[dflak]

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

[lavidalocas]

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?

[Jonmo1]

You're welcome.

[dflak]

Yes, that is how I interpreted the request.

[TudyBTH]

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

Try this CSE formula

=IFERROR(SUMPRODUCT((MMULT(IFERROR(SEARCH(TRANSPOSE(criteria),$A$3:$A$1510),0),(ROW(criteria)/ROW(criteria)))>0)*$B$3:$B$1510),"")

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

=OFFSET(Sheet3!F$3,,,COUNTA(Sheet3!F$3:F$50),1)

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

[Jonmo1]

@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.

[TudyBTH]

@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).

[Jonmo1]

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?