Excel formula to find last non blank cell in a column starting on a certain row

0
votes

So I've got a column of data that I want to count the non blank cells after a certain row.

Here's an example of what I have:

enter image description here

So, in this example, I would like to start counting non blank cells in column A starting on row 13 (which would be a total of 4). If you look at the formula I have entered into cell D12 I can get the value I'm looking for with this formula:

=COUNTA(A:A)-11

I could use this formula:

=COUNTA(A13:A16)

but the point is the last cell with data in it can change due to entering different amounts of data in the column.

But I'm wondering if there is a different formula I could use that would count non blank cells from a certain row down regardless of the amount of data I enter into the column from a certain row down using an open ended range, kind of like this:

=COUNTA(A13:A)

This formula doesn't work but it kind of illustrates what I tried to do that didn't work.

1
excelexcel-formulacount
=COUNTA(A13:A100000) or some large end row. Or till the last row: =COUNTA(A13:A1048576). Or =COUNTA(A:A)-COUNTA(A1:A12). – BigBen
@BigBen I thought of that as well and that would work too. I guess I'm asking if there is any way to use an open ended range to figure out the value I want, IE: a range starting at A13 that goes to the actual end of the list of data I enter into column A after cell A13? – Gharbad The Weak
You could use INDEX/MATCH but I feel like that's overcomplicating. – BigBen
Well, you could always get the last used row dynamically and incorporate that, not sure what your benefit is over using the last row: =COUNTA(A13:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))). This makes it somewhat "open" ended I guess. Unfortunately it isn't GS =) – JvdV
@JvdV that is exactly what I am looking for. If you add an answer to this question I will mark it as accepted and upvote. Thank so much! – Gharbad The Weak

1 Answers

1
votes

from my comment above:

Well, you could always get the last used row dynamically and incorporate that, not sure what your benefit is over using the last row:

=COUNTA(A13:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))))

This makes it somewhat "open" ended I guess. Unfortunately it isn't GS =)