#INVALID DATA TYPE Error
I am receiving an #INVALID DATA TYPE Error when I have blanks in the dates I am referencing. The formula is summing the equipment pieces based on the delivery month, but the delivery date can be added later in the project, so there will often be blanks. My formula is below:
Adding an IFERROR avoids the error message, but I would like for the formula to work for the data that is present in the other months. It is feeding a chart that is a lot less meaningful without the not accepted showing up.
Any ideas on this? Appreciate your help in advance!
Best Answer
-
Just add an ISDATE function to your SUMIFS so it only takes Dates into account.
Your formula would be like this then:
=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, AND(ISDATE(@cell),MONTH(@cell)=Value72))
Also, you can use the IFERROR on the MONTH function:
=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, IFERROR(MONTH(@cell), 0)=Value72)
Hope it helped!
Answers
-
Just add an ISDATE function to your SUMIFS so it only takes Dates into account.
Your formula would be like this then:
=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, AND(ISDATE(@cell),MONTH(@cell)=Value72))
Also, you can use the IFERROR on the MONTH function:
=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, IFERROR(MONTH(@cell), 0)=Value72)
Hope it helped!
-
Thanks for those suggestions. The ISDATE did not work, but the IFERROR on the month did.
Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!