INVALID DATA TYPE Error in single cell of column formula
Hello,
I am getting one cell returning the "INVALID DATA TYPE" error in a column formula. I have 159 rows in this sheet, and only one cell has the error, so I am very confused.
The formulas is:
=COUNTIFS({Site Visits Range 1}, Type@row, {Site Visits Range 3}, [Primary Column]@row, {Site Visits Range 2}, <>"", {Site Visits Range 2}, YEAR(@cell) = 2023, {Site Visits Range 7}, 1) + COUNTIFS({Site Visits Range 1}, Type@row, {Site Visits Range 3}, [Primary Column]@row, {Site Visits Range 4}, <>"", {Site Visits Range 2}, YEAR(@cell) = 2023, {Site Visits Range 7}, 1) + COUNTIFS({Site Visits Range 1}, Type@row, {Site Visits Range 3}, [Primary Column]@row, {Site Visits Range 5}, <>"", {Site Visits Range 2}, YEAR(@cell) = 2023, {Site Visits Range 7}, 1) + COUNTIFS({Site Visits Range 1}, Type@row, {Site Visits Range 3}, [Primary Column]@row, {Site Visits Range 6}, <>"", {Site Visits Range 2}, YEAR(@cell) = 2023, {Site Visits Range 7}, 1)
It just seems strange to me that I would only get this error for one cell. Maybe I'm missing something?
NOTE: These sheets and the formula were created by someone who has left my organization. I was asked to step in and finish the work, so I cannot speak to why anything was done or written the way that it is. I am just trying to fix the error.
Best Answer
-
Only thing that sticks out is the YEAR() function.
Check {Site Visits Range 2} sheet and add a column there with the year function and see if anything is giving you an error.
This could mean it does not have a proper date in the cell.
You could also add an IFERROR() in your formula to ignore them: IFERROR(YEAR(@cell),0) = 2023
Answers
-
Only thing that sticks out is the YEAR() function.
Check {Site Visits Range 2} sheet and add a column there with the year function and see if anything is giving you an error.
This could mean it does not have a proper date in the cell.
You could also add an IFERROR() in your formula to ignore them: IFERROR(YEAR(@cell),0) = 2023
-
@Leibel S You nailed it! I did what you suggested and added a column to the source sheet to see if I could pinpoint what was throwing the error, and I found the issue quickly!
I added the IFERROR function to the formula, and it fixed my issue. Thank you so much for your help on this!
-
Hello. I am facing a similar issue.
Formula is: =COUNTIFS({Fall Prevention Observation Tool UNIT}, Unit@row, {Fall Prevention Observation Tool DATE}, MONTH(@cell) = MONTH((TODAY())), {Fall Prevention Observation Tool DATE}, YEAR(@cell) = YEAR(TODAY()))
I tested the source table. When I changed the Unit name in the source table to any of the other units listed, the formula functioned and returned the correct count. I checked the DATE column for data type, tested different dates, re-entered data via entry form, re-entered data manually in the table and still got the error. The only thing that seemed to fix is if I entered a different Unit. I don't understand why "K7" would trigger an error.
-
This would mean that at least one of the rows that has K7 is not a valid date.
As mentioned previously you can add an IFERROR statement within your COUNTIFS function:
IFERROR(YEAR(@cell),0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!