Inconsistent Formula Error
Have you came across this situation?
I am working on a formula to calculate base on month and staff. The same formula only works for some staff and I am wondering what went wrong.
I am using exactly the same formula for both staff. But one is showing #Invalid Data Type (in our formula we use "ERROR" to indicate if there is any error).
It is referencing the same data source. What could be the issue here?
Best Answer
-
To avoid the error when the Actual End date is blank, add a criteria to your formula using the ISDATE function to check if there is a date value in the Actual End date column. Since Smartsheet formulas consider criteria from left to right, make sure it's to the left of any other criteria for Actual End date, so that we rule out any rows with blank dates first and thus prevent the formula from trying to evaluate them for MONTH and YEAR:
=IFERROR(COUNTIFS({PIC1}, $Name@row, {Actual end}, ISDATE(@cell), {Actual end}, MONTH(@cell) = [1]$1, {Actual end}, YEAR(@cell) = $Year@row), "ERROR")
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
-
It would be helpful to see the formula and to know the column types and the column types/data in the referenced range.
Without that, the only thing I could point you to would be the description of that error:
#INVALID DATA TYPE
Cause: The formula contains or references an incompatible data type, such as =INT("Hello")
Resolution: Make sure the formula references the correct data type.
The link to the page describing formula error messages is in my signature below.
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!
-
Hi @Jeff Reisman the issue here is formula for both staff are the same, referencing same data source, with same data column types/data. However, one is showing error, the other is working.
Anyway, here is the formula for "No of Test":
=IFERROR(COUNTIFS({PIC1}, $Name@row, {Actual end}, MONTH(@cell) = [1]$1, {Actual end}, YEAR(@cell) = $Year@row), "ERROR")
{PIC1} reference the staff name in source sheet (Contact List)
{Actual end} reference the end date in source sheet (date)
The "NAME" column in this sheet is Contact List
From the source sheet, it should pick up the PIC name (staff name) and actual end month and record in the respective monthly column (1 - 12). If staff name blank or actual end month is blank, it should not be taken into calculation.
What could cause the error?
-
@Jeff Reisman I think I have found a clue.
- The staff with some empty Actual End date is showing the #Invalid Data Type.
- Staff with all Actual End date is showing the right calculation.
How can I improve my formula so it will not take into account when the Actual end date is empty?
-
To avoid the error when the Actual End date is blank, add a criteria to your formula using the ISDATE function to check if there is a date value in the Actual End date column. Since Smartsheet formulas consider criteria from left to right, make sure it's to the left of any other criteria for Actual End date, so that we rule out any rows with blank dates first and thus prevent the formula from trying to evaluate them for MONTH and YEAR:
=IFERROR(COUNTIFS({PIC1}, $Name@row, {Actual end}, ISDATE(@cell), {Actual end}, MONTH(@cell) = [1]$1, {Actual end}, YEAR(@cell) = $Year@row), "ERROR")
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!