How do I sum different sized ranges?
 

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

How do I sum different sized ranges?
 

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

Tom

How do I sum different sized ranges?
 

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),""))


--
Biff
Microsoft Excel MVP


"darkfeld" wrote in message
...
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

How do I sum different sized ranges?
 

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

"darkfeld" wrote in message
...
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