# Formula to count multiple, same values in a cell

✭✭✭✭✭
edited 04/12/23

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"

--Lisa M.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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 job-saver!

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!