Last value of a column in Google Sheets

Question

I was trying to use the following function;

=INDEX(D:D,COUNTA(D:D),1),

in order to get the last currency value of a column, but it returns #ERROR!.

The value im trying to extract

As I montly update this spreadsheet, it would make it very convenient if would etract the last value in the column, e.g. the value marked in the image.

Is there a way (in Google Sheets) to find the last non-empty cell in this column, such that when I update the spreadsheet with a new "last value" it would return that value?

Answer 1

The index(counta()) pattern will fail when the data is sparse, i.e., when there are blank values in the column.

The index(match()) pattern will fail when the data contains a value that is not a number.

To find the last non-blank value in column D, regardless of data type, use the +sort(row()) pattern:

=+sort(D1:D; not(isblank(D1:D)) * row(D1:D); false)

The formula uses semicolons as argument separators to make it work in any locale.

Answer 2

If the column has only currency (ie number) values then you can use something like:

=INDEX(D1:D, MATCH(999^99, D1:D))

Answer 3

or try:

=SORTN(D:D; 1;;ROW(D:D)*(D:D<>""); )