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

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.

Tags:

Best Answer

  • Intern98
    Intern98 ✭✭✭
    Answer ✓

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!