to sum the quantity of only applicable for last 7 days from today

  • Thread starter Thread starter Narnimar
  • Start date Start date
N

Narnimar

I have a column with dates and another with quantities. I need a sum of the
quantity of only applicable for last 7 days from today.

Dates Quantities
11/12/2007 3
13/12/2007 4
14/12/2007 1
16/12/2007 5
17/12/2007 3
19/12/2007 2
20/12/2007 2
22/12/2007 1
23/12/2007 2
24/12/2007 3
25/12/2007 1
27/12/2007 3
28/12/2007 2
29/12/2007 5
31/12/2007 5
01/01/2008 3
03/01/2008 2

What would be the formula?
 
You could try this:-

=SUMPRODUCT(--(A1:A1000>=TODAY()-7),--(A1:A1000<=TODAY()),B1:B1000)

Mike
 
Perhaps an atypical approach:

This formula returns the sum of items
from today+1 through today+7,(12/22 through 12/28)

=SUMPRODUCT((A2:A100<=TODAY()+{-1,7})*B2:B100*{-1,1})

In your example, the formula returns 12.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

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