# Sum complete column of other sheet

Hello Everybody,

I have created a Spreadsheet which contains a bunch of sheets. All of them are in format like:

Sheet A:

date     | amount
---------+--------
12/01/19 | 100
05/04/19 | 200

Sheet B:

date     | amount
---------+--------
09/10/19 | 140
06/08/19 | 270

Sheet C, D, E, F … all in all about 20


And in Sheet X I would like to summarize this like so:

Sheet | I | II | III | IV | Total
------------------------------------------
A    | <Sum of all values from $A.B, where Month($A.A) in {1,2,3}> | … | … | …
B    | <Sum of all values from $B.B, where Month($B.A) in {1,2,3}>


So basically I would like to see the Sum of the amount for each Sheet for each quarter of the year.

The Data in each Sheet may not be ordered…

How can I do this?

Thanks for Help!!!

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

for performance reasons, the solution in my example files assumes you have 100 rows in each of your sheets A, B, C, ... and the example file is restricted to 3 Sheets only.

See the following example file:

C:\fakepath\SUMIF-Quarter.ods

Note(s)

• Your dates must be real calc dates and not just text looking like dates (i.e. integer numbers).
• Sheet X (last sheet in the example file) contains the summary
• If your sheets contain more the 100 data rows, you need to update/adapt the ranges A1:A100 and B1:B100
• I strongly recommend not to use full column ranges (e.g. A:A, B:B) for performance reasons.
• Use of function INDIRECT() allows for column A in sheet X to contain your sheet names
• Use of function INT() calculates the quarter of the year a date belongs to (0=1st quarter, 1=2nd quarter,..)
• Adding additional sheets just requires a new line in sheet X, adding the sheet name to column A and copy the formula from the row above.
• The Check column in sheet X is to compare SUM of the 4 conditional partial sums with the total sheet sum. If they are not the same numbers, something must be wrong.

Tested using LibreOffice:

Version: 6.3.3.2, Build ID: a64200df03143b798afd1ec74a12ab50359878ed
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded


Hope that helps

more

Thanks! The formulas look super complicated, but I will try to get it!

To sum a column from another sheet you can simply use something like this:

=SUM($A.A:A)  This basically will give you the SUM of all numbers in column A of sheet A. To sum more columns together: =SUM($A.A:A,$B.A:A,$C.A:A)


This example sums all column A's from sheets named A, B and C.

more