I’m counting some values in my table with COUNTIF in Libreoffice Calc, however the target spreadsheet is always getting larger and I need to update the formulas regularly to cover all of the range with data.
Is there any way to define a range with a lower bound, but with no upper bound (e.g. whole column A from row 10 to bottom)? I’ve searched the net but was unable to find anything.
Thanks in advance.
Max row number is currently 1048576, so you could give the range with a mixed relative/absolute address:
A10:A$1048576.
Older software or different titles may have a different “max rows” value. Also, some scenarios do not work well with this kind of addressing (row insertion/deletion is one possible pitfall).
Use A:A for entire column A. Use OFFSET() to move starting point, but then you also need to reduce number of rows to avoid error.
OFFSET(A:A;10;0;ROWS(A:A)-10;1)
To have a dynamic offset value, you can use the ROW() function with a relative cell address instead. This way you can copy formula up/down and have the starting point follow, while retaining the “bottom” end point. ROW() without a parameter returns the row number for current cell (where the formula is).
OFFSET(A:A;ROW();0;ROWS(A:A)-ROW();1)
INDIRECT() may be another way to reach your goal.
Please add a comment if this does not address your issue.
More detail about your layout can be added as a comment to your question (or perhaps an attached file - edit your question and use the paperclip icon), and may yield a better solution.
Thanks! These solutions seems to meet my needs. I’ll try them, and will comment if I need further help.