
Next: Need help with a function  help 3 
Author 
Message 
LinLin External
Since: Jan 28, 2009 Posts: 4

Posted: Wed Jan 28, 2009 10:43 pm Post subject: How to count commas in a cell? Archived from groups: microsoft>public>excel>worksheet>functions (more info?) 


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! 

Back to top 


Max External
Since: Mar 17, 2004 Posts: 1796

Posted: Wed Jan 28, 2009 10:58 pm Post subject: Re: How to count commas in a cell? Archived from groups: per prev. post (more info?) 


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

Back to top 


T. Valko External
Since: Nov 24, 2006 Posts: 3649

Posted: Thu Jan 29, 2009 1:56 am Post subject: Re: How to count commas in a cell? Archived from groups: per prev. post (more info?) 


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! 

Back to top 


Rick Rothstein External
Since: Jan 15, 2009 Posts: 127

Posted: Thu Jan 29, 2009 2:02 am Post subject: Re: How to count commas in a cell? Archived from groups: per prev. post (more info?) 


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! 

Back to top 


LinLin External
Since: Jan 28, 2009 Posts: 4

Posted: Thu Jan 29, 2009 2:02 am Post subject: Re: How to count commas in a cell? Archived from groups: per prev. post (more info?) 


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

Back to top 


LinLin External
Since: Jan 28, 2009 Posts: 4

Posted: Thu Jan 29, 2009 3:36 am Post subject: Re: How to count commas in a cell? Archived from groups: per prev. post (more info?) 


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

Back to top 



