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

[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?

 

[Mike H]

You could try this:-

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

Mike

 

[Ron Coderre]

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)