1

I want to a cell to just print what the last cell in a column. If I were to have the column starting on row 17,

1
3
5

I want it to print five, but if I updated the list to

1
3
5
7

I want it to print 7, but I know nothing about the functions of spreadsheets.

2

Formula

=INDEX(A17:A,COUNT(A17:A))

Explanation

Assuming that the data is on the column A, that it only has numbers, and that there are not blank cells between two cells with numbers, COUNT(A17:A) will return the count of cells having a number and it will be the index of the last cell with a number on column A. Index will return the value of the last cell that has a number in column A.

NOTES

  • If the values are TEXT instead of NUMBER use COUNTA instead of COUNT
  • If the values are mixed, TEXT and other type like NUMBER / DATE / TIME / DURATION , instead of the above formula use

    =ARRAYFORMULA(INDEX(A17:A, MAX(IF(ISBLANK(A17:A),,ROW(A17:A)))))
    

    or

    =ARRAYFORMULA(INDEX(A17:A, MAX(IF(LEN(A17:A),ROW(A17:A),)))))
    

See also

| improve this answer | |
  • It didn't quite work because my data doesn't start until row 17 in my document, but after that it is a continuous list. Is there a way to set it to start there? – user141093 Dec 6 '16 at 23:44
  • I edited your question and my answer. – Rubén Dec 6 '16 at 23:51
  • 1
    Also, if the values are not numbers, you will need to use COUNTA not COUNT. – JamesFaix Aug 28 '19 at 13:16
  • @JamesFaix Thanks. I updated my answer :) – Rubén Aug 28 '19 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.