Help with sum based on dates
 

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.

Help with sum based on dates
 

=sumproduct((a2:a22a1)*(a2:a22<=b1))
sum
=sumproduct((a2:a22b1)*(a2:a22<=b2)*b2:b22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DP7" wrote in message
...
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.

Help with sum based on dates
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DP7" wrote:
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.

Help with sum based on dates
 

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)

"DP7" wrote in message
...
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.