Help w/NOT counting specific agreements in a COUNTIFS formula

Options
jmo
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

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

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

    Appreciate the input.

    Jeff

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!