Countifs with Year, Month - Date Column

Good afternoon,

I am using

countifs with the Month and Year functions and keep getting errors.

Is it because, in my 'Delivery Date' column, some rows have text like 'Canceled' & 'On Hole.' I would love to get some assistance as I have a large dashboard due at noon tomorrow.

=COUNTIFS({Store Type}, "New", {Parent Row}, 1, {Delivery Date}, AND(IFERROR(MONTH(@cell), 0), IFERROR(YEAR(@cell), 0) = 2023))


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    You are missing the comparison for the month in your formula. This makes is easier to visualize:

    Here is the formula corrected to look for January 2023. The IFERROR()s will prevent the non-date entries from being an issue.

    =COUNTIFS({Store Type}, "New", {Parent Row}, 1, {Delivery Date}, AND(IFERROR(MONTH(@cell), 0)= 1, IFERROR(YEAR(@cell), 0) = 2023))

  • @Carson Penticuff, thank you for your response. I added the comparison. However, It is still not working.

    I have ran the formula multiple ways and now I am only getting an error when I add the YEAR function. Also, when I run the formula below, I am getting a '0' instead of and error in my cell.

    =COUNTIFS({Store Type}, "New", {Parent Row}, 1, {Delivery Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Looking at your screenshot, it does not appear the formatting is consistent in your date column. Can you verify it is formatted as "date"?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!