Sum all sums

[markh1182]

Hi, Is it possible to right a formula that would sum all the sums in a spreadsheet? I have in column O in various cells =SUM(Ox:Ox). At the bottom of the spreadsheet I would then like to sum up, all these sums.
Thanks, Mark

 

[erik.van.geit]

Hi, Mark,

did you try using SUBTOTAL (see menu data)
if that's impossible how would the SUM-formula-cells be recognized ?

if there are no other values involved, you could just total the entire column and divide by 2

   N          O          
 1            VALUES     
 2            1          
 3            2          
 4            3          
 5 SUM        6          
 6            NEXT RANGE 
 7            5          
 8            4          
 9 SUM        9          
10                       
11 TOTAL SUMS 15         

test
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
O5      =SUM(O2:O4)
O9      =SUM(O7:O8)
O11     =SUM(O2:O9)/2

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

EDIT: alternatively (using same sample data)
=SUMIF(N2:N9,"SUM",O2:O9)

 

[wavemehello]

Hi there, thats perfect, but I could not understand this divide by 2.
Even if there are 3 or more subtotal divide by 2 works correct. What is the reason behind this divide by 2? Sorry for my ignorance

 

[Krishnakumar]

Hi,

Range("O" & Rows.Count).End(xlUp).Offset(1) = "=sum(" & Columns("O:O").SpecialCells(xlCellTypeFormulas, 1).Address & ")"

 

[Barry Katcher]

Here's an explanation of Erik's "divide by two". Do the math!

Book1
	A	B	C	D
1
2		1	1	1
3		1	1	1
4	Sub-total		2	2
5
6
7		1	1	1
8		1	1	1
9		1	1	1
10	Sub-total		3	3
11
12
13	Total	5	10	5
14		SUM	SUM	SUM/2
Sheet1

 

[wavemehello]

Sorry for my ignorance,
I still could not get it, unless it is a theorem that "sum of all the numbers and their subtotals divided by 2 is equal to the sum of their sub total"?

 

[Domenic]

Basically, if SUM adds both original values along with their subtotals, the values are being summed twice. Hence, divide by 2.

Hope this helps!

 

[wavemehello]

Thank you Domenic,

Its crystal clear now!!

 

[erik.van.geit]

Its crystal clear now!!

thanks, guys, for jumping in while I was busy
loving this teamwork

 

[Domenic]

...loving this teamwork

Yep, same here!