Sum complete column of other sheet

[hanswurst]

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!!!

[anon73440385]

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:

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

If the answer is correct or helped you to solve your problem, please click the check mark () next to the answer.

[hanswurst]

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

[anon73440385]

Please consider to click the check mark () next to the answer, indicating a correct answer.

[ChrisVella]

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.

[anon73440385]

As far as I understand OP, the question is about sum with condition.

[hanswurst]

that’s true. The Condition is it