Help w/NOT counting specific agreements in a COUNTIFS formula

jmo

Hi team - I have a requirement to do a COUNTIFS formula that looks across a pretty large sheet (almost 4000 rows) and sum up those "agreements" that have errors.

Currently I'm using this formula (this is for the month of May, I change the month number for other month counts):

=COUNTIFS({Agreements out for Approval (C H M) main row}, 1, {Agreements out for Approval (C H M) Created}, IFERROR(MONTH(@cell), 0) = 5, {Agreements out for Approval (C H M) division by}, NOT(CONTAINS("general counsel", @cell)), {Agreements out for Approval (C H M) C/H/M incorrec}, 1, {Agreements out for Approval (C H M) authors}, NOT(CONTAINS("gsd invalid request", @cell)))

I am now being told that there is a large number of "agreements" (these are just numbers - no letters in the Agreement # cell) that need to be exempted from the count.

Would I have to do a {Agreements out for Approval (C H M) Agreement #}, NOT(CONTAINS(specific agreement #", @cell) for EVERY unique agreement number?

I have about 30 agreements I need to exempt and that number will definitely grow over time.

Appreciate your help on this!

Jeff

Mark Cronk

Hi, If I understand your need correctly, I'd create an "Exempt" column as a check box in your grid. Check it for those agreements that are exempt from the count. In your countif formula add a criteria that Exempt@row = False. Is that what you need?

Mark

jmo

https://community.smartsheet.com/discussion/comment/268539#Comment_268539

Hi @Mark Cronk - I actually did that (added an exempt column then counted those) for another formula however, now I'm being told to ONLY exempt specific agreements. The exempt column has a mixture of a lot of other agreements that have been exempted and backing the ones I need out of the exempt column affects a bunch of other computations downstream.

Guess I'll have to just work on creating a long series of NOT(CONTAINS) inclusions in my formula.

Mark Cronk

Hi again, NOT(CONTAINS) is going to be a nightmare if you have to exempt a large number of agreements or the exempted agreements change. Consider using multiple exempt columns for the different purposes your being asked to excuse some items. Alternatively, create an exempt reason column that could be multiple drop downs and then use a Not(Contain()) criteria that looks for the reason in the column. Either way gets rid of the hard owing and future problems. Good luck! Mark

jmo

That's a great idea to consider, @Mark Cronk!

Appreciate the input.

Jeff