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
-
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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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!!
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"))
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!