Countif cell contians a specific number (within a list)

P

porter444

Gurus,

Hope you can help me out once again. You guys/gals ROCK!

I have a worksheet that contains survey results and in one column there are
the results of a ranking exercise. The survey respondant enters a list of
numbers that correspond to the 15 most critical items from a list of 50
items. Each item is designated by a number between 1 - 50.

The entry looks like 1,4,6,18,19,23,44,50...

On another worksheet I need to summarize how many times each of the 50 items
makes it into a top 15 response.

In other words I need to be able to count how many times 1 appears in column
D (not 10,11,12,21,31,41...ect.).

My struggle here is coming up with a way to count if the range contains a
specific value, and then how to exclude non-matches like 10,11,12... etc.

Any help you can provide would be AWESOME.

Thanks,

Scott
 
P

Pranav Vaidya

I think if you use Data-->Text to column option and separate on 'comma (,)',
your current data will be split across the columns and the you should be
easily able to do this.

Hope this helps!!
 
P

porter444

I don't have that option.

Pranav Vaidya said:
I think if you use Data-->Text to column option and separate on 'comma (,)',
your current data will be split across the columns and the you should be
easily able to do this.

Hope this helps!!
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!
Click to expand...
 
R

Ron Coderre

If the lists of 15 critical items are actually contained in one cell:

Example: A1:A6 contains: 1,4,6,18,19,23,44,50
1,4,6,18,19,23,44,50
4,6,18,19,23,44,50
6,18,19,23,44,50
1,4,6,18,19,23,44,50
1,4,6,19,23,44


Then try this:

D1:D50 contains the series 1 through 50

This formula returns the count of D1's value in A1:A6
E1:
=SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<0))
Copy that formula down through E50

Using the example:
1 occurs 4 times
2 occurs 0 times
3 occurs 0 times
4 occurs 5 times
etc

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
J

JE McGimpsey

One way (assuming only one entry per cell):

With the desired value in A1, and the list in Data!D:D:

=COUNTIF(DATA!D:D,A1&",*") + COUNTIF(DATA!D:D,"*," & A1 & ",*") +
COUNTIF(DATA!D:D, "*," & A1) + COUNTIF(DATA!D:D,A1)
 
D

Don Guillett

I'm not sure I understand what you want but have you looked in the help
index for COUNTIF
 
P

Peo Sjoblom

That's clever


--


Regards,


Peo Sjoblom




JE McGimpsey said:
One way (assuming only one entry per cell):

With the desired value in A1, and the list in Data!D:D:

=COUNTIF(DATA!D:D,A1&",*") + COUNTIF(DATA!D:D,"*," & A1 & ",*") +
COUNTIF(DATA!D:D, "*," & A1) + COUNTIF(DATA!D:D,A1)
Click to expand...
 
P

porter444

Ron,

This is great, but there is one small gap. The last number in the list
isn't being counted. For example ",50" isn't showing up.

Can you help please sir?

Thanks,

Scott
 
R

Ron Coderre

Using this fomula in E1 (and copied down through E50):
=SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<0))

and these values in A1:A6:

1,4,6,18,19,23,44,50
1,4,6,18,19,23,44,50
,4,6,18,19,23,44,50
6,18,19,23,44,50
1,4,6,18,19,23,44,50
1,4,6,19,23,44

The count returned for 50 (in cell E50) is 5, the correct count.

Are you using the same formula?
Am I missing somethng?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
P

porter444

Strange... it works for 50, but if I add ",3" to the end of each of those
lists it doen't count the 3's.

???
 
R

Ron Coderre

I'm still confused....I'm getting a count for 3's when they're appended to
the list.
In fact if I clear the other strings and only have this in cell A1:
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50

Every formula returns a count of 1.

Do you have any spaces interspersed in the string of values?
I'm assuming the list is computer generated, so I'm not testing for stray
spaces or other characters.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
P

porter444

If you move the 3 to the end of the list like this:
1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,3

If I put another comma after the 3 it counts.
 
R

Ron Coderre

Something's still wrong....Wait a minute!....I was in the middle of a long
post, when it occurred to me....

If the match is the last item in the list...AND it's a single digit
number...AND the matched position is EQUAL to the length of the string...
the match_position less: the length equals zero!

The end of my formula should end with <=0 (instead of: <0 )

Now it works with this:
E1:
=SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<=0))

That was a challenge....thanks for catching the error.

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
R

Ron Coderre

Thanks for the kind words....and thanks for helping me root out the problem!

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Top