CODEWITHSUNDEEP
excelexcel-formula
Andrew
Andrew

Reputation: 25

Nesting SUMPRODUCT and SUMIF?

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!

Upvotes: 1

Views: 2808

Answers (2)

OverflowStacker
OverflowStacker

Reputation: 1348

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.

Upvotes: 1

user11198948
user11198948

Reputation:

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.

Upvotes: 1

Related Questions