Ask Your Question

Sum across multiple sheets in Calc?

asked 2018-02-19 11:27:49 +0200

m3110w gravatar image

How can I sum across multiple sheets in Calc? I've got multiple sheets (about 60) in my workbook and I've given all of them unique names. Creating the formula by typing in each name would take too long.

I looked for a Calc Extension to do this but didn't see any. Any ideas?


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-02-19 11:52:10 +0200

Lupp gravatar image

If you need to sum equally addressed (same rows, same columns) cell cell ranges from your sheets, you can use cuboids like in =SUM(MyFirstSheet.B2:MyLastSheet.K11;MySecondSheet.M2:MySecondLastSheet.P101) which includes all the respective ranges of sheets located between the first and the last named sheet. There are very few functions accepting cuboids on parameter positions, and you cannot apply array-evaluation to cuboids.

Having to sum this way across sheets may indicate identical structure of the sheets, based on a "one sheet per month" designs or the like. Such a design is often judged a beginners mistake by experienced spreadsheet users. The general advice is then either to use one sheet for the data and to filter the needed selection for a kind of reports or to move the task to a database. The first variant will only work well for at most a few thousand rows.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-19 11:27:49 +0200

Seen: 1,709 times

Last updated: Feb 19 '18