Get the last non-empty cell in a column in Google Sheets

google-sheets worksheet-function google-sheets-formula
MichaelS picture MichaelS · Nov 14, 2011 · Viewed 207.4k times · Source

I use the following function 

=DAYS360(A2, A35)

to calculate the difference between two dates in my column. However, the column is ever expanding and I currently have to manually change 'A35' as I update my spreadsheet.

Is there a way (in Google Sheets) to find the last non-empty cell in this column and then dynamically set that parameter in the above function?

Answer

Sam Plus Plus picture Sam Plus Plus · Nov 17, 2011

There may be a more eloquent way, but this is the way I came up with:

The function to find the last populated cell in a column is:

=INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )

So if you combine it with your current function it would look like this:

=DAYS360(A2,INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ))

Related questions

How can I check if the values of multiple cells are equal?

Let's say I have 6 different cells (that are not all in a line). I want to check if the values in these cells are equal. How could I do this with a function? I'd want the function simply to display "…

Read More
Google sheets, if statement multiple conditions

Trying to make an if statement that will return 4 outcomes based on the contents of the cell If the cell is "Yes", "No", "NA", "Unknown", to return 0, 1, 2, 3 respectively. I can use If(A1="Yes", 0,1) but not sure how to handle …

Read More
The last & the first day of a month in Google Spreadsheets

What is the way to get the first and last day of the previous month in Google Spreadsheet? I tried several ways to solve this issue with no success.

Read More