Countif and Year

Options

=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

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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 ✓
    Options

    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!