How to Get Function to Use End of Sheet (Even When Adding New Cells to Column)

[u.s.s_demon]

Hello All!

I am currently working on a spreadsheet to show how many warranty claims we have made for our service department. I have a couple different functions that are on one sheet, and I constantly have to go to each function and edit properties for the function to display the total of the cells in one column, after I have added new cells with values. How do I get my functions to use the end of the column(s) instead of going to each function and editing them, or is that not possible? I have uploaded an image of the chart and a sample of the functions: =COUNTIF(F2:F430,“KTM”) & =SUMIFS(C2:C430,G2:G430,">="&DATE(2021,1,1),G2:G430,"<="&DATE(2021,12,31)).

What I want to happen is the C430, G430 & F430 to go to the last cell if I add new cells. EX: Sheet was at F430 but I added three new rows/cells of information and the end is now F433 not F430.

I’m still fairly new to LibreOffice and enjoy using this program much more than any Microsoft or Windows program, but still don’t understand a lot of functions and other things

. Thank you!

[mariosv]

A function like: =LOOKUP(2;1/(F:F<>0);ROW(F:F))
Return the last row with data in the column. What you can use with INDIRECT() function to get the desire address.

But seems to me in your case it’s easy to use column name:
=COUNTIF(F:F,“KTM”)
=SUMIFS(C:C,G:G,">="&DATE(2021,1,1),G:G,"<="&DATE(2021,12,31)).
The calculation ends after the last row with data. And I guess there is not a problem for the result, including the first line
But be aware that not all functions support it, e.g., SUMPRODUCT()

[u.s.s_demon]

Thank you!