I am trying to count the non-blank cells in an unknown range. The start of
the range is determined by the position on the sheet of a date but is offset
by 1 row and 1 column and and the range should continue on through the end of
the sheet. Basically, it has a format like this:
A B
Date
Data
Data
Data

The problem is, it could contain 1 entry or 100 entries beyond the date, but
the location of the "Date" on the sheet varies depending on the data
predceding this part of the sheet. I've tried doing a count and counta with
an offset, indirect of an address ref, array, non-array. I could have had
incorrect syntax for the formulas but it wasnt the syntax that was the
problem, I just couldnt get it to create and then count a range where I
picked the starting cell. This is the current incarnation of my count:
COUNTA(INDIRECT(ADDRESS(Cell Ref with starting row, 2, 3, , "Name of
Sheet"), TRUE):$B$5000)
This give me a 1 but only because the INDIRECT() evaluates out to a #VALUE.
I would really appreciate any guidance or insite from anyone.

If starting cell is E13, for example:
=COUNTA(OFFSET(E13,1,1,65536-ROW(E13),1))

"atryon" wrote:

I am trying to count the non-blank cells in an unknown range. The start of
the range is determined by the position on the sheet of a date but is offset
by 1 row and 1 column and and the range should continue on through the end of
the sheet. Basically, it has a format like this:
A B
Date
Data
Data
Data

The problem is, it could contain 1 entry or 100 entries beyond the date, but
the location of the "Date" on the sheet varies depending on the data
predceding this part of the sheet. I've tried doing a count and counta with
an offset, indirect of an address ref, array, non-array. I could have had
incorrect syntax for the formulas but it wasnt the syntax that was the
problem, I just couldnt get it to create and then count a range where I
picked the starting cell. This is the current incarnation of my count:
COUNTA(INDIRECT(ADDRESS(Cell Ref with starting row, 2, 3, , "Name of
Sheet"), TRUE):$B$5000)
This give me a 1 but only because the INDIRECT() evaluates out to a #VALUE.
I would really appreciate any guidance or insite from anyone.