Formula to count multiple, same values in a cell

Lisa Matthews

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.

Paul Newcome

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

Lisa Matthews

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

Amber Eakin

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?

Lisa Matthews

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

Amber Eakin

Ah, yes, that'll do it. Sorry I couldn't be of more help!

Lisa Matthews

@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

Paul Newcome

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

Lisa Matthews

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

Have a great day!

--Lisa

Amber Eakin

@Paul Newcome - this is the COOLEST solution! What a clever way to employ this function!

Paul Newcome

Happy to help. 👍️

Lisa Matthews

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

Paul Newcome

@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

Lisa Matthews

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

Paul Newcome

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.

Lisa Matthews

Hi @Paul Newcome

I thought 16 should work, too. Formulas beat me every day.

Thanks for your help! Always appreciated! This community is awesome.

-Lisa