YEAR function in cross-sheet reference COUNTIFS

katie.dickinson

I am working on a metrics sheet where I'd like to count the number of items that a given department has implemented within in a specific year.

In this formula {Date Implemented} is a cross sheet reference to a date column (MM/DD/YYYY) and the matching criteria is a cell that contains the year value, in this example "2024". Naturally, this doesn't get counted in the countifs as is, because 2024 != 03/11/2024.

=COUNTIFS({Date Implemented}, $[2024]$1, {Department}, [Primary Column]@row, {Status}, "Implemented")

So, I added a YEAR function in the formula, expecting that the below would pull the year "2024" from "03/11/2024" and be counted. Instead, I receive an INVALID DATA TYPE error.

=COUNTIFS(YEAR({Date Implemented}), $[2024]$1, {Department}, [Primary Column]@row, {Status}, "Implemented")

I ended up adding a helper column in the source sheet and applied the YEAR function there so I have Year Implemented and the below worked just fine. BUT I don't understand why I couldn't nest the YEAR function in my metrics sheet. My metric sheet column type is text/number. {Date Implemented} does have null values in the source sheet, because not everything is implemented - could that be it?

Workaround formula using helper column {Year Implemented} on source sheet, which worked, for reference.

=COUNTIFS({Year Implemented}, $[2024]$1, {Department}, [Primary Column]@row, {Status}, "Implemented")

JamesB

@katie.dickinson

Trying to understand this part of your formula $[2024]$1

Why wouldn't you just put the 2024 in your formula.

=COUNTIFS(YEAR({Date Implemented}), 2024, {Department}, [Primary Column]@row, {Status}, "Implemented")