the NOT function

Shannon Lacerda

I am having trouble with the below formula, I want to exclude the "Unplanned"

=SUMIFS({Education 2021 Range 3}, {Education 2021 Range 1}, NOT(CONTAINS("Unplanned", @cell)), {Education 2021 Range 4}, "(Stage 3) 3.1 - RFP Submitted", {Education 2021 Range 2}, MONTH(@cell) = 1))

Leibel S

@Shannon Lacerda

2 things:

1. Looks like you have an extra parentheses at the end of your formula.
2. The NOT CONTAINS should work as described. But if you are trying to exclude a cell with a specific value, you can type: @cell<>"Unplanned"

Paul Newcome

Your formula should be working. Are you getting an error or an incorrect result?

Shannon Lacerda

@Paul Newcome I am getting this error #UNPARSEABLE

Formula

=SUMIFS({Education 2021 Range 3}, {Education 2021 Range 1}, NOT(CONTAINS(("Sole", @cell)), {Education 2021 Range 4}, "(Stage 3) 3.1 - RFP Submitted", {Education 2021 Range 2}, MONTH(@cell) = 1))

Paul Newcome

The problem with this most recent formula is an extra opening parenthesis after the CONTAINS function that needs removed which also put in an extra closing parenthesis at the end of the formula that also needs removed.

Shannon Lacerda

@Paul Newcome Thank you that works, funny how you look at enough and it all blends. and you cant really see the details.

Paul Newcome

No worries. I know the feeling.

Happy to help. 👍️