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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!