Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count percentage of specific text in a range of cells | Excel Worksheet Functions | |||
I need to count and / or sum cells with specific text colours. Hel | Excel Discussion (Misc queries) | |||
Count cells containing specific text | Excel Discussion (Misc queries) | |||
count cells that begin with specific text | Excel Worksheet Functions | |||
If two cells have specific text to count as 1 | Excel Worksheet Functions |