CountIF won't recognise the result of a formula

Kate Rhodes

I have a column that contains a column formula driven from a date.

GRID COLUMNS

Detection Date = DD/MM/YY

Year = formula: =20 + RIGHT([Detection Date]@row, 2). Result = 2023

Sheet summary

This Year = formula: =20 + RIGHT(TODAY(), 2). Result today = 2023

Breach This Year = formula: =COUNTIF(Year:Year, [This Year]#). Result = 0 (should count 113 instances of 2023).

The COUNTIF formula simply will not recognise the 2023 in the Year column. Even if i change the formula to state =COUNTIF(Year:Year, 2023) or put 2023 in "", the result is always the same as 0.

How can I update my COUNTIF to recognise the value of 2023 in the Year column?

Paul Newcome

If you are using a date type column, you can just use the YEAR function directly within the COUNTIF and not worry about the helper column with the year in it at all.

=COUNTIFS([Detection Date]:[Detection Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

Toufong Vang

@Kate Rhodes, place your Year formula in VALUE(): VALUE(20 + RIGHT([Detection Date]@row, 2))

It's not needed but, for consistency, you can do the same with your This Year formula.

Paul Newcome

If you are using a date type column, you can just use the YEAR function directly within the COUNTIF and not worry about the helper column with the year in it at all.

=COUNTIFS([Detection Date]:[Detection Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

Kate Rhodes

Paul Newcome I tried your formula and it worked perfectly. Thanks so much :)

Paul Newcome

Happy to help. 👍️