COUNTIF and Month
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
-
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
-
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!
-
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
-
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!
-
Perfect! Thank you, I will use ISDATE! Have a great weekend. Thank you very much for the explanation.
Amy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!