Get the last non-empty cell in a column in Google Sheets (excluding formulas)

Question

I have multiple Google Sheets that have several columns that are updated daily. All sheets are consolidated to a Summary page that updates when new data is entered into any of these sheets.

How do I find the last non-empty cell in each column of the Summary sheet, excluding cells that contain formulas returning a blank? For example, column A holds the date and it simply looks to see if a team leader has entered data in her sheet. If so, the date from her sheet is entered. The formula is:

=if(Cate!A709="","",Cate!A709

When I want to return the last date in the column, I try using this formula:

 =INDEX(A:A,COUNTA(A:A),1). 

It returns a blank because there are empty cells with formulas testing for future entries (like the "Cate" example above ... 710, 711, 712, etc.).

This works fine in Excel using =Last() ... but not so in Google Sheets.

Is there a simpler way to do this? I want the Summary sheet to look at all the other worksheets and provide a summary for each day, and I want to know what the most recent day's summary updated for my dashboard.

Answer 1

This worked for me!

We will use a combination of INDEX, MAX, COUNTA, and OFFSET functions.

Assuming you want to find the last non-empty value in column A, use the following formula:

=INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))

(A:A<>""): This creates an array of TRUE/FALSE values, where TRUE represents non-empty cells in column A.

ROW(A:A)*(A:A<>""): This multiplies the row numbers by the TRUE/FALSE array, giving an array of row numbers for non-empty cells and 0 for empty cells.

MAX(ROW(A:A)*(A:A<>"")): This finds the maximum row number from the array of row numbers, which corresponds to the last non-empty cell in column A.

INDEX(A:A, MAX(ROW(A:A)*(A:A<>""))): Finally, the INDEX function returns the value of the last non-empty cell in column A based on the maximum row number.

Make sure to adjust the column reference in the formula to match your actual data column. Replace all instances of "A:A" with the correct column letter (e.g., "B:B" for column B, "C:C" for column C, and so on).

Please try this formula, and it should give you the value of the last non-empty cell in the specified column. If it still doesn't work, please ensure that your data is contiguous (no empty rows within the range) and that you have data in the specified column.

Answer 2

to find the last cell from the range you can do this:

=QUERY(A2:A; "limit 1 offset "&(COUNT(A2:A)-1))

or even for the whole last row:

=QUERY(A2:C5; "limit 1 offset "&(COUNT(A2:C5)-1))