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

mhqsmartsheet

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},

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.

Carson Penticuff

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

Carson Penticuff

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

mhqsmartsheet

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.<\/p>

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.<\/p>

=COUNTIFS({PMO Project Pipeline Step}, "Done", {PMO Project Pipeline Target End}, <= TODAY(), {PMO Project Pipeline Target End}, > TODAY(-365))<\/p>","bodyRaw":"[{\"insert\":\"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.\\nThe 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.\\n=COUNTIFS({PMO Project Pipeline Step}, \\\"Done\\\", {PMO Project Pipeline Target End}, <= TODAY(), {PMO Project Pipeline Target End}, > TODAY(-365))\\n\"}]","format":"rich","dateInserted":"2023-08-03T17:17:00+00:00","insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388751#Comment_388751","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/388751#Comment_388751

Wow, huge thanks! Really appreciate the prompt and accurate answer! Much appreciated!!