Error with COUNTIFs and date formula


Can someone please advise what am I doing wrong getting this error?

The formula used is as follows:

=COUNTIFS({ref sheet}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2021))

The date column in the reference sheet is set as "Date" and the format is DD-Mon-YYYY (e.g. 19-Jan-2021). I've tried creating a default date column in my metrics sheet too, however, it made no change.

Any help is appreciated!


Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 01/19/21 Answer ✓

    Hi @Michaela Kamenska,

    Hope you are fine, please try the following formula

    =COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!