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.Īlso, 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 "". Just the row number would be like this: =max(arrayformula(if(A:A"",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. With the final equation being this: =DAYS360(A2,indirect("A"&max(arrayformula(if(A:A"",row(A:A),""))))) PS: please upvote answer since he wrote it first, I'm just explaining it here.
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). In the left side, it will produce 8 since there are 8 values while it will produce 9 in the right side. So we will count how many values are there in range B3:B by COUNTA(B3:B) first. INDEX(A1:C5,1) = A1,B1,C1 # Whole row since the column is not specifiedįor the picture above, our range will be B3:B. If the column is omitted then the whole row is returned. INDEX(range, row, col): Returns the content of a cell, specified by row and column offset. So to get last non-empty value in a range, we will use 2 functions:ĬOUNTA(range): Returns number of values in a range, we can use this to get the count of rows. 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 Use just the column name to denote last non-empty row of that column. Although the question is already answered, there is an eloquent way to do it.
0 kommentar(er)
