How do I sum different sized ranges?

[darkfeld]

I am working in Microsoft Excel 2003 (but I do have access to Excel 2007 if
needed). I am trying to sum certain ranges in multiple lines of data. An
example of my spreadsheet is below.

The way it is now, the amount in the "END BALANCE" row is not including the
amount in the CDU field (row 1, 8 and 17). I need to be able to sum rows 1-5
with results on row 6 and then sum rows 8-14 with results on row 15, etc. I
can put the new results in a new column, I just need them on the same rows.
Any ideas as to how I can accomplish this by using a formula?

A B C
1 NAME CDU 3.42
2 INCOME 1.31
3 BOND 1.29
4 BALANCED 5.42
5 LARGE-CAP 3.05
6 END BALANCE 11.07
7 0
8 NAME CDU 5.42
9 INCOME 6.31
10 BOND 18.29
11 BALANCED 53.42
12 LARGE-CAP 35.05
13 MID-CAP 2.00
14 WORLD 5.00
15 END BALANCE 120.07
16 0
17 NAME CDU 15.00
18 INCOME 13.00
19 END BALANCE 13.00
20 0

 

[TomPl]

I think the best way to accomplish this is with a Macro. Try the one I
posted to your other question.

Tom

 

[T. Valko]

Try this:

Rows 7,16,20 should be empty.

A6, A15, A19 = Ending Balance
B6, B15, B19 leave these cells empty

Enter this formula in cell C2 and copy down as needed:

=IF(A2="","",IF(B2="",SUM(B$1:B2)-SUM(C$1:C1),""))

 

[Ashish Mathur]

Hi,

In situations, where you want a subtotal and then a grandtotal, use the
SUBTOTAL(,range) function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com