=SUMIFS - INVALID DATA TYPE

jb@59069

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 > $[Date-Formula]$9, [Date Worked]:[Date Worked], @cell < $[Date-Formula]$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

Date-Formula = 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 > $[Date-Formula]$9, [Date Worked]:[Date Worked], @cell < $[Date-Formula]$12, [Employee/Contractor]:[Employee/Contractor], $Name17, [XXXX Billable?]:[XXXX Billable?], 1)

Genevieve P.

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:

1. Date Criteria

Use the ISDATE function as a test formula to make sure that your referenced date fields ($[Date-Formula]$9 and $[Date-Formula]$12) are being returned as a date:

=IF(ISDATE($[Date-Formula]$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 Date-Formula 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 > $[Date-Formula]$9, [Date Worked]:[Date Worked], @cell < $[Date-Formula]$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 > $[Date-Formula]$9)

=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell < $[Date-Formula]$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

Genevieve P.

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?