Sum data from multiple spreadsheets

Discuss the spreadsheet application

Sum data from multiple spreadsheets

Postby bbeard » Sat Apr 06, 2019 6:21 pm

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

Postby RoryOF » Sun Apr 07, 2019 10:32 am

Start by changing commas into semicolons, which OO uses as separators.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29438
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Sum data from multiple spreadsheets

Postby robleyd » Sun Apr 07, 2019 11:46 am

And remove the plus sign. The syntax is SUM(number1; number2; ... number30)
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2936
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Sum data from multiple spreadsheets

Postby RusselB » Sun Apr 07, 2019 12:49 pm

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5398
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sum data from multiple spreadsheets

Postby Lupp » Sun Apr 07, 2019 1:34 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Sum data from multiple spreadsheets

Postby RusselB » Sun Apr 07, 2019 4:21 pm

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5398
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sum data from multiple spreadsheets

Postby keme » Mon Apr 08, 2019 3:54 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests