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

"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"LinLin" wrote in message
...
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!