sum function with range dependant on date

C

Carmen

I have the following data

1/1/06 50
2/1/06 40
3/1/06 30
4/1/06 20

In January, I want to sum all four rows (50 through 20). In February, I only want to sum the last three rows (40 through 20). In March, 30 through 20 and in April 20 through 20. I want to tell the sum function which month to start by referring to a cell with the month (i.e. 2/1/06 for the 40 through 20 sum).

I currently use a "sum" function with an "offset" function embedded in it (with the offset from 50 depending on which month I am in) but would like to use some kind of "lookup" to the cell with the month in it to tell the "sum" where to start.

Any ideas?
 
M

Maistrye

Carmen said:
I have the following data

1/1/06 50
2/1/06 40
3/1/06 30
4/1/06 20

In January, I want to sum all four rows (50 through 20). In February,
I only want to sum the last three rows (40 through 20). In March, 30
through 20 and in April 20 through 20. I want to tell the sum function
which month to start by referring to a cell with the month (i.e. 2/1/06
for the 40 through 20 sum).

I currently use a "sum" function with an "offset" function embedded in
it (with the offset from 50 depending on which month I am in) but would
like to use some kind of "lookup" to the cell with the month in it to
tell the "sum" where to start.

Any ideas?
Click to expand...


I don't think this is exactly what you're looking for, but it might
work:

=SUMPRODUCT(--($A$1:$A$4>=A1),$B$1:$B$4)

Scott
 
C

Carmen

Scott, thanks, but I also wanted to use this logic to change starting and
ending points for XNPV calculations, such as

XNPV(10%, A1:A4, B1:B4), XNPV(10%, A2:A4, B2:B4), XNPV(10%, A3:A4, B3:B4),
XNPV(10%, A4:A4, B4:B4),

with the range depending on what date I enter in another cell C1 (the date
in C1 would of course correspond to a date in B1 to B4), some kind of
"lookup" function embedded in the XNPV function?

Thanks
 
M

Maistrye

Carmen said:
Scott, thanks, but I also wanted to use this logic to change startin
and
ending points for XNPV calculations, such as

XNPV(10%, A1:A4, B1:B4), XNPV(10%, A2:A4, B2:B4), XNPV(10%, A3:A4
B3:B4),
XNPV(10%, A4:A4, B4:B4),

with the range depending on what date I enter in another cell C1 (th
date
in C1 would of course correspond to a date in B1 to B4), some kind of
"lookup" function embedded in the XNPV function?

Thanks
Click to expand...

Ok, I'm assuming based on what you said that C1 will equal one of th
values from A1:A4.

Try this formula, and fill down.

=XNPV(10%
OFFSET($B$1,MATCH(A1,$A$1:$A$4,0)-1,0,MATCH($C$1,$A$1:$A$4,0)-MATCH(A1,$A$1:$A$4,0)+1,1)
OFFSET($A$1,MATCH(A1,$A$1:$A$4,0)-1,0,MATCH($C$1,$A$1:$A$4,0)-MATCH(A1,$A$1:$A$4,0)+1,1))

It's a bit long, but basically the OFFSETs set your range to be fro
the starting value corresponding to the row it's in to the ending valu
in C1. Both are identical, except that one is based on B1 and one o
A1.

You will get a #REF error for each row in A that has a date greate
than C1. You might want to put this formula inside of an IF(C1>A1,""
[Above Formula]) to get rid of the #REF error.

Scot
 
Top