How to count commas in a cell?

[LinLin]

Hi Everyone
I have cells with lists of people in them (ie as a list of names, divided by
commas.
I need to count the total number of people in all the cells.
For instance, A1 = Tom, John, Bob
A2 = Kate, Uma, Deidre

I figure if I can count the commas and add 1 to each cell, I'll have the
solution.
But I can't get the count functions to work on a comma (not even as ",")

Could someone point me in the right direction?
Thanks!

 

[T. Valko]

Try this:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

Assumes the cell(s) will not be empty.

 

[Max]

.. can count the commas

One way
In B1: =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
Adapt it further to suit your intents,
eg: "+1" to the expression to get the count of names
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Rick Rothstein]

This should give you the sum you are after...

=SUMPRODUCT((LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,",",""))+1)*(A1:A1000<>""))

Adjust the ranges to cover the maximum number of rows you expect to have
data in (and make sure you use the same range for each).

 

[LinLin]

Thanks Rick - that's saved me a heap of time and I would never have come up
with that!

 

[LinLin]

Thanks Rick - that worked extremely well!

This should give you the sum you are after...

=SUMPRODUCT((LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,",",""))+1)*(A1:A1000<>""))

Adjust the ranges to cover the maximum number of rows you expect to have
data in (and make sure you use the same range for each).