#INVALID DATA TYPE adding MONTH to COUNTIFS with cross-sheet refs

JLC
JLC ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello all, using the following formula:

=COUNTIFS({TACTICS design resource}, AND(@cell <> "", @cell <> "N/A"), {TACTICS status}, AND(@cell <> "Cancelled", @cell <> "On hold"), {TACTICS status}, <>"admin", {TACTICS project number}, <>"*N/A", @{TACTIC actual delivery date}, MONTH(@cell) = 12)

I'm getting an #INVALID DATA TYPE error. I know the issue lies in the date as it's the only piece I've tried updating - I previously had two sets of dates (>= Dec 1, <= Dec 31).

I believe I've read somewhere on here that using the MONTH function produces errors in certain scenarios. Am I unable to use this function when using cross-sheet cell references or something?

Grateful for any help addressing an error in my formula or in this function's capabilities.

Tags:

Comments

  • JLC
    JLC ✭✭✭✭✭✭

    I'd like to add that this formula is now miraculously working today without any editing by me. Can somebody offer any insight? I've seen multiple posts on the MONTH function being unreliable. If it ended up being reliable, it would really save a LOT of time for our team!

  • JLC
    JLC ✭✭✭✭✭✭

    Back to not working. I'm sure it has to do with an issue in one of the REFs...maybe a new row being added with the date column being blank?

  • Jaye, 

    Date formulas, especially with cross-sheet references, will error out if there are cells in your defined range that do NOT contain a date. You need to add a condition to only look at the rows that have dates, or exclude those that don't. However you want to look at it. Pretty easy to just add this to your formula:

    @{TACTIC actual delivery date}, ISDATE(@cell) = true

    =COUNTIFS({TACTICS design resource}, AND(@cell <> "", @cell <> "N/A"), {TACTICS status}, AND(@cell <> "Cancelled", @cell <> "On hold"), {TACTICS status}, <>"admin", {TACTICS project number}, <>"*N/A", @{TACTIC actual delivery date}, ISDATE(@cell) = true, @{TACTIC actual delivery date}, MONTH(@cell) = 12)

    That should help make sure your formula is more error free and reliable!

    -Mike

  • JLC
    JLC ✭✭✭✭✭✭

    Thanks Mike, appreciate that! We ended up doing something similar - {TACTIC actual delivery date}, <>"" seemed to work. I will use your suggestion instead in the future.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!