Countif if some cells contains ...

  • Thread starter Thread starter Pedro
  • Start date Start date
P

Pedro

Hi everyone
Is there any way of counting the number of some cells who
has some values.
Ex: a1 = Padro a2 = Nadro a3 = Fadra
Countif (a1:a3,dr) = 3
Countif (a1:a3,ad) = 3
or
Countif (a1:a3,a) = 4
This way it doesn't work cose i must look for the exact
word. Could be like Countif(a1:a3,Contains"ad") but it
doesn't let me do it.

If i do it with contains of custom in data filter it works
but i need to count it later.

Any suggestions? Anyone?
Tks in advance
Pedro
 
Hi,

Try this array formula (C1 contains the substring you are
looking for):

=SUM((LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C1,"")))/LEN(C1))

Confirm the formula by pressing control-shift-enter in
stead of just enter!!!

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
What if "ad" is a number and i would like to enter it in
another instead of changing the formula everytime ...?
Tks in advance
Pedro
 
Pedro said:
Hi everyone
Is there any way of counting the number of some cells who
has some values.
Ex: a1 = Padro a2 = Nadro a3 = Fadra
Countif (a1:a3,dr) = 3
Countif (a1:a3,ad) = 3
or
Countif (a1:a3,a) = 4
Click to expand...


you can use CSE formulas:
=SUM(1*(NOT(ISERROR(SEARCH(B7,A1:A6)))))
where B7 contains what you search.
ivano
 
Back
Top