Does not contain this OR that

02/22/21 Edited 02/22/21
Accepted

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 KhalilBassam.M Khalil ✭✭✭✭✭
    edited 02/23/21 Accepted 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"))

    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.

  • Accepted 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 WildayMike 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.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 02/23/21 Accepted 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"))

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

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • Accepted 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.M KhalilBassam.M 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.

    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.

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

Sign In or Register to comment.