Countif and Year


=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?

Best Answer


  • Nick Korna
    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
    Improve Consulting ✭✭✭✭
    Answer ✓

    Thanks Nick, I'll give them a go.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!