Hi what's the easiest way to add IF contains?

I have a sheet with many different rows with long string values and I'd like to get a total of how many times a particular value within the string appears.

For example:

Value 1: "ABCDED"

Value 2: "ABCDDD"

I'd like a count of how many times D appears in that range of values. (it should be 5).

I've started with something like: =IF(CONTAINS("D", {Values Range 1}), "1", "0")

but that doesn't seem to work correctly as it's not summing them. I've even tried wrapping it in SUM. Any ideas?


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Advm ,

    You can record that the value contains a "D" but you can't count the "D"s in the value string. I say you can't. There's almost always a way but it would be very complicated. Is there another way to get what you need?


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 04/09/21

    Hey @Advm

    Here's one solution that may work for you. It will require a Text/Number Helper column. Let's call this helper column "Number"

    In this column, insert this formula

    =(LEN([your text string column]@row) - LEN(SUBSTITUTE([your text string column]@row, "D", ""))) / LEN("D")

    The formula above is looking at the length (number of characters) of the entire string, subtracts the non "D" characters, which means what is left should be a string length in multiples of the word "D". By dividing by the D length we can see how many multiples were in the original string.

    The SUBSTITUTE function is case sensitive. If the text you require is inconsistent in it's case (capitalization), you will need the UPPER function. The UPPER function will force all the text to upper case. Below is the same formula written with the UPPER function.

    =(LEN([your text string column]@row) - LEN(SUBSTITUTE([your text string column]@row, UPPER("D"), ""))) / LEN(UPPER("D"))

    Then, in the column where you want the Sum value to be, or the summary formula for the sheet, insert =SUM(Number:Number).

    Will that work for you?


  • Advm
    Advm ✭✭


    This works for the most part as it does count the value occurrences within a string. However, when I try to apply this to a range, I receive the error "invalid data type". How can I apply this to a range? In other words, instead of just counting the values in a single string, i'd like to count them in a range of string values.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Advm

    The only way I have been able to come close to that is with a JOIN to join the entire range into one big cell and count that. I didn't try to do that in one formula- I joined into a helper, then counted. My suggestion above of counting within the individual cells by rows into a helper column then summing that range does not work for you? It seems the easiest work around?

    You may be able to lump the JOIN(COLLECT) into the above formula but as @Mark Cronk stated in the beginning, its complicated.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I too would suggest leveraging the LEN function similar to @KDM's solution but with a couple of tweaks (primarily moving the UPPER function to the text string as opposed to "D").

    If you are searching for a single character:

    =LEN([Column Name]@row) - LEN(SUBSTITUTE(UPPER([Column Name]@row), "D", "")

    If you are wanting to count a string that is longer than 1 character:

    =(LEN([Column Name]@row) - LEN(SUBSTITUTE(UPPER([Column Name]@row), "AB", "")) / LEN("AB")

    You would then (as @KDM suggested) sum this column.

    If you wanted it to be a single formula for an entire range, you can use:

    =LEN(JOIN(COLLECT([Column name]:[Column Name], CONTAINS("D", @cell)), "/")) - LEN(SUBSTITUTE(UPPER(JOIN(COLLECT([Column name]:[Column Name], CONTAINS("D", @cell)), "/")), "D", "")

    The only thing you will need to watch out for... If that JOIN/COLLECT pulls a string longer than 4,000 characters including spaces, only the first 4,000 will be evaluated and will skew your counts.

  • Advm
    Advm ✭✭

    I'd like a single formula for an entire range, but I am not understanding portions of your formula. What is @cell ? Everything should be searching for and counting within a range (column), not a single cell. Perhaps this will help, as I am actually looking for words, not a single value.

    Value 1: "Apples Oranges Pears Bananas"

    Value 2: "Oranges Bananas Apples"

    Value 3: "Bananas"

    Value 4: "Bananas Apples"

    Value 5: "Apples Bananas Pears"

    Simply put, within this range of string values, if I want a total of how many times "Apples" appears, how can I write a formula to calculate this?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The @cell reference tells the function to evaluate the entire range but on a cell by cell basis.

    For example... Instead of saying to count if {Range} = 1, you want to say to count the individual cells within {Range} that = 1. So you say "{Range}, @cell = 1".

    All you should have to change is the [Column Name].

    And in typing out this response, I realize I forgot a range in each of the COLLECT functions. Here it is corrected:

    =LEN(JOIN(COLLECT([Column name]:[Column Name], [Column name]:[Column Name], CONTAINS("D", @cell)), "/")) - LEN(SUBSTITUTE(UPPER(JOIN(COLLECT([Column name]:[Column Name], [Column name]:[Column Name], CONTAINS("D", @cell)), "/")), "D", "")

  • Advm
    Advm ✭✭

    Thank you for your continued support. This works - however, how would it differ if I wanted to do something like my previous message, i.e., count something longer than "D", like "Apples". Your proposal appears to count the specific characters within a string. For example if a string contains "Apples Oranges Pears" and I was looking for "Apples", your code would return "6" because there are 6 characters in apple. I would want it to return 1, because Apples is mentioned once in that string.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...

    =(LEN(JOIN(COLLECT([Column name]:[Column Name], [Column name]:[Column Name], CONTAINS("Apples", @cell)), "/")) - LEN(SUBSTITUTE(UPPER(JOIN(COLLECT([Column name]:[Column Name], [Column name]:[Column Name], CONTAINS("Apples", @cell)), "/")), "Apples", "")) / LEN("Apples")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!