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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Mar 15, 1:22 am, "T. Valko" <biffinp...@comcast.net> wrote:
> 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" <tribezspam...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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

Thank you! Appears to do the trick!!

You're welcome. Thanks for the feedback!

Biff

"Greg" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 15, 1:22 am, "T. Valko" <biffinp...@comcast.net> wrote:
>> 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" <tribezspam...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > 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
>
> 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,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.