Prev: Two Level Search
Next: Concatenate with a specific number of spaces
From: StonyfieldRob on 27 May 2010 17:41
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

From: Steve Dunn on 28 May 2010 10:03
Hi,

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

Should do it.


"StonyfieldRob" <StonyfieldRob(a)discussions.microsoft.com> wrote in message
news:DA034767-FF28-4DA8-B2E2-D3BE54D649CE(a)microsoft.com...
> 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
>

 | 
Pages: 1
Prev: Two Level Search
Next: Concatenate with a specific number of spaces