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

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.

Find more posts tagged with

Formulas

Comments

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…