How to specify a range from arbitrary cell and to "end of worksheet"

[derevin]

Hello,

I would like some help with efficiently counting cells in a worksheet
where neither the location of the last row or the last column are
known.

I have a worksheet where data is arranged in columns of various
lengths. Data begins in column B, and the first three rows of the
worksheet are not to be counted.

So, column B might have entries from B4 to B10 that I need to count,
while column C has entries from C4 to C25, and D has entries from D4
to D7 - very short.

Data is not all numerical.
I am guaranteed to have adjacent columns (i.e. first blank column
signals the end of data)
I am guaranteed to have adjacent data in each column (i.e. there is no
data below a blank cell)

My current formula is this:
=COUNTA(4:10000)-COUNTA(A4:A10000)

It counts the data in all rows starting with 4, and then subtracts
column A, which is not to be counted.

I would LIKE my formula to look like this:
=COUNTA(B4:last_cell)

It doesn't matter if last_cell is not exact. I know CTRL-END takes me
to the last cell of a spreadsheet (or whatever Excel thinks is the
last cell). Is there a way to reference THAT CELL that I'm taken to
when I hit CTRL-END?


Thanks,

Alex

 

[T. Valko]

Why not just use this:

=COUNTA(B4:IV10000)

That's about as efficient as you're going to get!

 

[derevin]

That's what I'm doing now... I think it's a little brittle to specify
an enclosed area like that and it doesn't look as "clean" to me as an
entire-row or entire-column reference. (i.e. A:A or 3:3)

So there is NO way to refer to the CTRL-END cell in a formula?

 

[T. Valko]

So there is NO way to refer to the CTRL-END cell in a formula?

There is but it's nowhere near as efficient as what I suggested. In fact, it
would be very inefficient. Still interested?

Finding the last used column is easy *but* finding the last used row is more
difficult since the number of rows varies by column and this is where you
lose all efficiency.

 

[derevin]

Hi Biff,

In the interest of conserving computer cycles, I will use your
solution. However, I am curious about how I could refer to the last
cell without using VBA.

Thanks for your help!

-Alex

 

[T. Valko]

You can find the last cell in the range using this array formula** :

=ADDRESS(MAX((B4:J15<>"")*ROW(B4:J15)),MAX((B4:J15<>"")*COLUMN(B4:J15)))

You'll notice that I'm testing a relatively small range. This formula
returns the cell reference as a TEXT string. By itself, it can't be used as
a reference in a formula. You'd need to do it like this array formula** :

=COUNTA(INDIRECT("B4:"&ADDRESS(MAX((B4:J15<>"")*ROW(B4:J15)),MAX((B4:J15<>"")*COLUMN(B4:J15)))))

The bigger the range you need to include the more inefficient this is.

Another way without actually finding the reference for the last cell.
Another array formula** :

=COUNTA(OFFSET(B4,,,MAX(SUBTOTAL(3,OFFSET(B4:B15,,COLUMN(B4:J15)-COLUMN(B4),))),COUNTA(B4:J4)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

You can tell just by looking at these formulas that they are nowhere near as
efficient as:

=COUNTA(B4:J15)