date lookup, summing numbers

  • Thread starter Thread starter Greg
  • Start date Start date
G

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 D1:D7
 
Try this:

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

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()),D2:D20)

Biff
 
Try this:

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

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()),D2:D20)

Biff
Click to expand...

Thank you! Appears to do the trick!!
 
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,D12:D22)

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.
 
Back
Top