Ask Your Question
1

Sum complete column of other sheet

asked 2019-11-06 12:38:51 +0100

hanswurst gravatar image

updated 2019-11-06 12:43:36 +0100

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

answered 2019-11-06 14:58:46 +0100

Opaque gravatar image

updated 2019-11-06 16:10:46 +0100

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

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

edit flag offensive delete link more

Comments

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

hanswurst gravatar imagehanswurst ( 2019-11-06 20:26:09 +0100 )edit

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

Opaque gravatar imageOpaque ( 2019-11-06 22:09:09 +0100 )edit
0

answered 2019-11-06 14:35:26 +0100

Chris Vella gravatar image

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.

edit flag offensive delete link more

Comments

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

Opaque gravatar imageOpaque ( 2019-11-06 14:43:10 +0100 )edit

that's true. The Condition is it :)

hanswurst gravatar imagehanswurst ( 2019-11-06 20:11:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-06 12:38:51 +0100

Seen: 40 times

Last updated: Nov 06