How to count commas in a cell?
 

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!

How to count commas in a cell?
 

Try this:

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

Assumes the cell(s) will not be empty.

--
Biff
Microsoft Excel MVP


"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!

How to count commas in a cell?
 

.. 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
---
"LinLin" wrote:
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 ",")

How to count commas in a cell?
 

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!

How to count commas in a cell?
 

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!

How to count commas in a cell?
 

Thanks Rick - that worked extremely well!

"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!