Countif using Wildcard * and number 1

S

southdaytona

I've got a column of letters and numbers that I want to count. Fo
example the column could look like this:

a,1
1
1
b,1
b
a

What I want to do is count all the times that 1 shows up. When I tr
to use formula =COUNTIF(I508:I512,"=*1*") It doesn't count th
instances where 1 is all by itself. It only counts 1 when it is wit
another letter.

What I've resorted to is a forumula that looks like this.

=SUM(COUNTIF(G2:G431,"=1"),COUNTIF(G2:G431,"*1"),COUNTIF(G2:G431,"=1*"))

Basically it has all the possible options and then adds them together.
But why doesn't the wildcard feature work?

thanks, mik
 
S

southdaytona

Thanks! that worked. I wonder why the wildcards didn't work though? Oh
well thanks for your help!

mike
 
A

Ashish Mathur

Hi,

You may try this array formula (Ctrl+Shift+Enter)

SUM(IF(ISNUMBER(FIND("1",$I$502:$I$508)),1,0))

Regards,

Ashish Mathur
 
H

Harlan Grove

southdaytona wrote...
Thanks! that worked. I wonder why the wildcards didn't work though? Oh
well thanks for your help!
Click to expand...

Wildcards only work for cells containing text. They don't work for
cells containing numbers.

Another alternative would have been

=SUM(COUNTIF(A1:A6,{1,"*1*"}))
 
Top