checking for a string & No.

  • Thread starter Thread starter Christopher Naveen
  • Start date Start date
C

Christopher Naveen

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.
 
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
 
=LEN(A2)-LEN(SUBSTITUTE(A2,"A",""))
--
David Biddulph

... Also pls tell me how can we find that how
many A's i have in a cell,

Ex :

AAAABBN
AHHH
AQREE
AA
Click to expand...
....
 
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?
 
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
 
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.
 
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")
 
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
 
Back
Top