Counting the amount of numbers that show up in Column

[uschad]

I have a column that has cells that have multiple numbers in them separated by commas:

1,2,3
2,5,7
1
1,8,9,10

I need a formula that will return the number of times that a particular number shows up in the column. So for the number 1 the formula would return 3 instances, and for the number 2 it would return 2 instances and so on. I have used this formula and it seems to do what I need except for the numbers 1 and 10.

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"2",""))) in array.

Any help would be appreciated.

 

[Domenic]

Let's assume that A2:A5 contains the data, let C2 contain 1, C3 contain 2, C4 contain 3, and so on, and then try...

D2, copied down:

=SUMPRODUCT(LEN(","&$A$2:$A$5&",")-LEN(SUBSTITUTE(","&$A$2:$A$5&",",","&C2&",","")))/LEN(","&C2&",")

 

[uschad]

That appears to work great. Thanks again.