Sumifs returning #INVALID DATA TYPE when referenced range contains a formula
I am creating a per week rollup that shows a sum of the children rows from another sheet. It looks to me like SUMIFS does not work if the range referenced contains a formula. Is this a bug, limitation, or do i have an error in my formula?
=SUMIFS({ManpowerPerTaskApp}, {ManpowerWorkingStart}, WEEKNUMBER($Date@row) = WEEKNUMBER(@cell), {ManpowerWorkingStart}, YEAR($Date@row) = YEAR((@cell) - (WEEKDAY(@cell) - 1) + 1))
This is returning #INVALID DATA TYPE when the values in {ManpowerPerTaskApp} are the formula:
=SUM(CHILDREN(App@row))
The exact same SUMIFS formula works fine if the {ManpowerPerTaskApp} range points to data that are numbers (such as the data in the 'App' column on the referenced sheet) as opposed to number as a result of a formula
Comments
-
When using functions that look at dates, you will get that same error if there are any blanks or non-date type cells within the range to include text or plain numerical values.
Try wrapping function that references a date in an IFERROR function to replace the invalid data type with a 0 (zero). So instead of
YEAR(@cell)
you would have
IFERROR(YEAR(@cell), 0)
-
Thank you Paul! Our implementation manager at smartsheet got back to me at the same time with the same solution. For others, here was the solution:
=SUMIFS({ManpowerPerTaskApp}, {ManpowerWorkingStart}, WEEKNUMBER($Date@row) = IFERROR(WEEKNUMBER(@cell), 0), {ManpowerWorkingStart}, YEAR($Date@row) = YEAR((@cell) - (WEEKDAY(@cell) - 1) + 1))
-
Happy to help!
I also notice that you are using the YEAR and WEEKDAY functions as well. These are both also susceptible to the same issue.
.
.
A pointer...
When dealing with formulas that reference a few ranges, it doesn't make too much a difference, but if you end up using a formula that references a lot of different ranges and data types, I have found the incorporating AND and OR statements really helps with consolidation and organization.
=SUMIFS({Sum Range}, {Date Range}, WEEKNUMBER($Date@row) = IFERROR(WEEKNUMBER(@cell), 0), {Date Range}, YEAR($Date@row) = YEAR((@cell) - (WEEKDAY(@cell) - 1) + 1))
can also be written as
=SUMIFS({Sum Range}, {Date Range}, AND(WEEKNUMBER($Date@row) = IFERROR(WEEKNUMBER(@cell), 0), YEAR($Date@row) = YEAR((@cell) - (WEEKDAY(@cell) - 1) + 1)))
.
It may not seem like much in the above example, but if you have 6 or 7 ranges and some are referenced more than once, it really helps to tidy things up.
.
.
I am also rather curious about this little bit:
.................... YEAR($Date@row) = YEAR((@cell) - (WEEKDAY(@cell) - 1) + 1) .......................
.
I read it as equaling
The year of last Friday after 1 is added to the year.
So you are basically going to last Friday and adding 1 to the year?
.
