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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @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

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!