Sum data from multiple spreadsheets

Discuss the spreadsheet application
Post Reply
bbeard
Posts: 1
Joined: Sat Apr 06, 2019 6:13 pm

Sum data from multiple spreadsheets

Post by bbeard »

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
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Combining data from multiple spreadsheets

Post by RoryOF »

Start by changing commas into semicolons, which OO uses as separators.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Sum data from multiple spreadsheets

Post by robleyd »

And remove the plus sign. The syntax is SUM(number1; number2; ... number30)
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sum data from multiple spreadsheets

Post by RusselB »

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

=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

=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.
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Sum data from multiple spreadsheets

Post by Lupp »

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

=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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sum data from multiple spreadsheets

Post by RusselB »

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.
User avatar
keme
Volunteer
Posts: 3691
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Sum data from multiple spreadsheets

Post by keme »

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
(8.48 KiB) Downloaded 89 times
Post Reply