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.
Best 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
-
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
-
This did work - thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!