Sum all sums

markh1182

New Member
Joined
Apr 25, 2006
Messages
48
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

Board Regular
Joined
Jan 24, 2006
Messages
221
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

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

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053

ADVERTISEMENT

Here's an explanation of Erik's "divide by two". Do the math!
Book1
ABCD
1
2111
3111
4Sub-total22
5
6
7111
8111
9111
10Sub-total33
11
12
13Total5105
14SUMSUMSUM/2
Sheet1
 

wavemehello

Board Regular
Joined
Jan 24, 2006
Messages
221
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,308

ADVERTISEMENT

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

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,329
Messages
5,528,033
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top