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

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!!


"porter444" wrote:

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

I don't have that option.

"Pranav Vaidya" wrote:

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!!


"porter444" wrote:

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

Would you care to tell us why you don't have that option?
--
David Biddulph

"porter444" wrote in message
...
I don't have that option.

"Pranav Vaidya" wrote:

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!!

"porter444" wrote:

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

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

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

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

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

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

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)



In article ,
porter444 wrote:

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

That's clever


--


Regards,


Peo Sjoblom




"JE McGimpsey" wrote in message
...
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)



In article ,
porter444 wrote:

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

AWESOME! Thanks to all who responded.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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