SELECT until endless in LibreOffic Calc

Question

I am used to =COUNTA(B3:B999) and change it when the number of rows exceeds 999, but I was wondering whether there is an endless setting.

Attempt

=COUNTA(B3:B) did not work.

Art Gertner · Accepted Answer · 2015-01-18 22:20:03Z

4

Try following (B3 is used as the first cell in this example):

Get remaining rows from current cell to the end of the column:

65536-ROW($B$3)+1

Select range from current cell to the end of the column:

OFFSET($B$3,0,0,(65536-ROW($B$3)+1),1)

Then count:

=COUNTA(OFFSET($B$3,0,0,(65536-ROW($B$3)+1),1))

NOTE: If you are using LibreOffice Calc v3.3.3 or later, then use 1048576 instead of 65536

answered Jan 18, 2015 at 13:34

Art Gertner

7,27911 gold badges44 silver badges72 bronze badges

Since you seem to be using 65536 as the upper limit on the number of rows, what's wrong with the much simpler =COUNTA(B3:B65536)? I ask because I think I must be missing something.
– AFH
Jan 18, 2015 at 14:59
1

=COUNTA(B3:B65536) works fine until you start deleting rows in that range, when you delete rows the range will shrink in your formula
– Art Gertner
Jan 18, 2015 at 16:49
Also =counta(indirect("B3:B65535")) works - note that later Excel versions has 1M (2^20) rows, not only 64K (2^16).
– Hannu
Jan 18, 2015 at 17:08
1

OK, thanks. I've tested and found that the 2^20 limit mentioned by @Hannu also applies to LibreOffice, so you can use up to =COUNTA(B3:B1048576). That means you would need over a million line deletions before the formula change becomes a problem. But I'm glad to have been made aware of the effect.
– AFH
Jan 18, 2015 at 19:48

Add a comment |

1 Answer 1