Countif using Wildcard * and number 1
 

I've got a column of letters and numbers that I want to count. For
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 try
to use formula =COUNTIF(I508:I512,"=*1*") It doesn't count the
instances where 1 is all by itself. It only counts 1 when it is with
another letter.

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

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

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

thanks, mike


--
southdaytona
------------------------------------------------------------------------
southdaytona's Profile: http://www.excelforum.com/member.php...o&userid=28529
View this thread: http://www.excelforum.com/showthread...hreadid=481830

Countif using Wildcard * and number 1
 

Try

=SUMPRODUCT(--(ISNUMBER(FIND("1",I508:I512))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"southdaytona"
wrote in message
news:southdaytona.1xx9uz_1131023703.6391@excelforu m-nospam.com...

I've got a column of letters and numbers that I want to count. For
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 try
to use formula =COUNTIF(I508:I512,"=*1*") It doesn't count the
instances where 1 is all by itself. It only counts 1 when it is with
another letter.

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

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

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

thanks, mike


--
southdaytona
------------------------------------------------------------------------
southdaytona's Profile:
http://www.excelforum.com/member.php...o&userid=28529
View this thread: http://www.excelforum.com/showthread...hreadid=481830

Countif using Wildcard * and number 1
 

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

mike


--
southdaytona
------------------------------------------------------------------------
southdaytona's Profile: http://www.excelforum.com/member.php...o&userid=28529
View this thread: http://www.excelforum.com/showthread...hreadid=481830

Countif using Wildcard * and number 1
 

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:


I've got a column of letters and numbers that I want to count. For
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 try
to use formula =COUNTIF(I508:I512,"=*1*") It doesn't count the
instances where 1 is all by itself. It only counts 1 when it is with
another letter.

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

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

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

thanks, mike


--
southdaytona
------------------------------------------------------------------------
southdaytona's Profile: http://www.excelforum.com/member.php...o&userid=28529
View this thread: http://www.excelforum.com/showthread...hreadid=481830

Countif using Wildcard * and number 1
 

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

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*"}))