# Sum Every nth Row In Calc [closed]

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

Sort by » oldest newest most voted

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.

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.

( 2018-04-08 02:52:23 +0200 )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.

( 2018-04-09 11:39:50 +0200 )edit