Count Cells where text may contain a specific word

  • Thread starter Thread starter Vic
  • Start date Start date
V

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,
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
 
=CONCATENATE(COUNTIF(A10:A500,"*Australia*")," :
",COUNTIF('DFS-Sym'!C90:C2000,"*Australia*"),"(",SUMPRODUCT(--('DFS-Sym'!C90:C2000="*Australia*")),--('DFS-Sym'!J90:J2000="*DSMB*"),")")
 
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
 
Back
Top