# Does not contain this OR that

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

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

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!!

• ✭✭✭✭✭✭

Try this:

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

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

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• ✭✭✭✭✭✭

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

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!!

• ✭✭✭✭✭✭

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

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

bassam.khalil2009@gmail.com