Ask Your Question

Sum Every nth Row In Calc [closed]

asked 2018-04-05 02:57:39 +0100

PAlotta gravatar image

updated 2018-04-05 03:26:31 +0100

I need to sum every nth row in LO 6.0 Calc. I am trying to find out how many rows of a name and address list are populated so I can build a print range.

The column that is being used to test two rows for the presence of data contains a formula such as =IF(A12>0,1,0) to give a 0 for an empty row or 1 for a populated row. Underneath those two rows is a formula such as =IF(SUM(F11:F13)>0,1,0) to see if either row is populated.

I can tell if a group of rows contains data, but I need to know how many of these groups there are.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-04-09 11:40:05.483555

1 Answer

Sort by » oldest newest most voted

answered 2018-04-05 15:18:49 +0100

erAck gravatar image

updated 2018-04-05 15:23:13 +0100

Instead of =IF(A12>0;1;0) use =SUBTOTAL(9;A12>0) and then for the overall calculation use =SUBTOTAL(9;F11:F13) (with the proper range instead of F11:F13). Note that to match your description of two data rows your example should had been =IF(SUM(F11:F12)>0;1;0) or =IF(SUM(F12:F13)>0;1;0) instead.

SUBTOTAL ignores all results from formulas where a SUBTOTAL is present, hence here only sums (function 9 is Sum,Total) the cells that contain the =IF(SUM(F11:F12)>0;1;0) formula.

Btw, =SUBTOTAL(9;A12>0) here calculates the same as =IF(A12>0;SUBTOTAL(9;1);0) just shorter and quicker.

edit flag offensive delete link more


erAck, Thanks! I tried your suggestion and it works very well, far simpler that others I found that did not work out.

The fact that "SUBTOTAL ignores all results from formulas where a SUBTOTAL is present" should be documented in the Help. I wonder why it is not.

BTW, " =IF(SUM(F11:F13)>0,1,0) " was a copy-paste from a cell where I was looking at three rows and then deciding that two were only needed. Sorry for the confusion.

PAlotta gravatar imagePAlotta ( 2018-04-08 02:52:23 +0100 )edit

Help somehow does state it: "If a range already contains subtotals, these are not used for further calculations." The implications this may have are not made very clear though.

erAck gravatar imageerAck ( 2018-04-09 11:39:50 +0100 )edit

Question Tools

1 follower


Asked: 2018-04-05 02:57:39 +0100

Seen: 723 times

Last updated: Apr 05 '18