SUMIF Formula

Dina B

Hi - I am having a problem with my SUMIF formula when it contains the 'Scheduled' column. My formula works if I remove the "Scheduled' column, but when I add that column, I get the message '#Invalid Data Type'. I need to have it included. I attached a screenshot for more information. Any help would be appreciated. Thank you.

Nick Korna

Hi @Dina B,

You have some blank rows in your Date Manufacturing column which is what's causing the error with your SUMIFS.

You can get round this by using something like this:

=SUMIFS(Shipped:Shipped, [Date Manufacturing]:[Date Manufacturing], AND(@cell > (DATE(2021, 12, 31)), @cell < DATE(2023, 1, 1))) + SUMIFS(Pending:Pending, [Date Manufacturing]:[Date Manufacturing], AND(@cell > (DATE(2021, 12, 31)), @cell < DATE(2023, 1, 1))) + SUMIFS(Scheduled:Scheduled, [Date Manufacturing]:[Date Manufacturing], AND(@cell > (DATE(2021, 12, 31)), @cell < DATE(2023, 1, 1)))

You could switch the dates to exact start/end of the year and the greater/less than to greater/less than or equal to, but either is fine.

Example showing this in action (using just a scheduled column):

Hope this helps, but if I've misunderstood anything or you've still any problems/questions then let us know!

Nick Korna

Hi @Dina B,

You have some blank rows in your Date Manufacturing column which is what's causing the error with your SUMIFS.

You can get round this by using something like this:

=SUMIFS(Shipped:Shipped, [Date Manufacturing]:[Date Manufacturing], AND(@cell > (DATE(2021, 12, 31)), @cell < DATE(2023, 1, 1))) + SUMIFS(Pending:Pending, [Date Manufacturing]:[Date Manufacturing], AND(@cell > (DATE(2021, 12, 31)), @cell < DATE(2023, 1, 1))) + SUMIFS(Scheduled:Scheduled, [Date Manufacturing]:[Date Manufacturing], AND(@cell > (DATE(2021, 12, 31)), @cell < DATE(2023, 1, 1)))

You could switch the dates to exact start/end of the year and the greater/less than to greater/less than or equal to, but either is fine.

Example showing this in action (using just a scheduled column):

Hope this helps, but if I've misunderstood anything or you've still any problems/questions then let us know!

Dina B

@Nick Korna Thank you!