Grandtotal of subtotals

  • Thread starter Thread starter Eva Shanley
  • Start date Start date
E

Eva Shanley

A user here has a goofy spreadsheet with multiple sums in
it. The reason she did not use automatic subtotals is
because the spreadsheet layout is not conducive to them.
Is there a way to get a total of just the subtotals in a
range? Thanks.
 
If the subtotals that you want to total are done using the subtotal formula
like

1
2
3
=SUBTOTAL(9,A1:A3) = 6
next


then you can total the whole range with

=SUBTOTAL(9,A1:A20)

and it will only pick up the subtotal formula values

if it is done by like

1
2
3
=sum(A1:A3)
next

then it will sum every value


However you can do as follows,
select the column with the sums
do edit>replace and replace =
with r=
do another replace and replace SUM(
replace with SUBTOTAL(9,
finally replace r= with =
now you can use the subtotal formula on the whole range to get a grandtotal
and it will
only pick up the subtotals

Now while doing this I found an even easier way
select the whole range, press F5, select special,
select formulas and click OK
now type =SUBTOTAL(9,A1:A3)
for only the first range you want to change
finish by pressing ctrl + enter

The last one will only work if the only formulas in the range are those
that needed to be changed so the rest must be constants
then press ctrl + enter and


--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top