INVALID DATA TYPE Error in single cell of column formula

kelceyg

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.

Leibel S

@kelceyg

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

@kelceyg

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

kelceyg

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

trixyone

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.

Leibel S

@trixyone

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)