Does not contain this OR that

Sharon Ostler · 02/22/21

Trying to get the correct formula that will return the forecast amount for every column except those that have the words "closed" or "no response" in the Sales Stage Column. I can get it to work if there's just one qualifier:

=SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], NOT(CONTAINS("Closed", @cell)))

but can't get it to work with two:

=SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], NOT(CONTAINS("Closed", "No Response", @cell)))

Thanks in advance!! 😁

Bassam Khalil · 02/23/21

Hi @Sharon Ostler

Hope you are fine, please try the following formula

=SUM([Forecast Amount]:[Forecast Amount]) - SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], (@cell = "closed")) - SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], (@cell = "no response"))

Sharon Ostler · 02/23/21

https://community.smartsheet.com/discussion/comment/280784#Comment_280784

This formula worked! I also was able to get the same result using this approach:

=SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], NOT(CONTAINS("Closed", @cell)), [Sales Stage]:[Sales Stage], NOT(CONTAINS("No Response", @cell)))

Thanks so much, Mike and Bassam for your help!!

Mike Wilday · 02/22/21

Try this:

=SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], OR(NOT(CONTAINS("Closed", @cell)),NOT(CONTAINS("No Response", @cell))))

Sharon Ostler · 02/22/21

HI Mike - thanks for such a quick response! That formula returned everything in the Forecast Amount column -- it didn't leave anything out. It gave me a higher number than the first formula.

Bassam Khalil · 02/23/21

Hi @Sharon Ostler

Hope you are fine, please try the following formula

=SUM([Forecast Amount]:[Forecast Amount]) - SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], (@cell = "closed")) - SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], (@cell = "no response"))

Mike Wilday · 02/23/21

@Bassam.M Khalil's formula should work for you. Doing simple math by subtracting those amounts is sometimes the best way to go!

Sharon Ostler · 02/23/21

https://community.smartsheet.com/discussion/comment/280784#Comment_280784

This formula worked! I also was able to get the same result using this approach:

=SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], NOT(CONTAINS("Closed", @cell)), [Sales Stage]:[Sales Stage], NOT(CONTAINS("No Response", @cell)))

Thanks so much, Mike and Bassam for your help!!

Bassam Khalil · 02/24/21

@Mike Wilday

Thank you for your support and i confirm that your first formula in this post was my reference to build my formula

@Sharon Ostler

You are welcome and am happy that you find the answer to your question.

Mike Wilday · 02/24/21

@Sharon Ostler

Duh. I should have recognized that. You can compare the same range two different ways and remove all of the results. Its like a built-in AND statement. I was seriously complicating matters.

Does not contain this OR that

Best Answers

Answers

Help Article Resources

Categories