I am trying to use countif to count the number of 6's in a list of numbers
from 1 to 100 for example. I only get one counted. I want it to count
16,26,36.46.56.60,61,62,63,64 etc. I tried putting a wildcard in the criteria
but it didn't work. I also tried count . Any suggestions would be appreciated

Thanks
Tom

Is this what you mean? Assuming that A2:A10 contains the data...

=SUMPRODUCT(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,6,"")))

Hope this helps!

http://www.xl-central.com

In article ,
Tom wrote:

I am trying to use countif to count the number of 6's in a list of numbers
from 1 to 100 for example. I only get one counted. I want it to count
16,26,36.46.56.60,61,62,63,64 etc. I tried putting a wildcard in the criteria
but it didn't work. I also tried count . Any suggestions would be appreciated

Thanks
Tom

Thanks a million!


"Tom" wrote:

I am trying to use countif to count the number of 6's in a list of numbers
from 1 to 100 for example. I only get one counted. I want it to count
16,26,36.46.56.60,61,62,63,64 etc. I tried putting a wildcard in the criteria
but it didn't work. I also tried count . Any suggestions would be appreciated

Thanks
Tom

Thnks a million!

"Niek Otten" wrote:

Next to the column with the figures, enter this formula:

=LEN(A1)-LEN(SUBSTITUTE(A1,"6",""))
and fill down as far as you need. Then SUM this column

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tom" wrote in message
...
I am trying to use countif to count the number of 6's in a list of numbers
from 1 to 100 for example. I only get one counted. I want it to count
16,26,36.46.56.60,61,62,63,64 etc. I tried putting a wildcard in the
criteria
but it didn't work. I also tried count . Any suggestions would be
appreciated

Thanks
Tom

Thanks a million

"Domenic" wrote:

Is this what you mean? Assuming that A2:A10 contains the data...

=SUMPRODUCT(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,6,"")))

Hope this helps!

http://www.xl-central.com

In article ,
Tom wrote:

I am trying to use countif to count the number of 6's in a list of numbers
from 1 to 100 for example. I only get one counted. I want it to count
16,26,36.46.56.60,61,62,63,64 etc. I tried putting a wildcard in the criteria
but it didn't work. I also tried count . Any suggestions would be appreciated

Thanks
Tom

Hi

What if you have 66 in your range? Do you want to count it as 1 or 2?
If 2, the above solutions works well.

otherwise, try this array formula, confirm by Ctrl,Shift and Enter
=SUM(IF(LEN(E19:E31)-LEN(SUBSTITUTE(E19:E31,6,"")),1))

this will count 66 as 1 occurrence rather than 2


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Tom" wrote:

I am trying to use countif to count the number of 6's in a list of numbers
from 1 to 100 for example. I only get one counted. I want it to count
16,26,36.46.56.60,61,62,63,64 etc. I tried putting a wildcard in the criteria
but it didn't work. I also tried count . Any suggestions would be appreciated

Thanks
Tom