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!