| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
checking for a string & No.
Hi,
I have a col and i need to check for a text in a cell and i required the count. EX : ABC 115 DEF 245 ADD 515 FAC 576 So here i want to know how many cell contains "A". I required the count. Answer is 3 same way in Nos. col i required how many cell contains "5". Answer is 4 Can any one help me to get it. I can do it in filters == custom == contains == but i need it by formula. -Christ. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
=countif(A:A,"*A*")
-- Regards, Fred "Christopher Naveen" wrote in message ... Hi, I have a col and i need to check for a text in a cell and i required the count. EX : ABC 115 DEF 245 ADD 515 FAC 576 So here i want to know how many cell contains "A". I required the count. Answer is 3 same way in Nos. col i required how many cell contains "5". Answer is 4 Can any one help me to get it. I can do it in filters == custom == contains == but i need it by formula. -Christ. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
=COUNTIF(A1:A100,"*A*")
"Christopher Naveen" wrote: Hi, I have a col and i need to check for a text in a cell and i required the count. EX : ABC 115 DEF 245 ADD 515 FAC 576 So here i want to know how many cell contains "A". I required the count. Answer is 3 same way in Nos. col i required how many cell contains "5". Answer is 4 Can any one help me to get it. I can do it in filters == custom == contains == but i need it by formula. -Christ. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
Hi,
Thanks a lot. Can u pls tell me how to find the Nubmers. I tried the same formula to find the Nos. but is showing as "0" means it is to mapping the values from the col. Pls advise. Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA So my answer shud be "8". Pls help............. - Regards, Christ "Mike H" wrote: =COUNTIF(A1:A100,"*A*") "Christopher Naveen" wrote: Hi, I have a col and i need to check for a text in a cell and i required the count. EX : ABC 115 DEF 245 ADD 515 FAC 576 So here i want to know how many cell contains "A". I required the count. Answer is 3 same way in Nos. col i required how many cell contains "5". Answer is 4 Can any one help me to get it. I can do it in filters == custom == contains == but i need it by formula. -Christ. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
=LEN(A2)-LEN(SUBSTITUTE(A2,"A",""))
-- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
HI David,
Thank u very much. I got it and i requested to check for Nos. also. I tried the same formula for Nos. but it is not working. Pls advise. Ex : 56566665 56566666 Ans : 5 If i want to know how many 5's are ther in that 2 cells what formula shud i apply? "David Biddulph" wrote: =LEN(A2)-LEN(SUBSTITUTE(A2,"A","")) -- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
Hi David,
Pls refer the below example, ABC ABC CDE EFG EFG ABC I have some data's in a col as mentioned above. Now i use countif to count the values. I want to count only "ABC" in that Col and subtract with the total no. of Rows and reject some values in that col. Refer the detail explaination below, I have totally 6 rows. "ABC" = 3, "CDE" = 1, "EFG"=2 I want to know the count of = Total (6 rows) that does not includes "CDE" - "ABC" Ans : (6 - 1) - 3 = 2 Pls help me how to calculate this............ Thanks in advance. -Christ "David Biddulph" wrote: =LEN(A2)-LEN(SUBSTITUTE(A2,"A","")) -- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
So having found how many there are in one cell, we'll leave it as exercise
for the interested reader to work out how many there are in whichever range of cells you are interested in. -- David Biddulph "Christopher Naveen" wrote in message ... HI David, Thank u very much. I got it and i requested to check for Nos. also. I tried the same formula for Nos. but it is not working. Pls advise. Ex : 56566665 56566666 Ans : 5 If i want to know how many 5's are ther in that 2 cells what formula shud i apply? "David Biddulph" wrote: =LEN(A2)-LEN(SUBSTITUTE(A2,"A","")) -- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
David,
Pls help me in this.. Thanks in advance. -Christ "Christopher Naveen" wrote: Hi David, Pls refer the below example, ABC ABC CDE EFG EFG ABC I have some data's in a col as mentioned above. Now i use countif to count the values. I want to count only "ABC" in that Col and subtract with the total no. of Rows and reject some values in that col. Refer the detail explaination below, I have totally 6 rows. "ABC" = 3, "CDE" = 1, "EFG"=2 I want to know the count of = Total (6 rows) that does not includes "CDE" - "ABC" Ans : (6 - 1) - 3 = 2 Pls help me how to calculate this............ Thanks in advance. -Christ "David Biddulph" wrote: =LEN(A2)-LEN(SUBSTITUTE(A2,"A","")) -- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
Assuming the data are in column A:
=COUNTIF(A:A,"<"&"") will give the number of cells with something in them (in your example, 6). =COUNTIF(A:A,"="&"CDE") will give the number of cells containing "CDE" (e.g 1). =COUNTIF(A:A,"="&"ABC") will give the number of cells containing "ABC" (e.g. 3). So to get the answer you require, use =COUNTIF(A:A,"<"&"")-COUNTIF(A:A,"="&"CDE")-COUNTIF(A:A,"="&"ABC") "Christopher Naveen" wrote in message ... David, Pls help me in this.. Thanks in advance. -Christ "Christopher Naveen" wrote: Hi David, Pls refer the below example, ABC ABC CDE EFG EFG ABC I have some data's in a col as mentioned above. Now i use countif to count the values. I want to count only "ABC" in that Col and subtract with the total no. of Rows and reject some values in that col. Refer the detail explaination below, I have totally 6 rows. "ABC" = 3, "CDE" = 1, "EFG"=2 I want to know the count of = Total (6 rows) that does not includes "CDE" - "ABC" Ans : (6 - 1) - 3 = 2 Pls help me how to calculate this............ Thanks in advance. -Christ "David Biddulph" wrote: =LEN(A2)-LEN(SUBSTITUTE(A2,"A","")) -- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
checking for a string & No.
Hi Stephen,
Thank u very much. I got it. Can u pls tell me how can we count the Nos. in count if function. Ex : 55666665 55002422 Ans : 5 Now i need to count how many 5's are ther in both the cells. -Christ "Stephen" wrote: Assuming the data are in column A: =COUNTIF(A:A,"<"&"") will give the number of cells with something in them (in your example, 6). =COUNTIF(A:A,"="&"CDE") will give the number of cells containing "CDE" (e.g 1). =COUNTIF(A:A,"="&"ABC") will give the number of cells containing "ABC" (e.g. 3). So to get the answer you require, use =COUNTIF(A:A,"<"&"")-COUNTIF(A:A,"="&"CDE")-COUNTIF(A:A,"="&"ABC") "Christopher Naveen" wrote in message ... David, Pls help me in this.. Thanks in advance. -Christ "Christopher Naveen" wrote: Hi David, Pls refer the below example, ABC ABC CDE EFG EFG ABC I have some data's in a col as mentioned above. Now i use countif to count the values. I want to count only "ABC" in that Col and subtract with the total no. of Rows and reject some values in that col. Refer the detail explaination below, I have totally 6 rows. "ABC" = 3, "CDE" = 1, "EFG"=2 I want to know the count of = Total (6 rows) that does not includes "CDE" - "ABC" Ans : (6 - 1) - 3 = 2 Pls help me how to calculate this............ Thanks in advance. -Christ "David Biddulph" wrote: =LEN(A2)-LEN(SUBSTITUTE(A2,"A","")) -- David Biddulph "Christopher Naveen" wrote in message ... ... Also pls tell me how can we find that how many A's i have in a cell, Ex : AAAABBN AHHH AQREE AA .... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
| How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
| Checking for the existence of a characted in a string | Excel Discussion (Misc queries) | |||
| Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
| to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
![Christopher Naveen[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
