# How do I find the bottom value in a column? [closed]

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

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.)

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-17 00:51:11.446924

Sort by » oldest newest most voted

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)

more

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

( 2013-03-13 10:09:15 +0200 )edit

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.

( 2015-10-24 07:10:26 +0200 )edit

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.

more