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

mhqsmartsheet
mhqsmartsheet ✭✭
in Formulas and Functions

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.

· Share on FacebookShare on Twitter

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))

    · Share on FacebookShare on Twitter

Answers

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!