Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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*"})) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple Wildcard Question | Excel Discussion (Misc queries) | |||
Wildcard Usage: I absolutely need this to count?/sum? two or more criteri | Excel Discussion (Misc queries) | |||
SUMPRODUCT with Wildcard | Excel Worksheet Functions | |||
Wildcard in Nested functions | Excel Discussion (Misc queries) | |||
VARYING number of sheets in EXCEL | New Users to Excel |