sum function with range dependant on date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carmen - 21 Jul 2006 18:11 GMT

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?

Signature

CARMEN HOVENDICK

Reply to this Message

Maistrye - 21 Jul 2006 18:59 GMT

Carmen Wrote:

> I have the following data
>
[quoted text clipped - 15 lines]
>
> Any ideas?

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

Signature

Maistrye

Reply to this Message

Carmen - 21 Jul 2006 19:23 GMT

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

Signature

CARMEN HOVENDICK

>
> Carmen Wrote:
[quoted text clipped - 24 lines]
>
> Scott

Reply to this Message

Maistrye - 22 Jul 2006 19:00 GMT

Carmen Wrote:

> Scott, thanks, but I also wanted to use this logic to change startin
> and
[quoted text clipped - 12 lines]
> --
> CARMEN HOVENDICK

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

Reply to this Message

Rate this thread: