=SUMIFS  INVALID DATA TYPE
Opened a sheet I've not been in for about a week, all was fine then. But today I have hundreds of cells with formulas showing: "INVALD DATA TYPE".
This sheet has been working fine for months, but today something has gone very wrong. Here's an example of the formulas that are in these cells:
=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell > $[DateFormula]$9, [Date Worked]:[Date Worked], @cell < $[DateFormula]$12, [Employee/Contractor]:[Employee/Contractor], $Name17, [XXXX Billable?]:[XXXX Billable?], 1)  [Jan 15 XXXX Improv Hours]@row
Time Worked = Text/Number column
Date Worked = Date column
DateFormula = Date column
Employee/Contractor = Contact List
Name = Text/Number column
XXXX Billable? = Checkbox
Jan 15 XXXX Improv Hours = Text/Number column
Again, this sheet has been working fine for months. Other sheets are very similar and are not having any issues with:
=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell > $[DateFormula]$9, [Date Worked]:[Date Worked], @cell < $[DateFormula]$12, [Employee/Contractor]:[Employee/Contractor], $Name17, [XXXX Billable?]:[XXXX Billable?], 1)
Answers

Hi @jb@59069
There could be a few causes for this specific error. Invalid Data Type means that there's an inconsistency with the the data you're looking at and the action you're asking the formula to take.
For example, if you asked the formula to look for a date but the cell you referenced is text instead of a date.
Can you check the following details:
 Date Criteria
Use the ISDATE function as a test formula to make sure that your referenced date fields ($[DateFormula]$9 and $[DateFormula]$12) are being returned as a date:
=IF(ISDATE($[DateFormula]$12), "Yes Date", "No Not Date")
If the formula above returns "No Not Date", can you paste in the formula you're using in the DateFormula column? There may be something in there which is changing your formula from a date to text, but only at specific times.
2 . Hours criteria
I noticed that you are subtracting a value at the end of your formula. You note that this column (Jan 15 XXXX Improv Hours) is a Text/Number column. Is it possible that the value being returned in this cell is being seen as text, instead of a number?
Try wrapping the VALUE function around the cell to ensure it's a number:
=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell > $[DateFormula]$9, [Date Worked]:[Date Worked], @cell < $[DateFormula]$12, [Employee/Contractor]:[Employee/Contractor], $Name17, [XXXX Billable?]:[XXXX Billable?], 1)  VALUE([Jan 15 XXXX Improv Hours]@row)
3 . Break down the formula
If neither of these checks has helped, try breaking down the formula and testing each range and criteria one at a time, to see where the error is happening:
=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell > $[DateFormula]$9)
=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell < $[DateFormula]$12)
=SUMIFS([Time Worked]:[Time Worked], [Employee/Contractor]:[Employee/Contractor], $Name17)
=SUMIFS([Time Worked]:[Time Worked], [XXXX Billable?]:[XXXX Billable?], 1)
Then once you know what range and criteria is causing the issue, we can work on how to solve it. It would then be useful to know how that column is populated (is there a formula in that column?). Keep in mind that formula errors can create a domino effect... so if one of your referenced ranges (such as "Date Worked") has an error in it, this might be why your current formula has an error as well.
Let me know if any of this has helped identify the cause of your error!
Cheers,
Genevieve

Hi @jb@59069
I see that you marked my first response as not answering the question.
What happened when you broke down the formula (number 3), were you able to identify the range/criteria that's causing the error?
Help Article Resources
Categories
Check out the Formula Handbook template!