Sum data from multiple spreadsheets

[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.

[RoryOF]

Start by changing commas into semicolons, which OO uses as separators.

[robleyd]

And remove the plus sign. The syntax is SUM(number1; number2; ... number30)

[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   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.

[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   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.

[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.

[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.