Countif and Year

Improve Consulting

=COUNTIF([Date Reported]:[Date Reported], (YEAR(@cell) = YEAR(TODAY())))

Hi, I'm trying to count the items for this year. But every now and then blank rows seem to appear in the sheet and this calc gives an error. Any better ideas?

Improve Consulting

Thanks Nick, I'll give them a go.

Nick Korna

Hi @Improve Consulting,

It looks like the YEAR doesn't play well with the blank rows while in a COUNTIF, so here a couple of options:

=COUNTIF([Date Reported]:[Date Reported], AND(@cell >= DATE(YEAR(TODAY()), 1, 1), (@cell <= DATE(YEAR(TODAY()), 12, 31))))

=COUNTIF([Date Reported]:[Date Reported], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

The first one checks if the date falls within this year. The second one substitutes a 0 if there isn't a date which stops the error from trying to get a YEAR from a non-existent value.

Hope these help, if you've any problems/questions then let us know. 🙂

Improve Consulting

Thanks Nick, I'll give them a go.