Does not contain this OR that
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!! 😁
Best Answers
-
Bassam.M 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"))
Best Regards
Bassam.M Khalil
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.
0 -
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!!
0
Answers
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"))
Best Regards
Bassam.M Khalil
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.
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"))
Best Regards
Bassam.M Khalil
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.
@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!!
@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.
Best Regards
Bassam.M Khalil
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.
@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.