SUMIFS formula

Dina B

In my Sheet Summary, I am using the formula =SUMIFS(Shipped:Shipped, [Date Manufacturing]:[Date Manufacturing], YEAR(@cell) = 2023) + SUMIFS(Pending:Pending, [Date Manufacturing]:[Date Manufacturing], YEAR(@cell) = 2023) + SUMIFS(Scheduled:Scheduled, [Date Manufacturing]:[Date Manufacturing], YEAR(@cell) = 2023) across four separate sheets. All of the sheets have the same column names. The formula is working for three of the four sheets. On the sheet that it is not working on, it reads "#invalid data type". If I delete the SUMIFS(Scheduled:Scheduled, [Date Manufacturing]:[Date Manufacturing], YEAR(@cell) = 2023) part of the formula, I no longer get the invalid message, but the formula doesn't include the sum of the Scheduled column. There must be a problem with the Scheduled column in this fourth sheet, but I cannot figure out what the issue is. Please help.

Nick Korna

Hi @Dina B,

Do you have a row with a value in Scheduled, but not in the Date Manufacturing or the other columns? This would trigger the error as the formula can't match the value to a date to check and would explain why the remainder of the formula works without the Scheduled SUMIF being included.

Paul Newcome

Try adjusting each of your YEAR functions like so:

YEAR(@cell) = 2023

changes to

IFERROR(YEAR(@cell), 0) = 2023

Dina B

@ Nick Korna Yes! That was it! Thank you SO much!!