| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem about count text
Dear all,
I have a table A B SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is =countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
Try it this way:
=countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05*am, "Kent" wrote: Dear all, I have a table A * * * * * * * * * * *B * * * * * * * * * * * * SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is *=countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
Thank you Pete, it works.
Another case. A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would indicate in C2 to E3. What is the formula please. Kent "Pete_UK" ... Try it this way: =countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05*am, "Kent" wrote: Dear all, I have a table A ??????????*B ????????????SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is ?countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
Glad to hear that the first suggestion worked for you.
I'm sorry, but your second request doesn't make any sense to me. Can you rephrase it, and repost the example so that it doesn't wrap onto new lines each time. Pete On Dec 8, 11:39*am, "Kent" wrote: Thank you Pete, it works. Another case. A * * * * * * * * * *B * * * * * * * * * *C * * * * * * * * * *D E * * * * * * * * * * * * * * * * * * * * * * Jose * * * * * * * *John Roy Game * * * * * AJoseq * * * * * *Game Toy * * * * * * *BJohnRoy * * * * * * * * * * * * * * * *Toy Toy If Column B contain text from C1 to E1, then the content at Column A would indicate in C2 to E3. What is the formula please. Kent "Pete_UK" ... Try it this way: =countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05*am, "Kent" wrote: Dear all, I have a table A ??????????*B ????????????SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is ?countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
A B C D E
Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would appear in C2 to E3. What is the formula please. Hope you understand my question this time. "Pete_UK" ... Glad to hear that the first suggestion worked for you. I'm sorry, but your second request doesn't make any sense to me. Can you rephrase it, and repost the example so that it doesn't wrap onto new lines each time. Pete On Dec 8, 11:39 am, "Kent" wrote: Thank you Pete, it works. Another case. A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would indicate in C2 to E3. What is the formula please. Kent "Pete_UK" ... Try it this way: =countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05 am, "Kent" wrote: Dear all, I have a table A ?????????? B ????????????SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is ?countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
Put this formula in C2:
=IF(ISNUMBER(SEARCH(C$1,$B2)),$A2,"") Copy it across to E2, then copy C2:E2 down as far as you need. Hope this helps. Pete On Dec 8, 4:01*pm, "kent" wrote: A * * * * * * * B * * * * * * * C * * * * * * * D * * * * * * * E * * * * * * * * * * * * * * * * * *Jose * * * * *John * * * * * Roy Game * *AJoseq * * * * Game Toy * * BJohnRoy * * * * * * * * * * * * *Toy * * * * * *Toy If Column B contain text from C1 to E1, then the content at Column A would appear in C2 to E3. What is the formula please. Hope you understand my question this time. "Pete_UK" .... Glad to hear that the first suggestion worked for you. I'm sorry, but your second request doesn't make any sense to me. Can you rephrase it, and repost the example so that it doesn't wrap onto new lines each time. Pete On Dec 8, 11:39 am, "Kent" wrote: Thank you Pete, it works. Another case. A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would indicate in C2 to E3. What is the formula please. Kent "Pete_UK" ? J .... Try it this way: =countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05 am, "Kent" wrote: Dear all, I have a table A ?????????? B ????????????SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is ?countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
Dear Pete
Again You help me solving it. thank you Kent "Pete_UK" ... Put this formula in C2: =IF(ISNUMBER(SEARCH(C$1,$B2)),$A2,"") Copy it across to E2, then copy C2:E2 down as far as you need. Hope this helps. Pete On Dec 8, 4:01 pm, "kent" wrote: A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would appear in C2 to E3. What is the formula please. Hope you understand my question this time. "Pete_UK" ... Glad to hear that the first suggestion worked for you. I'm sorry, but your second request doesn't make any sense to me. Can you rephrase it, and repost the example so that it doesn't wrap onto new lines each time. Pete On Dec 8, 11:39 am, "Kent" wrote: Thank you Pete, it works. Another case. A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would indicate in C2 to E3. What is the formula please. Kent "Pete_UK" ? J ... Try it this way: =countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05 am, "Kent" wrote: Dear all, I have a table A ?????????? B ????????????SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is ?countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
problem about count text
You're welcome, Kent - thanks for feeding back.
Pete On Dec 8, 4:42*pm, "kent" wrote: Dear Pete Again You help me solving it. thank you Kent "Pete_UK" .... Put this formula in C2: =IF(ISNUMBER(SEARCH(C$1,$B2)),$A2,"") Copy it across to E2, then copy C2:E2 down as far as you need. Hope this helps. Pete On Dec 8, 4:01 pm, "kent" wrote: A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would appear in C2 to E3. What is the formula please. Hope you understand my question this time. "Pete_UK" ... Glad to hear that the first suggestion worked for you. I'm sorry, but your second request doesn't make any sense to me. Can you rephrase it, and repost the example so that it doesn't wrap onto new lines each time. Pete On Dec 8, 11:39 am, "Kent" wrote: Thank you Pete, it works. Another case. A B C D E Jose John Roy Game AJoseq Game Toy BJohnRoy Toy Toy If Column B contain text from C1 to E1, then the content at Column A would indicate in C2 to E3. What is the formula please. Kent "Pete_UK" ? J .... Try it this way: =countif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05 am, "Kent" wrote: Dear all, I have a table A ?????????? B ????????????SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is ?countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
| how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
