Summing Subtotals

[Exceller]

I need to sum the subtotals in a field, and it appears the best way to do it
is to add all of the cells that are above a blank cell. For example:

A
1 <FORMULA>
2 <heading>
3 5
4 7
5 12
6
7 2
8 3
9 5
10

The subtotals are in rows 5 and 9. I need to sum those (and all other
subtotals in this field) in cell A1. It looks like the best way to do this
would be to condition the formula to sum all cells above the blank cells
(cells A5 and A9--and all other cells above blanks).
How would I do this? I searched for an example of this on the web and in
this forum but haven't found anything on it.

Thanks you.

 

[Ron Coderre]

If you used the SUBTOTAL function to calculate the subtotals
then you can use the SUBTOTAL function at the bottom of the list.

(The SUBTOTAL function ignores other SUBTOTAL functions that are in its
referenced range.)

So....if your data is in A2:A100 with SUBTOTAL function interspersed in that
range,
this formula will return the correct Grand Total

A101: =SUBTOTAL(9,A2:A100)

Note: the 9 causes the function to return the sum

Other first parameter options
Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP


Alternatively, if you used the SUM function in *every* place a subtotal
should be (even if it's totaling just one item, the you can use this

A101: SUM(A2:A100)/2

But, I'd go with the SUBTOTAL function, if practical

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Exceller]

Thanks, Ron, but the formulas that I'm using to subtotal within that field
don't use the SUBTOTAL function. In fact, the formula used to subtotal ranges
in that field is the (brilliant) Index/Match formula you came up with for me
last week (thanks again)!
So, I think I need something that sums each cell above a blank cell.
Thanks.

 

[Ron Coderre]

Try this:

Using your posted example
A
1 <FORMULA>
2 <heading>
3 5
4 7
5 12
6
7 2
8 3
9 5
10

Then...
A11: =SUMIF(A4:A10,"",A3:A9)

Notice the staggered ranges
A4:A10 vs A3:A9

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Exceller]

Ron,
Sorry to get back with you so late. We had a blizzard here yesterday
(Columbus, OH) and I left the office early.
It works perfectly! Thanks so much for your help.
Regards,
Exceller