Sum of all formulas in one column?
 

Is there some automatic way to make Excel total all of the cells containing
formulas in a column? I know about the Subtotals feature, and being a new
user, I can't quite figure out how to work it. I got to thinking if there is
some easy formula that SUMs the results of all formulas in the same column,
that would do exactly what I want it to do. Right now, all I'm doing to
create a grand total is creating a formula manually to give me my grand
total, i.e. =SUM(C5,C11,C18). However, if I'm going to add rows with
subtotals in between each of my categories, I'll have to manually add the
cell address of the new formula into the grand total formula. Is there a
nice, automatic way that I'm not finding, to do this, so that any new cell
with results in it is automatically added to the grand total?

You really want to go with SUBTOTAL

If you use the SUBTOTAL function with the SUM argument of 9 to do all your
totals within the column, eg the formulas within C5,C11,C18 which would
probably be

=SUBTOTAL(9,C1:C4) ( Same result in this case as =SUM(C1:C4) )
=SUBTOTAL(9,C6:C10) ( Same result in this case as =SUM(C6:C10) )
=SUBTOTAL(9,C12:C17) ( Same result in this case as =SUM(C12:C17) )
respectively

Then by using =SUBTOTAL(9,A1:A18) it will sum all of that range EXCEPT any
other SUBTOTAL formula in that range, so you avoid the double count.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Claudia" wrote in message
...
Is there some automatic way to make Excel total all of the cells
containing
formulas in a column? I know about the Subtotals feature, and being a new
user, I can't quite figure out how to work it. I got to thinking if there
is
some easy formula that SUMs the results of all formulas in the same
column,
that would do exactly what I want it to do. Right now, all I'm doing to
create a grand total is creating a formula manually to give me my grand
total, i.e. =SUM(C5,C11,C18). However, if I'm going to add rows with
subtotals in between each of my categories, I'll have to manually add the
cell address of the new formula into the grand total formula. Is there a
nice, automatic way that I'm not finding, to do this, so that any new cell
with results in it is automatically added to the grand total?