Help w/NOT counting specific agreements in a COUNTIFS formula

jmo
jmo ✭✭✭✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Answers

  • Mark Cronk
    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


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

    · Share on FacebookShare on Twitter
  • jmo
    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.

    · Share on FacebookShare on Twitter
  • Mark Cronk
    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


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

    · Share on FacebookShare on Twitter
  • jmo
    jmo ✭✭✭✭✭✭

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

    Appreciate the input.

    Jeff

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!