Count Cells where text may contain a specific word
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. |
Count Cells where text may contain a specific word
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. |
Count Cells where text may contain a specific word
=sumproduct(--(instr(G2:G2000,"DSMB")0))
"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. |
Count Cells where text may contain a specific word
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. |
Count Cells where text may contain a specific word
=CONCATENATE(COUNTIF(A10:A500,"*Australia*")," :
",COUNTIF('DFS-Sym'!C90:C2000,"*Australia*"),"(",SUMPRODUCT(--('DFS-Sym'!C90:C2000="*Australia*")),--('DFS-Sym'!J90:J2000="*DSMB*"),")") "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. |
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. |
All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com