Countif using Wildcard * and number 1

  • Thread starter Thread starter southdaytona
  • Start date Start date
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
 
Thanks! that worked. I wonder why the wildcards didn't work though? Oh
well thanks for your help!

mike
 
Hi,

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

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

Regards,

Ashish Mathur
 
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*"}))
 
Back
Top