# 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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### 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
0
Views
68
Replies
1
Views
102
Replies
7
Views
134
Replies
5
Views
135
Replies
5
Views
42

1,141,139
Messages
5,704,503
Members
421,353
Latest member
jekoxien15

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back