COUNTIF and Month

Options

Hello,

I cannot figure out what's wrong with the below. I receive an "Invalid Data Type" error. I'm counting a column of dates and the column is formatted for a date.

=COUNTIFS({2022 Epic Finish Date}, MONTH(@cell) = 1)


Thanks,

Amy

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    The dangerous thing about using IFERROR is that it can hide errors that you might need to see that would indicate a problem with your data. So if there's an easy way to avoid the error in the first place, I'll go with that. But that's just my preference.

    I don't know how much an efficiency gain there is either way. With adding in the ISDATE function, the system evaluates the column, finds the ones that are dates, and then evaluates which ones are in January and counts them. With using IFERROR, it evaluates, finds the errors, replaces the errors, and then evaluates again and counts. 🤷‍♂️

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 04/22/22
    Options

    @Amy Evans

    Do you have any blank cells in the {2022 Epic Finish Date} range? If so, you can get that error, because the MONTH function is trying to evaluate a date field that doesn't have a date in it.

    But don't panic! Here's how to get around that. COUNTIFS evaluates its criteria from left to right, eliminating cells from consideration as it goes. So we'll add a criteria to the left of the one you've already given it, to filter out those blank cells:

    =COUNTIFS({2022 Epic Finish Date}, ISDATE(@cell), {2022 Epic Finish Date}, MONTH(@cell) = 1)

    This tells it to only consider cells in that range that have date values!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Amy Evans
    Amy Evans ✭✭
    Options

    Is this better than using IFERROR? I had solved it with IFERROR, but the above is interesting, too. I haven''t used ISDATE. Which way is more efficient?


    Amy

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    The dangerous thing about using IFERROR is that it can hide errors that you might need to see that would indicate a problem with your data. So if there's an easy way to avoid the error in the first place, I'll go with that. But that's just my preference.

    I don't know how much an efficiency gain there is either way. With adding in the ISDATE function, the system evaluates the column, finds the ones that are dates, and then evaluates which ones are in January and counts them. With using IFERROR, it evaluates, finds the errors, replaces the errors, and then evaluates again and counts. 🤷‍♂️

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Amy Evans
    Amy Evans ✭✭
    Options

    Perfect! Thank you, I will use ISDATE! Have a great weekend. Thank you very much for the explanation.


    Amy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!