checking for a string & No.

[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.

 

[Fred Smith]

=countif(A:A,"*A*")

 

[Mike H]

=COUNTIF(A1:A100,"*A*")

 

[Christopher Naveen]

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

 

[David Biddulph]

=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

....

 

[Christopher Naveen]

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?

 

[Christopher Naveen]

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]

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.

 

[Christopher Naveen]

David,

Pls help me in this..

Thanks in advance.

-Christ

 

[Stephen]

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]

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