Count number of values in multiple cells with multiple values in each cell

[Muhannad1]

I have a list of cells that contain a list of names. Some cells have multiple names separated by commas.
Cell A1: Andrew
Cell A2: vanessa, christine, charlotte
Cell A3: Karl, Joe, Jason, Andrew, jonathan
etc...
I would like a count of how many names in a range of cells in column A (such as A1:A350)
I do not have a list of all names to reference as there are new ones being added all the time.

I was able to find a formula that counted how many names in each cell:
=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1
This would tell me there are 3 names in cell A2, but I want the count of all names from a range of cells in column A (such as A1:A350)

 

[Peter_SSs]

Welcome to the MrExcel board!

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, do you want this to be a count of 2 or a count of 3?

22 07 21.xlsm
	A
1	Tom
2	Bob, Tom
3
Count Names

 

[bebo021999]

try:
=SUMPRODUCT(LEN($A$1:$A$350&",")-LEN(SUBSTITUTE($A$1:$A$350&",",",","")))

 

[Muhannad1]

Welcome to the MrExcel board!

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, do you want this to be a count of 2 or a count of 3?

22 07 21.xlsm
	A
1	Tom
2	Bob, Tom
3
Count Names

Updated account settings!
In this example, the count should be 3.

 

[Muhannad1]

try:
=SUMPRODUCT(LEN($A$1:$A$350&",")-LEN(SUBSTITUTE($A$1:$A$350&",",",","")))

This didn't seem to work and gave a count of 351 for some reason....

In the example I gave where:
Cell A1: Andrew
Cell A2: vanessa, christine, charlotte
Cell A3: Karl, Joe, Jason, Andrew, jonathan

I would like the count for A1:A3 to be 9 as that's how many names are in the cells.

 

[Peter_SSs]

Try

22 07 21.xlsm
	A	B
1	Andrew	9
2	vanessa, christine, charlotte
3	Karl, Joe, Jason, Andrew, jonathan
4
Count Names

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(LEN($A$1:$A$350)-LEN(SUBSTITUTE($A$1:$A$350,",","")))+COUNTA(A1:A350)

 

[Rick Rothstein]

Try
=SUMPRODUCT(LEN($A$1:$A$350)-LEN(SUBSTITUTE($A$1:$A$350,",","")))+COUNTA(A1:A350)

Why did you use absolute references for the first two occurrences of the range and a relative reference for the third occurrence?

 

[Peter_SSs]

Why did you use absolute references for the first two occurrences of the range and a relative reference for the third occurrence?

Simply because I started with the post #3 formula, edited it & added the relative reference part and forgot to remove the $ signs.
The mixture isn't a problem is it?

 

[bebo021999]

Simply because I started with the post #3 formula, edited it & added the relative reference part ...

So, may I get contribution share?

 

[Rick Rothstein]

Simply because I started with the post #3 formula, edited it & added the relative reference part and forgot to remove the $ signs.
The mixture isn't a problem is it?

It is to someone with OCD or to someone, like me, who has some tendencies of OCD.