How to Count if End Date is in Less than 365 Days?

Hi Smartsheet Community.

Hoping for your help on this question.

We need to have a count of Projects that are closed and have an end date that is in the last year.

=COUNTIF({PMO Project Pipeline Step}, "Done", {PMO Project Pipeline Target End}, <TODAY(-365))

This is throwing an Incorrect Argument Set error. Do you see anything wrong with my equation? Do you have any advice?

Thanks for any guidance you can offer.

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I see two issues. First, you are getting the error because you are using COUNTIF() but are evaluating two conditions. COUNTIF() only handles one condition... to evaluate multiple, you will need to use COUNTIFS() instead.

    The second: As written, you will be attempting to match entries that are before 365 days prior to the current date, meaning MORE than one year prior. If I understand your intent, the formula below should work.

    =COUNTIFS({PMO Project Pipeline Step}, "Done", {PMO Project Pipeline Target End}, <= TODAY(), {PMO Project Pipeline Target End}, > TODAY(-365))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!