How to count multiple values in a cell within a range?

Kelly Pratt

I am trying to figure out a formula where I can count 1 row more than once in a COUNTIF formula based on what it contains. For example, I have a column that includes multiple values, separated by commas. I'd like to be able to count the total number of commas within that cell. For example, if a cell has three commas - it is counted three times rather than just once in a COUNT IF formula.

Hope this makes sense and open to any and all feedback! Hoping to do this without helper columns.

Intern98

Hi I am only able to provide a solution which need a helper column:

=LEN([X]@row) - LEN(SUBSTITUTE([X]@row, ",", "")) which counts number of commas

after which a simple

=sum([help]:[help])

will account for all commas

Do let me know if it worked

Intern98

Hi I am only able to provide a solution which need a helper column:

=LEN([X]@row) - LEN(SUBSTITUTE([X]@row, ",", "")) which counts number of commas

after which a simple

=sum([help]:[help])

will account for all commas

Do let me know if it worked

Kelly Pratt

This did work - thank you!