CODEWITHSUNDEEP
excelexcel-formula
AlmostThere
AlmostThere

Reputation: 647

sumproduct in different columns between dates

Im trying to sum between two dates across columns. If I had a start date input in Sheet1!F1 and an end date input in Sheet1!F2 and I needed to multiply column B times column E.

I can do sumproduct(Sheet1!B2:B14,Sheet1!E2:E14) which would result in 48 based on the example table below. However, I need to include date parameters so I could choose between dates 2/1/15 and 6/1/15 which should result in 20.

A       B       C       D       E
Date    Value1  Value2  Value3  Value4
1/1/2015    1      2       3    4
2/1/2015    1      2       3    4
3/1/2015    1      2       3    4
4/1/2015    1      2       3    4
5/1/2015    1      2       3    4
6/1/2015    1      2       3    4
7/1/2015    1      2       3    4
8/1/2015    1      2       3    4
9/1/2015    1      2       3    4
10/1/2015   1      2       3    4
11/1/2015   1      2       3    4
12/1/2015   1      2       3    4

Upvotes: 0

Views: 1073

Answers (1)

BigBen
BigBen

Reputation: 49998

Try,

=SUMPRODUCT((Sheet1!A2:A14>=Sheet1!F1)*(Sheet1!A2:A14<=Sheet1!F2)*Sheet1!B2:B14*Sheet1!E2:E14)

enter image description here

Upvotes: 1

Related Questions