I have previously written about how to find the top and bottom value of column in SQL Server but recently I got a very interesting question based on the same blog post. The question was how to find the last value from Column in Google Spreadsheet. I find it very interesting and attempted to find the solution in Google Spreadsheet.
Honestly, looking the problem of finding the last value indeed looked very challenging in the beginning as I have not used Google Spreadsheet in the past. If I had to find the maximum or minimum value, it was not that difficult as I can easily use the function MIN or MAX but when I had to find the last value – there was no LAST function. After some try and error I figured out the solution. It is very difficult to show the solution in the blog format so I have demonstrated image herewith, where I have demonstrated the function which I have used right below the column to find the last value.
The function which I have used is as follows: =INDEX(A2:A10, COUNTA(A2:A10))
Let me know if you find this quick tip useful.
Reference: Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
Nice
Thanks.
thanks pinal, so annoying google sheets don’t have a single function for this!
this is a good simple solution but I would like to think it took you hours. Why google sheets don’t have this as a single function is beyond belief!
Pinal, I have a problem here similar to that:
I have a spreadsheet like this:
A B C
daniel 5/1/2016 29000
pedro 5/1/2016 28000
vanessa 5/1/2016 27000
daniel 6/1/2016 29500
pedro 6/1/2016 28500
vanessa 6/1/2016 27500
I need to know the last value of each seller. In this example it would be:
daniel 29500
pedro 28000
vanessa 27500
Do you know how could I do it?
Thanks!
Hello Pinal
This solution does not work if all the cells in the column do not have values entered.
I have not tested that situation. Did you find any solution? please share.
Thanks Pinal
I found it very useful
Khaled – I am glad that you liked it.
Pinal, great solution here. Do you have any idea how we could use this to take the very last number of a column, and subtract it by the 2nd to last number?
This is really helpful. Any chance of a solution that ignores “0”?
OMG, thank you so much. I’ve been looking all over the place for this simple function. Annoying that Google Sheets doesn’t have the last in column function apparently. My problem is that I’m barely knowledgeable of Excel and I’m trying to find FX’s that work for Google Sheets. That helped me out sooooooo much. Thank you. I finally got the sheet to do what I needed it to do.
Thanks for the comment Jen.
Super old post but still works perfectly. I modified it to work for Rows as “=INDEX(C27:27, COUNTA(C27:27))”. Thanks for saving me the effort
Thanks BoB
Yes, this was very helpful. It even works on other worksheets, such as: =index(‘Recon Report YTD’!G:G, counta(‘Recon Report YTD’!G:G))
Matt – thanks for the comment.