Formula to count multiple, same values in a cell

Lisa Matthews
Lisa Matthews ✭✭✭✭✭
edited 04/12/23 in Formulas and Functions


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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Lisa Matthews ✭✭✭✭✭
    Answer ✓

    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!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!