how to count occurence of numbers separated by , in a single cell
 

Pls tell how I can count the occurence of particular number in list of
numbers separated by comma in single cell.I would further extend this
counting to the entire column.

Eg the number in a first cell : 2,3,4,5
Second cell: 2,3,6
third cell : 3,5,7 and so on for more than
two rows..
I wish to count how many times number 2 appeared in the entire column of
more than two rows.

Here the answer expected from excel programming is 2.

"kish20" wrote...
Pls tell how I can count the occurence of particular number in list of
numbers separated by comma in single cell.I would further extend this
counting to the entire column.

Eg the number in a first cell : 2,3,4,5
Second cell: 2,3,6
third cell : 3,5,7 . . .
....
Here the answer expected from excel programming is 2.

=SUMPRODUCT(LEN(","&A1:A3&",")-LEN(SUBSTITUTE(","&A1:A3&",",",2,",",,")))

Hi Harlan,

Please suggest me a good book to learn excel functions and programming.

"Harlan Grove" wrote:

"kish20" wrote...
Pls tell how I can count the occurence of particular number in list of
numbers separated by comma in single cell.I would further extend this
counting to the entire column.

Eg the number in a first cell : 2,3,4,5
Second cell: 2,3,6
third cell : 3,5,7 . . .
....
Here the answer expected from excel programming is 2.

=SUMPRODUCT(LEN(","&A1:A3&",")-LEN(SUBSTITUTE(","&A1:A3&",",",2,",",,")))

"kish20" wrote...
Please suggest me a good book to learn excel functions and programming.
....

Re Functions: I have no idea. I've never read any. I used to read the
spreadsheet columns in PC World, PC Magazine and Byte way back, and Lotus
Magazine too, but that's now over a decade ago. I never bought a general
book on spreadsheets (didn't have to, the manuals that came with Lotus
Symphony, Lotus 123, VP-Planner and even Excel prior to Excel 97 were all
very good, though the How To guide that came with Symphony was the best of
all of them).

Re Programming: maybe buy John Green's book or John Walkenbach's latest, but
the best programming books aren't VBA programming books.