Count Cells where text may contain a specific word

[Vic]

I need to count (text or general) cells G2 thru G2000 if the text contains
the following word DSMB - combination of 4 charachters All Caps. How do I do
this? The word DSVB may be at the beginning, middle or the end of sentence.
No specific position.
Thank you.

 

[Mike H]

Try,

=COUNTIF(G2:G2000,"*DSMB*")

Mike

 

[Joel]

=sumproduct(--(instr(G2:G2000,"DSMB")>0))

 

[Vic]

Mike,
Here is my entire formula. I can't get it to work. Everything is spelled
correctly.

=CONCATENATE(COUNTIF(A10:A500,"*Australia*")," :
",COUNTIF('DFS-Sym'!C90:C2000,"*Australia*"),"
(",SUMPRODUCT(--('DFS-Sym'!C90:C2000,"*Australia*"),--('DFS-Sym'!J90:J2000,"*DSMB*")),")")

The logic behind it: I need counter for all sent items to each country,
counter for received items for that country and how many DSMB items they
received. The result should look like this ==> 200 : 100 (10)
Thank you

 

[Joel]

=CONCATENATE(COUNTIF(A10:A500,"*Australia*")," :
",COUNTIF('DFS-Sym'!C90:C2000,"*Australia*"),"(",SUMPRODUCT(--('DFS-Sym'!C90:C2000="*Australia*")),--('DFS-Sym'!J90:J2000="*DSMB*"),")")

 

[Mike H]

Vic,

Your formula is falling over because your trying to use wildcards in
sumproduct.

Try this and note I'm using & instead of concatenate

=COUNTIF(A10:A500,"*Australia*")&" :
"&COUNTIF('DFs-Sym'!C90:C2000,"*Australia*")&"("&SUMPRODUCT(ISNUMBER(SEARCH("Australia",'DFs-Sym'!C90:C2000,1))*(ISNUMBER(SEARCH("DSMB",'DFs-Sym'!J90:J2000,1))))&")"

Mike

Mike