We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Exclude cells from cumulative sum [closed]

asked 2014-03-19 15:36:17 +0200

LOU gravatar image

updated 2015-09-10 11:08:13 +0200

Alex Kemp gravatar image

Hi Libreoffice users,

This is a simple version of my spreadsheet in Libreoffice Calc : http://i.imgur.com/bgOTpWf.png

The C column starts with the formula =SUM($B$1:$B1) and the second one is =SUM($B$1:$B2) and so on, to calculate the cumulative sum of row B. Now I would like to automatically exclude the total cells from row C. (Because my own spreadsheet is really long)

Thanks in advance!

Version: LibreOffice Build ID: 350m1(Build:2) OS: Debian Wheezy

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:37:28.798859

3 Answers

Sort by » oldest newest most voted

answered 2014-03-19 16:55:45 +0200

erAck gravatar image

Use SUBTOTAL(9,...) instead of SUM(...), SUBTOTAL ignores results of formula cells that contain SUBTOTAL in their formula expression.

edit flag offensive delete link more

answered 2014-03-20 13:50:32 +0200

mahfiaz gravatar image

updated 2014-03-20 13:50:59 +0200

C1: =B1
C2...: =C1+IF(A2="Total", 0, B2)
edit flag offensive delete link more

answered 2014-03-19 18:42:47 +0200

jleslie48 gravatar image

I didn't know about subtotal, but generally I would just put subtotals in column D instead... Its a really bad Idea to mix values and subtotals in the same column. its very confusing, and if someone does a cut and paste into another document, you are gonna have a mess.

edit flag offensive delete link more


I agree that it's a bad idea to put values and subtotals in the same column, but I'm going to print the spreadsheet and put it in a ring binder ;-) The other spreadsheet looks different from the example, so it is not as confusing as the example.

LOU gravatar imageLOU ( 2014-03-19 20:01:05 +0200 )edit

Question Tools

1 follower


Asked: 2014-03-19 15:36:17 +0200

Seen: 1,599 times

Last updated: Mar 20 '14