Formula for a Countif in the last year

Spencor

I am working on pulling historical data from another source sheet but I only want to evaluate the data within the last year. I am having problems with getting the formula to pull information.
I am attempting to count completed projects that were ordered in this calendar year. The idea would be to take this concept and use it for data for other date ranges as well (previous year, 2 years ago etc) to show trends and progression.
Once I can get the core formula functioning as expected, I will be expanding it to a Countifs formula to further narrow the data.
The core formula I am using is:

=COUNTIF({Archive Project}, YEAR({Archive Year}) = YEAR(TODAY()))

-The archive year reference is looking at a column in our completed projects, this column is a date column.
-I keep getting 0 as my result although I have tested each part of the formula individually.

Paul Newcome

Is {Archive Year} populated with just years or is it a date type column containing dates?

Spencor

@Paul Newcome it is a date type column containing dates. I am attempting to use the year function to mine the year from it without needing to create a helper column.

Paul Newcome

In that case you would use this instead:

=COUNTIFS({Archive Year}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Spencor

@Paul Newcome that formula works!
Just for my own understanding- what is missing in the logic from my formula?
- I was able to use the Year function in a single date cell to extract the year 2024, but when trying to compare that value to the Year(today()), it wouldn't translate correctly.
-In my mind, if I can extract the year 2024, i should be able to compare that to a value in a countif formula. What dots am I not connecting?

Paul Newcome

The issue is in the syntax. To evaluate the year of all cells in a date column one by one, you have to establish the {Range} and the use the @cell reference inside of the YEAR function.

A challenge that comes with that though is if you have any blanks or non-date values in your {Range}, the YEAR function will throw an error (because there is no year to pull) which will in turn error the whole formula. That is why we use the IFERROR. To replace that error with a zero. Since we know zero is not equal to 2024, we don't have to worry about any false positives or anything in those instances.

Spencor

@Paul Newcome thanks for explaining it that way!
How does this translate into a SUMIF situation? Say I want to sum the revenue of the projects in the last year

Spencor

@Paul Newcome I was able to work this out. Just posting the solutions here to help anyone who finds it in the future.

-Sum of revenue that happened in this year

=SUMIF({Archive Year}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()), {Archived Revenue})

and further broken down by salesperson using

=SUMIFS({Archived Revenue}, {Archive Year}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()), {Archived Salesperson}, =Metric@row)

Paul Newcome

Happy to help. 👍️