SUM IF CONTAINS?

Advm

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

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?

Mark

Kelly Moore

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?

Kelly

Advm

https://community.smartsheet.com/discussion/comment/286773#Comment_286773

Hi,

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

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.

Kelly

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

https://community.smartsheet.com/discussion/comment/286832#Comment_286832

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

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

https://community.smartsheet.com/discussion/comment/286862#Comment_286862

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

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")