COUNTIF and Month

Amy Evans

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327130#Comment_327130

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. 🤷‍♂️

Jeff Reisman

@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!

Amy Evans

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

https://community.smartsheet.com/discussion/comment/327130#Comment_327130

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. 🤷‍♂️

Amy Evans

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

Amy