Formula to count multiple, same values in a cell
Hello,
I am stumped trying to write a working formula to count multiple, same values in one cell. I used this formula but it is not returning the number I am looking for:
=COUNTIF([DoorKing Summary]@row, CONTAINS("Yes", @cell))
I also tried this formula without luck:
=COUNTM([DoorKing Summary]@row, "Yes")
I would like the formula to count all of the "Yes" values in a cell. If none are found, value returned should be "0"
Thank you, in advance, for your help!
Lisa M.
Best Answers

In cases like this, we have to count how many characters are in the full string then subtract from that the number of characters left when we remove all instances of the word we are looking for. We then divide that number by the number of characters in the desired word (in this case 3).
=(LEN([DoorKing Summary]@row)  LEN(SUBSTITUTE([DoorKing Summary]@row, "Yes", ""))) / 3
Using the example of the second cell, you have 15 total characters. Subtract 12 from that (since we are removing one "Yes"), and we get 3. 3 divided by 3 equals 1.
=(15  12) / 3
The first cell has 33 characters. 24 Characters remain after removing all "Yes" instances giving us 9. 9 divided by 3 = 3.
=(33  24) / 3

Thank you @Paul Newcome , the formula still returned a "0", then I tried this and it worked!
=(LEN([End Device Summary]@row)  LEN(SUBSTITUTE([End Device Summary]@row, "No cross connect", ""))) / LEN("No cross connect")
I'm celebrating! Thank you so much for helping me!
Lisa
Answers

Hi @Lisa Matthews  COUNTM works if it's a dropdown, so it may not work if it's a collection of responses. These don't look like a multiple select dropdown. Are they?
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

Hi @Amber Eakin ,
You are correct, the column is not a "Dropdown List". The file is an export from monday.com and this particular column is "Text/Number". Thank you for clarifying, that's helpful to know why the COUNTM formula didn't work!
Lisa

Ah, yes, that'll do it. Sorry I couldn't be of more help!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

@Amber Eakin Thanks for trying!
I am sending out positive energy and hoping @Paul Newcome will see my call for help and walk me through how to write this formula correctly! 😊 The explanations are so clear and always have helped me figure out other formulas on my own. I'm improving but have a long way to go!
Thanks, again!
Lisa

In cases like this, we have to count how many characters are in the full string then subtract from that the number of characters left when we remove all instances of the word we are looking for. We then divide that number by the number of characters in the desired word (in this case 3).
=(LEN([DoorKing Summary]@row)  LEN(SUBSTITUTE([DoorKing Summary]@row, "Yes", ""))) / 3
Using the example of the second cell, you have 15 total characters. Subtract 12 from that (since we are removing one "Yes"), and we get 3. 3 divided by 3 equals 1.
=(15  12) / 3
The first cell has 33 characters. 24 Characters remain after removing all "Yes" instances giving us 9. 9 divided by 3 = 3.
=(33  24) / 3

Hi @Paul Newcome ,
Thank you so much! This worked perfectly! I actually read this as I was searching the community for a solution and I tried it but did not work (user error, of course) so I tried the next thing and the next...
I'm so thankful for this community and this tool, it's awesome! You are awesome and a jobsaver!
Have a great day!
Lisa

@Paul Newcome  this is the COOLEST solution! What a clever way to employ this function!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

Happy to help. 👍️

Hello @Paul Newcome ,
How can I use this formula for a longer string value like this "No Cross Connect"? I tried the LEN formula but getting "0" result for each row (works perfectly for "Unknown" string, though).
Objective: Count the number of "No Cross Connect" values in a string.
Tried this formula:
=(LEN([End Device Summary]@row)  LEN(SUBSTITUTE([End Device Summary]@row, "No Cross Connect", ""))) / 16
Thank you so much for taking a look at this and helping me.
Lisa

@Lisa Matthews I don't think it would be case sensitive, but just in case, give this a try:
=(LEN([End Device Summary]@row)  LEN(SUBSTITUTE(UPPER([End Device Summary]@row), "NO CROSS CONNECT", ""))) / 16

Thank you @Paul Newcome , the formula still returned a "0", then I tried this and it worked!
=(LEN([End Device Summary]@row)  LEN(SUBSTITUTE([End Device Summary]@row, "No cross connect", ""))) / LEN("No cross connect")
I'm celebrating! Thank you so much for helping me!
Lisa

Glad you were able to get it working. That was going to be my next try even though LEN("No cross connect") SHOULD equal 16.

I thought 16 should work, too. Formulas beat me every day.
Thanks for your help! Always appreciated! This community is awesome.
Lisa
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!