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

"kish20" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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.

"kish20" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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.

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" <[email protected]> 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" <[email protected]> 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" <[email protected]> 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.