SumProduct Between Dates

S

StonyfieldRob

Having a tough time with this one.

Sheet 1
Column A = Start Date, Column B = End Date, Column C = Quantity.

Sheet 2
Row A = Start Date, Row B = End Date.
I would like Row C to sum quantity from sheet 1 where ever the two date
ranges intersect.
The date ranges on sheet 2 represent the beginning and ending of a week
(Mon-Sun).

Sheet 1
Column A Column B Column C
01JAN2010 24JAN2010 1,000

Sheet 2
Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010
Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010
Row c 1,000 1,000 1,000 0
 
S

Steve Dunn

Hi,

=SUMPRODUCT((A$1>=Sheet1!$A$1:$A$5)*
(A$2<=Sheet1!$B$1:$B$5)*Sheet1!$C$1:$C$5)

Should do it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct Question Working With Start and End Date Range 1
Sumproduct with multiple date criteria 1
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Reserved dates 3
find a cell in a range and copy another cell 1
Figuring out a lengthy formula. Probably quite simple... 3
Using SUMIF with dates 11
SumProduct with dates 4

Top