CODEWITHSUNDEEP
google-sheets
Lyft
Lyft

Reputation: 81

Last value of a column in Google Sheets

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?

Upvotes: 1

Views: 6539

Answers (3)

player0
player0

Reputation: 1

or try:

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

Upvotes: 1

doubleunary
doubleunary

Reputation: 18784

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.

Upvotes: 3

Broly
Broly

Reputation: 921

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

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

Upvotes: 1

Related Questions