Inconsistent Formula Error

Vivien Chong · 12/28/22

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?

Jeff Reisman · 01/03/23

https://community.smartsheet.com/discussion/comment/355477#Comment_355477

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")

Jeff Reisman · 12/28/22

@Vivien Chong

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.

Vivien Chong · 12/29/22

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?

Vivien Chong · 12/29/22

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

Jeff Reisman · 01/03/23

https://community.smartsheet.com/discussion/comment/355477#Comment_355477

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")

Inconsistent Formula Error

Best Answer

Answers

Help Article Resources

Categories