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)


"porter444" wrote in message
...
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




"Ron Coderre" wrote:

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)



"porter444" wrote in message
...
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