Help with sum based on dates

[Guest]

I have a column that contains dates & quantities I would like to sum based on
date ranges that I would define. For example I would want the sum quantities
whose dates fall in-between Nov. 11 & Nov 17. Nov. 18 & 24….. So on & so
forth. I have been trying to figure out a way to do this with maybe a v
lookup or pivot table. However I have not been able to do so. If anyone has
any ideas as to how I can do what I want to do I would be very grateful.
Thanks in advance.

 

[Don Guillett]

=sumproduct((a2:a22>a1)*(a2:a22<=b1))
sum
=sumproduct((a2:a22>b1)*(a2:a22<=b2)*b2:b22)

 

[Guest]

One way, something along these lines

Assuming source dates (real dates) within A2:A100, amounts in B2:B100
Then in say, D2
=SUMPRODUCT((A$2:A$100>=--"11-Nov-2007")*(A$2:A$100<=--"23-Nov-2007"),B$2:B$100)
will return the sum of amounts for dates between 11 Nov 2007 to 23 Nov 2007
(inclusive)

 

[Ron Coderre]

Try something like this:

With
Col_A containing dates, A1: Dates
Col_B containing quantities, B1: Qty

And
D1: Week Beginning
D2: (a WeekStartDate...eg 11-NOV-2007)

This formula returns the sum of Qty for the 7 days beginning with the
WeekStartDate in D2:
E2: =SUMPRODUCT(($A$2:$A$50<(D2+{0,7}))*($B$2:$B$50*{-1,1}))

or...in a longer form:
E2: =SUMPRODUCT(($A$2:$A$50>=D2)*($A$2:$A$50<D2+7)*($B$2:$B$50))

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)