Get the last non-empty cell in a column in Google Sheets

Question

I use the following function    DAYS360 A2  A35    to calculate the difference between two dates in my column  However  the column is ever expanding and I currently have to manually change  A35  as I update my spreadsheet   Is there a way  in Google Sheets  to find the last non-empty cell in this column and then dynamically set that parameter in the above function

User · Answer

What about this formula for getting the last value:

=index(G:G;max((G:G<>"")*row(G:G)))

And this would be a final formula for your original task:

=DAYS360(G10;index(G:G;max((G:G<>"")*row(G:G))))

Suppose that your initial date is in G10.

User · Answer

To find last nonempty row number  allowing blanks between them  I used below to search column A    ArrayFormula IFNA match 2 1  A A lt  gt

User · Answer

This will give the contents of the last cell    indirect  A  amp max ARRAYFORMULA row a a  -- a a lt  gt          This will give the address of the last cell     A  amp max ARRAYFORMULA row a a  -- a a lt  gt         This will give the row of the last cell    max ARRAYFORMULA row a a  -- a a lt  gt         Maybe you d prefer a script  This script is way shorter than the huge one posted above by someone else   Go to script editor and save this script   function getLastRow range     while range length gt 0  amp  amp  range range length-1  0       range pop      return range length      One this is done you just need to enter this in a cell    getLastRow A A

User · Answer

Although the question is already answered  there is an eloquent way to do it  Use just the column name to denote last non-empty row of that column   For example  If your data is in A1 A100 and you want to be able to add some more data to column A  say it can be A1 A105 or even A1 A1234 later  you can use this range  A1 A   So to get last non-empty value in a range  we will use 2 functions   COUNTA INDEX  The answer is  INDEX B3 B COUNTA B3 B    Here is the explanation  COUNTA range  returns number of values in a range  we can use this to get the count of rows  INDEX range  row  col  returns the value in a range at position row and col  col 1 if not specified  Examples  INDEX A1 C5 1 1    A1 INDEX A1 C5 1    A1   implicitly states that col   1 INDEX A1 C5 1 2    A2 INDEX A1 C5 2 1    B1 INDEX A1 C5 2 2    B2 INDEX A1 C5 3 1    C1 INDEX A1 C5 3 2    C2  For the picture above  our range will be B3 B  So we will count how many values are there in range B3 B by COUNTA B3 B  first  In the left side  it will produce 8 since there are 8 values while it will produce 9 in the right side  We also know that the last value is in the 1st column of the range B3 B so the col parameter of INDEX must be 1 and the row parameter should be COUNTA B3 B   PS  please upvote  bloodymurderlive s answer since he wrote it first  I m just explaining it here

User · Answer

This works for me  Get last value of the column A in Google sheet     index A A max row A A   A A lt  gt          It also skips blank rows in between if any

User · Answer

for a row    ARRAYFORMULA INDIRECT  A  amp MAX IF A A lt  gt     ROW A A          for a column    ARRAYFORMULA INDIRECT ADDRESS 1  MAX IF 1 1 lt  gt     COLUMN 1 1       4

User · Answer

My favorite is    INDEX A2 A COUNTA A2 A  1    So  for the OP s need    DAYS360 A2 INDEX A2 A COUNTA A2 A  1

User · Answer

This seems like the simplest solution that I ve found to retrieve the last value in an ever-expanding column    INDEX A A COUNTA A A  1

User · Answer

There may be a more eloquent way  but this is the way I came up with   The function to find the last populated cell in a column is    INDEX  FILTER  A A   NOT  ISBLANK  A A         ROWS  FILTER  A A   NOT  ISBLANK  A A             So if you combine it with your current function it would look like this    DAYS360 A2 INDEX  FILTER  A A   NOT  ISBLANK  A A         ROWS  FILTER  A A   NOT  ISBLANK  A A

User · Answer

I went a different route  Since I know I ll be adding something into a row column one by one  I find out the last row by first counting the fields that have data  I ll demonstrate this with a column    COUNT A5 A34    So  let s say that returned 21  A5 is 4 rows down  so I need to get the 21st position from the 4th row down  I can do this using inderect  like so    INDIRECT  A  amp COUNT A5 A34  4    It s finding the amount of rows with data  and returning me a number I m using as an index modifier

User · Answer

The way an amateur does it is   CONCATENATE  A  COUNTUNIQUE A1 A9999     where A1 is the first cell in the column  and A9999 is farther down that column than I ever expect to have any entries   This resultant A  can be used with the INDIRECT function as needed

User · Answer

Here s another one    indirect  A  amp max arrayformula if A A lt  gt    row A A           With the final equation being this    DAYS360 A2 indirect  A  amp max arrayformula if A A lt  gt    row A A            The other equations on here work  but I like this one because it makes getting the row number easy  which I find I need to do more often  Just the row number would be like this    max arrayformula if A A lt  gt    row A A          I originally tried to find just this to solve a spreadsheet issue  but couldn t find anything useful that just gave the row number of the last entry  so hopefully this is helpful for someone   Also  this has the added advantage that it works for any type of data in any order  and you can have blank rows in between rows with content  and it doesn t count cells with formulas that evaluate to     It can also handle repeated values  All in all it s very similar to the equation that uses max  G G lt      row G G   on here  but makes pulling out the row number a little easier if that s what you re after   Alternatively  if you want to put a script on your sheet you can make it easy on yourself if you plan on doing this a lot  Here s that scirpt   function lastRow sheet column      var ss   SpreadsheetApp getActiveSpreadsheet      if  column    null        if  sheet    null           var sheet   ss getSheetByName sheet         else         var sheet   ss getActiveSheet              return sheet getLastRow        else       var sheet   ss getSheetByName sheet       var lastRow   sheet getLastRow        var array   sheet getRange column   1         column   lastRow  getValues        for  i 0 i lt array length i            if  array i                          var final   i   1                    if  final    null          return final        else         return 0                Here you can just type in the following if you want the last row on the same of the sheet that you re currently editing    LASTROW     or if you want the last row of a particular column from that sheet  or of a particular column from another sheet you can do the following    LASTROW  Sheet1   A     And for the last row of a particular sheet in general    LASTROW  Sheet1     Then to get the actual data you can either use indirect    INDIRECT  A  amp LASTROW      or you can modify the above script at the last two return lines  the last two since you would have to put both the sheet and the column to get the actual value from an actual column   and replace the variable with the following   return sheet getRange column   final  getValue      and  return sheet getRange column   lastRow  getValue      One benefit of this script is that you can choose if you want to include equations that evaluate to     If no arguments are added equations evaluating to    will be counted  but if you specify a sheet and column they will now be counted  Also  there s a lot of flexibility if you re willing to use variations of the script   Probably overkill  but all possible

User · Answer

If the column expanded only by contiguously added dates  as in my case - I used just MAX function to get last date   The final formula will be    DAYS360 A2  MAX A2 A

User · Answer

To find the last non-empty cell you can use INDEX and MATCH functions like this    DAYS360 A2  INDEX A A  MATCH 99 99 A A  1      I think this is a little bit faster and easier

User · Answer

Calculate the difference between latest date in column A with the date in cell A2    MAX A2 A -A2

User · Answer

If A2 A contains dates contiguously then INDEX A2 A COUNT A2 A   will return the last date  The final formula is   DAYS360 A2 INDEX A2 A COUNT A2 A

[google-sheets] Get the last non-empty cell in a column in Google Sheets

Examples related to google-sheets

Examples related to worksheet-function

Examples related to google-sheets-formula