Variable Sums

**SeattleDuck** · 07-16-2009, 02:18 AM

Hello. First time on this forum, and I'm a fairly new excel user. Not bad, but not great.

My question:

I have an array, A1:C5. I want to sum up rows in the C column that depend on specific criteria USING columns A and B, and put them in Column D.

For example:
---A B C D
1 1 2 10 Results
2 2 3 12
3 3 1 9
4 4 2 10
5 5 2 6

For Row A Result, I want C1+C2, based on the fact that B1 is 2 (summing two rows in Column C starting with C1).

For Row B Result, I want C2+C3+C4 based on the fact that B2 is 3 (summing three rows in Column C starting with C2).

For Row C Result, I want C3 based on the fact that B3 is 1 (summing one row in Column C starting with C3).

For Row D Result, I want C4+C5 based on the fact that B4 is 2 (summing two rows in Column C starting with C4).

etc, etc...

Thanks for any help. I've been searching the internet all night and can't figure it out, seems like I've played with every function in the book. I can provide more detailed info if needed.

Thanks again!

**DonkeyOte** · 07-16-2009, 02:27 AM

You can use:

D1: =SUM(OFFSET(C1,,,B1,1))
copied down

Note: OFFSET is Volatile (see link in sig. for more info).

**squiggler47** · 07-16-2009, 02:30 AM

you could do

=SUM(INDIRECT("c"&A1&":"&"c"&(A1+A2-1)))

copied down in row D

**SeattleDuck** · 07-16-2009, 10:31 AM

DonkeyOte,

That worked perfectly! I can't believe I wasted so much time. I tried the OFFSET function earlier, but the Volatile result threw me off. Thanks for the help! Squiggler, thanks for the reply too, I'm sure your way would have worked, I just didn't try it.

Thanks again!
SeattleDuck