Does not contain this OR that

Sharon Ostler
Sharon Ostler ✭✭
edited 02/22/21 in Formulas and Functions

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 Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/23/21 Answer ✓

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

    PMP Certified

    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"

  • Sharon Ostler
    Sharon Ostler ✭✭
    Answer ✓

    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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/23/21 Answer ✓

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

    PMP Certified

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

    PMP Certified

    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"

  • Mike Wilday
    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
    Sharon Ostler ✭✭
    Answer ✓

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

    PMP Certified

    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"

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!