Progressive Sum Based on Variable.

**robert_shindorf** · 01-23-2009, 08:53 PM

ok, this may be the same answer as the previous post i did, but if so, i can't figure that part out.

In O2, if M2 = 8, and N2 = 2008, then add O3 + 8 rows (O3:O10)

or, if M2 = 5, and N2 = 2009 then add O16 + 5 rows (O16:O20).

I'm ready for a simple solution that I don't understand, lol.

**davesexcel** · 01-23-2009, 08:59 PM

Try,
=IF(AND(M2=8,N2=2008),SUM(O3:O10),IF(AND(M2=5,N2=2009),SUM(O16:O20),""))

**robert_shindorf** · 01-23-2009, 09:08 PM

oops, i don't think i explained it properly...

ok, m2 will vary, it's linked to a different worksheet.... and dependent on it's value, depends on the number of rows needed. (if it's a 3, 3 rows, it it's a 9, 9 rows).

if it's 2008 in n2, then start counting the rows at o3, if it's 2009, then start at o16.

does that make more sense... it's a little harder than that dave! but, you did solve what i said!

**DonkeyOte** · 01-24-2009, 05:37 AM

Not that I'm a fan of OFFSET given it's volatility but here it might be quite useful:

=SUM(OFFSET($O$3,12*($N$2-2008),0,$M$2,1))

**robert_shindorf** · 01-24-2009, 08:59 PM

hmmm... that didn't seem to work... i'm not too familiar with the offset function either, i copied and pasted it, directly into cell O2.

**DonkeyOte** · 01-25-2009, 05:01 AM

change the 12* to 13*

Post a sample of your file if the above does not resolve the issue.

**robert_shindorf** · 01-26-2009, 05:58 PM

ahhh, that worked with the 13...

i don't understand how it works, if you wouldn't mind explaining it, i'd really appreciate it... thanks!

**DonkeyOte** · 01-26-2009, 06:09 PM

OFFSET(reference,rows,cols,height,width)

traditionally OFFSET is use to reference another cell from a starting position by "offsetting" by x rows and x cols...

eg

=OFFSET(D4,3,3)

would offset D4 by 3 rows and 3 columns - eg D4 -> G7 and return the result of that cell
(row offset if < 0 goes "up" and col offset if <0 goes "left")

Height & Width are used less often but essentially can be used to specify the size of the new range...

=OFFSET(D4,3,3,10,2)

would offset D4 as before to G7 but would create a new range 10 rows high and 2 columns wide, eg: G7:H16, however, when used in this context you need to "do something" with the range created... in your case SUM

=SUM(OFFSET(D4,3,3,10,2))

will SUM the contents of G7:H16

So in your case the variables are the amount of rows by which you need to offset from O3 and the height of the new range... the row can be determined by multiplying 13 * (year - 2008) -- so if looking at 2009 you will offset from O3 by 13 rows, were the year 2010 you would offset by 26 rows... the height of the final range is determined by the value in M2.