Count Cells where text may contain a specific word
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(ISNUM BER(SEARCH("Australia",'DFs-Sym'!C90:C2000,1))*(ISNUMBER(SEARCH("DSMB",'DFs-Sym'!J90:J2000,1))))&")"
Mike
Mike
"Vic" wrote:
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
"Mike H" wrote:
Try,
=COUNTIF(G2:G2000,"*DSMB*")
Mike
"Vic" wrote:
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.
|