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.

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.