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

JLC

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.

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

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?

Mike Blumenstein

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

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.