Sum all sums

markh1182

New Member
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

Excel Facts

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
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
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
Hi,

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

Barry Katcher

Well-known Member

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
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

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

Board Regular
Thank you Domenic,

Its crystal clear now!!

erik.van.geit

MrExcel MVP
Its crystal clear now!!
thanks, guys, for jumping in while I was busy
loving this teamwork

Replies
2
Views
51
Replies
4
Views
38
Replies
3
Views
75
Replies
12
Views
48
Replies
3
Views
16