date lookup, summing numbers

[Greg]

hello everyone,

I'm looking for some simple help (or i hope simple) on doing a date
lookup in one column, then comparing it to a list of numbers and
giving a sum.

So I'd want to possibly use the Today()-10 function so it will go back
ten days, and know I'm talking about March 4th, from that, I'd like to
sum the numbers in another column and give me the total.

..... The background on this is to make my job easier, giving me the
abality to know right away if any of my employees have any "points"
for the last 10 days, 30, days, 50, days etc...

I'll try to show an example of what I want...


|A |B |C |D |E F|
1 Date X text Y Text Number
2 3/2/07 xxx yyy 1
3 3/3/07 xxx yyy .5
4 3/4/07 xxx yyy 0
5 .
6 .
7 .
8 Range to be A1:A7 and to sum D17

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A2:A20>=TODAY()-10),--(A2:A20<=TODAY()),D220)

Just change the -10 for other intervals: -30, -50 etc

Or, use a cell to hold the interval and just change the value in the cell:

H1 = 10

=SUMPRODUCT(--(A2:A20>=TODAY()-H1),--(A2:A20<=TODAY()),D220)

Biff

 

[Greg]

Try this:

=SUMPRODUCT(--(A2:A20>=TODAY()-10),--(A2:A20<=TODAY()),D220)

Just change the -10 for other intervals: -30, -50 etc

Or, use a cell to hold the interval and just change the value in the cell:

H1 = 10

=SUMPRODUCT(--(A2:A20>=TODAY()-H1),--(A2:A20<=TODAY()),D220)

Biff

Thank you! Appears to do the trick!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thank you! Appears to do the trick!!

 

[Sandie Scrivens]

Hi Greg

It is a simple answer. You need the SUMIF function

Assuming your dates are in the array A12-A22 and the values you want to SUM
are in the array D12-D22 the following will sum the values whose date is
Today()-10

=SUMIF(A12:A22,TODAY()-10,D1222)

Today being 25 May

A B C D E F

12 22/05/2007 1
13 13/05/2007 1 7
14 14/05/2007 3
15 15/05/2007 1
16 16/05/2007 4
17 15/05/2007 1
18 18/05/2007 5
19 15/05/2007 3
20 20/05/2007
21 15/05/2007 2
22 22/05/2007 1

Hope that gelps you.