Nesting SUMPRODUCT and SUMIF?

1
votes

I'd like to include a SUMPRODUCT within a SUMIF formula.

I'm trying to calculate the sum of product of two cells for a specific row, within a date range.

Right now, I have:

=SUMIFS(A12:E12,$A$10:$E$10,">="&DATE(2018,10,1), $A$10:$E$10, "<="&DATE(2019,9,30))

It's currently summing the entire row for the parts that fall within these dates. I'd like it to sum the entire row for the part falling within these dates, but instead of just summing the row, I'd like it to sum the product of A12 * A9, B12 * B9, etc.

Ideally it would look something like this:

=SUMIFS(SUMPRODUCT($A$9:$E$9,A12:E12),$A$10:$E$10,">="&DATE(2018,10,1), $A$10:$E$10, "<="&DATE(2019,9,30))

But that doesn't work.

Any suggestions would be great, thanks!

2
excelexcel-formula
Your second formula isnt' working, because the sumproduct gives a value, but the SUMIFS function needs an array. As array-formula (close with CTRL + SHIT +ENTER) =SUMIFS(($A$9:$E$9)*(A12:E12),$A$10:$E$10,">="&DATE(2018,10,1), $A$10:$E$10, "<="&DATE(2019,9,30)) should work without the sumproduct, but i didnt test it. – OverflowStacker

2 Answers

1
votes

Use an old style array formula with nested IF conditions.They are much more versatile than trying to marry forced cyclic processing with functions that already use cyclic processing.

=SUM(IF($A$10:$E$10>=DATE(2018,10,1), IF($A$10:$E$10<DATE(2019,10,1), A9:E9*B12:E12)))

Finish this with ctrl+shift+enter, not just enter.

In case the dates in A10:E10 contin a time value I've modified your less than to include all of 30-Sep-2019 up to midnight.

1
votes

Looks like a sumproduct would be sufficient, as it can also take conditions.

=SUMPRODUCT(($A$9:$E$9)*(A12:E12)*($A$10:$E$10>=DATE(2018,10,1))*($A$10:$E$10<=DATE(2019,9,30)))

But without your sample data i can't tell if this works would work on your data. For my sample data the first 2 arrays got multiplied and summed up if the date was between the 2 conditions.