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

##### MrExcel MVP
...loving this teamwork Yep, same here! Replies
2
Views
51
Replies
4
Views
38
Replies
3
Views
75
Replies
12
Views
48
Replies
3
Views
16