How to count commas in a cell?

  • Thread starter Thread starter LinLin
  • Start date Start date
L

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!
 
.. 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
 
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).
 
Thanks Rick - that worked extremely well!

Rick Rothstein said:
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).
Click to expand...
 
Back
Top