How do I find the bottom value in a column?

[Asday]

Simply using a spreadsheet to manage my finances, and I’d like to be able to use my latest balance in formulae. It’s always going to be the lowest (position within the sheet) populated cell, in a certain column.

So far, I’ve got as far as

=CELL(“SHEET”, CELL(“ADDRESS”, CONCATENATE(“E”, 3+MATCH(".+",SheetName.E3:E99999, -1))))

With “SheetName” being whatever the sheet is called, and “E” being the column. It simply returns “#REF!”.

The 3 being added to the match is to account for the headers (column names, etc.)

[JohnSUN]

Another variant of the same formula (without “array”):

=SUMPRODUCT(MAX(ROW(E1:E99999)*(E1:E99999<>"")))

(The function SUMPRODUCT() takes an array as a parameter and returns a single value)

[qubit1]

Confirmed. This function will return the row number of last row in column ‘E’ that contains data.

[subone]

I don’t have any karma to upvote any of this, but thanks for asking this question, thanks for answering it, and thanks for adding the above comment which allowed me to find this.

[mariosv]

An array formula, like next, find the last no blank row, in the column A:

{=MAX(IF(ISBLANK(A2:A10);0;ROW(A2:A10)))}

Ctrl+Shift+Enter to enter the formula, not only Enter.