## Sum data from multiple spreadsheets

### Sum data from multiple spreadsheets

SUM('2009'!L3,+'2010'!L3,+'2011'!L3,+'2012'!L2,+'2013'!L2+'2014'!L2+'2015'!L2+'2016'!L2+'2017'!L2+'2018'!L2+'2019'!L2) This is the formula that I use in Excel to combine date from my other sheets but doesn't seem to work in Open Office. What do I need to change?? Thanks.
Open Office 4.1.5 on Windows 10
bbeard

Posts: 1
Joined: Sat Apr 06, 2019 6:13 pm

### Re: Combining data from multiple spreadsheets

Start by changing commas into semicolons, which OO uses as separators.
Apache OpenOffice 4.1.8 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 31695
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: Sum data from multiple spreadsheets

And remove the plus sign. The syntax is SUM(number1; number2; ... number30)
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3560
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Sum data from multiple spreadsheets

Also the sheet names don't need the ' character around the names, as the names don't contain spaces and the sheetname cell separator is a . not !

A simpler formula would look like
Code: Select all   Expand viewCollapse view
`=2009.L3+2010.L3+2011.L3+2012.L2+2013.L2+2014.L2+2015.L2`

Another option is to use ranges. Based on the formula I posted earlier, the formula with ranges would look like
Code: Select all   Expand viewCollapse view
`=sum(2009.L3:2011.L3;2012.L2:2015.L2)`

The cell address is the same for each sheet in the range.
Adjust for the actual number of sheets and cell addresses you have.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6209
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Sum data from multiple spreadsheets

Welcome to the forum, "bbeard".
(This is also to explain the answer by RusselB which I expect not to work in the given way due to the lacking apostrophes. As I just tested, the recent versions of AOO and of LibO as well will insert the mandatory apostrophes automatically if they "feel sure" that's what the user intended. Bad again, imo.)

...(Deleted.)
A variant using so-caled cuboids:

Code: Select all   Expand viewCollapse view
`=SUM(y2009.L3:y2011.L3)+SUM(y2012.L2:y2019.L2)`
(...)
Cuboids are a way to include ranges of equal size and position from many adjacent (in tab order) sheets.
(Overly wordy parts deleted.)
...Most likely your sheets (their tabs) are ordered strictly ascending. In this case your formula can express the concept "from this year to that one" in a very clear and simple way. A formula term like y2001.B8:y2031:K27 can cause the rectangular range B8:K27 for each sheet starting with y2001 and ending with y2031 to be included with a SUM in one go. Supposedly the example includes 6000 cells by a reference of 18 characters. There aren't many functions accepting these so-called cuboids, but where applicable, the concept is efficient. Expressly: Please note that the "from ... till ..." not is interpreted by the lexicographic order of the names, but by the actual order of the sheets.
...(Deleted.)
Thanks to keme for his important clarifications below.
Last edited by Lupp on Mon Apr 08, 2019 9:37 am, edited 1 time in total.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2936
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Sum data from multiple spreadsheets

I expect not to work in the given way due to the lacking apostrophes

@Lupp: My second code was tested before I posted it. Since the sheet names do not include the space character, the apostrophes are not required.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6209
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Sum data from multiple spreadsheets

A tad off topic, but I take my chance...

As there are two main approaches suggested, namely using the SUM() function vs. using the + operator, explanation of an important difference between the two may be in order:
When you use the + operator, you are telling Calc to expect a number from each cell. If a cell contains a text, Calc will try to interpret that text numerically. If that cannot be determined, the formula will return an error.
When you use the SUM() function, you tell calc to sum all numbers in the referenced cell range(s). If a referenced cell contains a text, regardless whether the text may represent a number or not, it will count as zero.
Attachments
sum_vs_plus.ods

keme
Volunteer

Posts: 3390
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway