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,3 7,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)


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

???

"Ron Coderre" wrote:

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