与系统生成的日期列在Separ条件统计ate Sheet

Sara Loehwing

Hello!

I have a sheet that all projects that are marked as closed are automatically moved to. There is a system generated date field to show when the lines were created so that we know what date each project was closed.

I am trying to, in a separate sheet, reference the Date Closed column and return a countifs if the date is within a specific month.

In reading previous forum posts, I was attempting to modify something like this:

=COUNTIFS{IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

And I was going to work back from the Year to the Month, but It keeps returning an #UNPARSEABLE error.

My Date Closed column looks like this, but I am not sure why it won't return any of my 2021 results. Please help!

David Tutwiler

=COUNTIF({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

I just got that formula working on one of my test sheets. Try that and see what it gives you.

David Tutwiler

I think you'd be looking at something like (assuming you're looking for January. If not just change the 1 to 1-12 corresponding to the months):

=COUNTIFS({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021, {IT Completed Projects Archive Range 1}, IFERROR(MONTH(@cell), 0) = 1)

Danielle O'Connell

This might not be the cleanest way to do what you're looking for, but you could create two date columns - one with a start date (IE 1/1/21) and one with an end date (1/31/21). Then (assuming you have a column with the mm-yyyy in your new sheet that you are tallying this in) apply a formula like this to the cell with the month you are looking for (IE January 2021):

=COUNTIFS({IT Completed Projects Archive Range 1}, @cell >= [start date]@row, {IT Completed Projects Archive Range 1}, @cell <= [end date]@row)

If you don't want to do two columns, you can put in DATE(2021,1,1) for example as the start date and DATE(2021,1,31) for the end date, but then you would have to manually change the date for each month.

You can still do the IFERROR portion, but I would personally put it on the outside:

=IFERROR((COUNTIFS({IT Completed Projects Archive Range 1}, @cell >= [start date]@row, {IT Completed Projects Archive Range 1}, @cell <= [end date]@row), 0)

Sara Loehwing

I am not sure if I am just really terrible at this, I don't want to have multiple columns if possible for the dates, so I just tried to test with January and I am still getting an Unparseable error on the below:

=IFERROR((COUNTIFS({IT Completed Projects Archive Range 1}, @cell >= DATE(2021,1,1)@row, {IT Completed Projects Archive Range 1}, @cell <= DATE(2021,1,31)@row), 0)

Should I perhaps remove the IFERROR and try again?

David Tutwiler

=COUNTIF({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

I just got that formula working on one of my test sheets. Try that and see what it gives you.

Sara Loehwing

=COUNTIF({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021)<\/p>

I just got that formula working on one of my test sheets. Try that and see what it gives you.<\/p>","bodyRaw":"[{\"insert\":\"=COUNTIF({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021)\\nI just got that formula working on one of my test sheets. Try that and see what it gives you.\\n\"}]","format":"rich","dateInserted":"2021-03-16T18:09:16+00:00","insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-08-24T21:03:19+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/283781#Comment_283781","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/283781#Comment_283781

This worked! Can I ask you to add how I would add a month to it as well?

David Tutwiler

I think you'd be looking at something like (assuming you're looking for January. If not just change the 1 to 1-12 corresponding to the months):

=COUNTIFS({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021, {IT Completed Projects Archive Range 1}, IFERROR(MONTH(@cell), 0) = 1)

Sara Loehwing

I think you'd be looking at something like (assuming you're looking for January. If not just change the 1 to 1-12 corresponding to the months):<\/p>

=COUNTIFS({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021, {IT Completed Projects Archive Range 1}, IFERROR(MONTH(@cell), 0) = 1)<\/p>","bodyRaw":"[{\"insert\":\"I think you'd be looking at something like (assuming you're looking for January. If not just change the 1 to 1-12 corresponding to the months):\\n=COUNTIFS({IT Completed Projects Archive Range 1}, IFERROR(YEAR(@cell), 0) = 2021, {IT Completed Projects Archive Range 1}, IFERROR(MONTH(@cell), 0) = 1)\\n\"}]","format":"rich","dateInserted":"2021-03-16T18:19:01+00:00","insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-08-24T21:03:19+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/283785#Comment_283785","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/283785#Comment_283785

You are amazing David! Thank you so much!!

David Tutwiler

No problem. Glad it's working!