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

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

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.

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

that’s true. The Condition is it