Countif with System Generated Date Column on Separate Sheet

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!



Best Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    =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
    David Tutwiler Overachievers Alumni
    Answer ✓

    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)

Answers

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    edited 03/16/21

    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)

  • 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
    David Tutwiler Overachievers Alumni
    Answer ✓

    =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
    David Tutwiler Overachievers Alumni
    Answer ✓

    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)

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    No problem. Glad it's working!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!