How to get the last non-blank row number in a column

[polarisking]

Easy to do in VBA, not so much in Excel.

Often, I put totals in Row 1 followed by a column header, and the data beginning in row 3. I'm constantly having to do something like =countif(A3:A10000,"ValueToTest") knowing in this instance there would never be anything near 10,000 rows. Seems like there should be a straightforward formula that would dynamically return the last non-blank row number. There's a formula on various Excel sites and blogs that works [=LOOKUP(2,1/(A:A<>""),ROW(A:A))], but it's just too bizarre and complex to put in front of standard Excel users.

Any ideas? I'd prefer nothing with either a hard-coded or dynamic named range. Just let the formula come back with the last row.

Thank you in advance

 

[Peter Bartholomew]

As a 365 user, I would either return the last cell as a range reference using
= XLOOKUP( TRUE, ISTEXT(columnA), columnA,,,-1)
or its row number using
= XMATCH( TRUE, ISTEXT(columnA),,-1)

I might even place the first formula in a defined name 'finalCell' so the row number would be
= ROW(finalCell)
or the remaining part of the range would be
= finalCell : @columnA

Then again, I do not need to care what a standard Excel user might make of it.

 

[polarisking]

As a 365 user, I would either return the last cell as a range reference using
= XLOOKUP( TRUE, ISTEXT(columnA), columnA,,,-1)
or its row number using
= XMATCH( TRUE, ISTEXT(columnA),,-1)

I might even place the first formula in a defined name 'finalCell' so the row number would be
= ROW(finalCell)
or the remaining part of the range would be
= finalCell : @columnA

Then again, I do not need to care what a standard Excel user might make of it.

Peter, thank you for the response. Any suggestions for those not on 365?

 

[bosco_yip]

Peter, thank you for the response. Any suggestions for those not on 365?

{=MATCH(2,1/(A:A))}

Regards

 

[polarisking]

Thank you, bosco_yip.

See the attached, please. My requirement is that the formula returning the last row be in the column in which the derivation is being done. Your formula is returning a circular reference. See column 1 - that's the overly complex one mentioned in my initial post. Does it work? Yes. Is it over-engineered? Yes.

Thanks for looking at this.

 

[bosco_yip]

Thank you, bosco_yip.

See the attached, please. My requirement is that the formula returning the last row be in the column in which the derivation is being done. Your formula is returning a circular reference. See column 1 - that's the overly complex one mentioned in my initial post. Does it work? Yes. Is it over-engineered? Yes.

Thanks for looking at this.

Then, use

{=MATCH(2,1/(C:C<>""))}

or,

=MATCH("zzz",C:C)

 

[polarisking]

You, my friend, are a rock star! Well deserved Ninja designation. The =MATCH("zzz",C:C) formula works exactly the way I requested, and it's elegant.

Thank you.

 

[pecoflyer]

Be aware this will only work with cells containing text. If the last cell is a number it will be ignored, and your result incorrect
If there are no blanks and starts on row 1 =ROW(INDEX(C:C,COUNTA(C:C))) also works
Various other possibilities to be wrapped in the ROW function