Date & Sum Formula Help

Scott2023

Good afternoon. I need assistance with a formula to sum a Status column (new, active, complete). In addition, I need this formula to capture the year it was completed (i.e., 2022, 2023, 2024). However, my values in the date columns are shown as (1/2/22, 1/2/23, 1/2/24). Any advice on how to do this or if it is possible?

Paul Newcome

Ok. So we DEFINITELY have dates. What about this one...

=COUNTIFS({End Date}, IFERROR(YEAR(@cell), 0) = 2023)

Paul Newcome

You would use a COUNTIFS like so:

=COUNTIFS({Status Column}, @cell = "New", {Date Column}, IFERROR(YEAR(@cell), 0) = 2023)

Scott2023

Thank you very much, as that worked; however, it is returning a value of 0, which is incorrect as I just completed two items yesterday, 8/2/2023.

Paul Newcome

Are you able to provide screenshots of the formula and source data?

Scott2023

Status (1).PNG

Scott2023

Status (1).PNG

=COUNTIFS({Assessment Status Column}, @cell = "New", {End Date}, IFERROR(YEAR(@cell), 0) = 2023)

Paul Newcome

In your screenshot you have "Complete", but in your formula you have "New".

Scott2023

Apologies, I entered it wrong in the previous post. I just tried again with the "complete" still returning a 0 value

=COUNTIFS({Assessment Status Column}, @cell = "Complete", {End Date}, IFERROR(YEAR(@cell), 0) = 2023)

Paul Newcome

Is the End Date column set as a date type column? If so, exactly how is it being populated?

Scott2023

Hello yes it is a date column and the dates are populated as (1/12/23, 1/12/22, etc.) hope that helps

Paul Newcome

Is it being manually populated, cell linked, or using a formula?

Scott2023

They are manually populated as the column properties are set to date.

Paul Newcome

Lets break it down a little bit. What do you get with this:

=COUNTIFS({Assessment Status Column}, @cell = "Complete")

Scott2023

I get 120 which is accurate. However, I need a formula that can separate the years based on the dates populated as such (1/12/23, 1/12/22, etc.) 

Paul Newcome

Right. I understand what your needs are. I was just trying to separate the pieces out so that we can pinpoint exactly which one is causing the issue.

So it looks like it is the date piece that is causing the issue. You have said that the column is set as a date type column and the values are populated manually. The only other reason for this issue would be if the wrong column was accidentally selected when setting up your {End Date} range. Have you verified that you did in fact select the appropriate column for this cross sheet reference?

Paul Newcome

I am actually working on another thread currently where another user is having a similar issue with using a date range in a COUNTIFS.

What happens with this:

=COUNTIFS({End Date}, ISDATE(@cell))