Counting Occurence of String of Text in a Cell

Poldy ✭✭
edited 07/22/22 in Formulas and Functions


I need suggestions on how to count strings of text on a cell.

My cell contains: Triple MXL, SMax Pivot Door, SMax Pivot Door

I need to count: SMax Pivot Door and get the result: 2

The function I'm trying is giving me the wrong result:

=COUNTIF([Product - all]@row, CONTAINS("SMax Pivot Door", @cell))

I also tried to use the dropdown menu and count that, but I can't have duplicates there.

Thank you so much :)



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    First we need to add a comma to the end of the string so that each selections ends with the same value.

    =[Product - all]@row + ","

    Then we count how many commas there are to get the total number of entries (3).

    =LEN([Product - all]@row + ",") - LEN(SUBSTITUTE([Product - all]@row + ",", ",", ""))

    Now we need to remove the "SMax Pivot Door," string.

    =SUBSTITUTE([Product - all]@row + ",", "SMax Pivot Door,", "")

    We take this and find out how many entries are remaining (1).

    =LEN([Product - all]@row + ",") - LEN(SUBSTITUTE(SUBSTITUTE([Product - all]@row + ",", "SMax Pivot Door,", ""), ",", ""))

    Subtracting the entries remaining from the total entries should give us how many "SMax Pivot Door," entries there are.

    =(LEN([Product - all]@row + ",") - LEN(SUBSTITUTE([Product - all]@row + ",", ",", ""))) - (LEN([Product - all]@row + ",") - LEN(SUBSTITUTE(SUBSTITUTE([Product - all]@row + ",", "SMax Pivot Door,", ""), ",", "")))

  • Poldy
    Poldy ✭✭

    Thank you so much for your answer.

    But unfortunately, this doesn't really work for me.

    The problem is that this was just an example of what can be in the cell. Usually, I have different stings in the cell as well.

    Example: One, One, Two ...I need to count One and get result: 2, and Two and get result: 1

    Or: One, Two, One, Two, Two ...Same again...I need to count One and get result: 2, and count Two and get result: 3

    I feel like I'm trying to achieve something impossible.

    Thank you again :)


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So in the final formula you would replace "SMax Pivot Door," with whatever text string you are wanting to count.

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    Is there a maximum number of times a choice will fall within a cell?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!