I am trying to count the total of extensions completed more than 30 days from my contracts sheet.

=COUNTIFS(Team:Team, "EPS", Disposition:Disposition, "Extend as is", QTR:QTR, "FY22 Q2"), AND([Days Extended prior to Expiration]:[Days Extended prior to Expiration], >30)

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hello @Yanneth

    The syntax needs some tweaking in the above formula.

    =COUNTIFS(Team:Team, "EPS", Disposition:Disposition, "Extend as is", QTR:QTR, "FY22 Q2", [Days Extended prior to Expiration]:[Days Extended prior to Expiration], >30)

    Kelly

  • Yanneth
    Yanneth ✭✭✭

    I tried that, and does not work. I get a message of invalid operation

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Yanneth

    Is it possible to see a screenshot so that I can verify the data you're entering is what the formula is expecting?

    For troubleshooting purposes, begin removing the range-criteria pairs, one by one. I would start at the end and remove the [Days Extended Prior to Exp] and the .>30. This will help pinpoint if there is a specific term that is causing the problem.

  • Yanneth
    Yanneth ✭✭✭

    This is my table, I do have a good result with out the last criteria, it is when I add the last criteria that the formula does not work

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Yanneth

    In the screenshot above the [Days Extended prior to Expiration] field is blank. I wanted to see if you have only numbers in that field.

    Let's see if this works

    =COUNTIFS([Days Extended prior to Expiration]:[Days Extended prior to Expiration], ISNUMBER(@cell), Team:Team, "EPS", Disposition:Disposition, "Extend as is", QTR:QTR, "FY22 Q2", [Days Extended prior to Expiration]:[Days Extended prior to Expiration], >30)

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!