Welcome!

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

Find more posts tagged with

Comments

Paul Newcome

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)

jonny_b

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))

Paul Newcome

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Slow loading
Helloe Everyone! I’m trying to reference another sheet in Smartsheet, but I’ve noticed that sometimes it loads quickly, while other times it takes a very long time to display the list. Does anyone know why this happens? Is there a way to reduce how often this issue occurs? Today, I can’t get anything to load at all. 😑
Report that includes multiple sheets
Hello - Is there a work around in a Report to list multiple sheets in an order that I choose? Sorting doesn't give me the order in which I want them to appear in the report or in a widget on a dashboard. I would like them in a specific order. Thank you.
Revise Filter in Dashboard
I need to revise/expand a filter - the list of items in the filter NOT the filter name - in a dashboard I created. Is this done through the dashboard or through the main data sheet?