I am trying to total the occurrences of comma delimited numbers in a column.
See original post below:

I have a column which has a variable number of comma delimited values in
the range of 11 to 56 in each cell. Is there a method or macro to count
the
occurrences of each number in the column and either output the results to
a
file or another worksheet or the same worksheet?

Example:
A1: 23,40,52,31,
A2: 42,14,
A3: 56,
A4: 27,43,19,
etc.

What I am doing now is coping the column to Word, closing the margins to
3
characters, coping that result to an clean worksheet, sorting the new
column ascending and printing the results and counting the occurrences
manually. This is getting tedious as the column grows in length.

Can anyone help me or point me to a solution?


Biff replied with a formula using SUMPRODUCT. See his reply below:

Hi!

Assume the numbers are in the range A1:A10.

In C1 enter 11.

In D1 enter this formula:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(A$1:A$10,C1,"")))/2

Select both C1 and D1 and drag copy down to row 46.

Biff

For the life of me I cannot get this to work. When I enter his formula
(exactly as written in a test worksheet), I get an error #NAME?

Can anyone please help.

Thanks a million,

Glynn .. gfurr1 at nc dot rr dot com