Does not contain this OR that

Sharon Ostler

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

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

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

Try this:

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

Sharon Ostler

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

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

Bassam Khalil

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

@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

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

@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

@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.