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

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

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

Wow, huge thanks! Really appreciate the prompt and accurate answer! Much appreciated!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!