what is the best function for counting rows?
 

I just want a simple formula to automatically return a result for number of
rows in a sheet (minus the heading). I don't want to have to review the last
row every time it changes or might have changed.

what is the best function for counting rows?
 

Assuming your table is in Sheet1, in col A across, headers in row1, data from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for number of
rows in a sheet (minus the heading). I don't want to have to review the last
row every time it changes or might have changed.

what is the best function for counting rows?
 

Thank you Max - that's perfect. Just for interest's sake, does 'A' refer to
a whole worksheet?

"Max" wrote:

Assuming your table is in Sheet1, in col A across, headers in row1, data from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for number of
rows in a sheet (minus the heading). I don't want to have to review the last
row every time it changes or might have changed.

what is the best function for counting rows?
 

And if there are, or can be, intervening blanks...

=MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)

Rick

"Max" wrote in message
...
Assuming your table is in Sheet1, in col A across, headers in row1, data
from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for number
of
rows in a sheet (minus the heading). I don't want to have to review the
last
row every time it changes or might have changed.

what is the best function for counting rows?
 

Thank you Rick. Once a formula gets complicated I lose interest!!

"Rick Rothstein (MVP - VB)" wrote:

And if there are, or can be, intervening blanks...

=MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)

Rick

"Max" wrote in message
...
Assuming your table is in Sheet1, in col A across, headers in row1, data
from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for number
of
rows in a sheet (minus the heading). I don't want to have to review the
last
row every time it changes or might have changed.

what is the best function for counting rows?
 

Thank you Max - that's perfect.
That's good

.. Just for interest's sake, does 'A' refer to a whole worksheet?
If you mean: Sheet1!A:A in the formula,
that term refers to the entire col A in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,700, Files: 356, Subscribers: 53
xdemechanik
---

what is the best function for counting rows?
 

But if you can ever have intervening blank cells, then the non-complicated
formula won't work.

Rick


"Jo" wrote in message
...
Thank you Rick. Once a formula gets complicated I lose interest!!

"Rick Rothstein (MVP - VB)" wrote:

And if there are, or can be, intervening blanks...

=MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)

Rick

"Max" wrote in message
...
Assuming your table is in Sheet1, in col A across, headers in row1,
data
from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx
in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for
number
of
rows in a sheet (minus the heading). I don't want to have to review
the
last
row every time it changes or might have changed.

what is the best function for counting rows?
 

Thanks - I'll remember that.

"Rick Rothstein (MVP - VB)" wrote:

But if you can ever have intervening blank cells, then the non-complicated
formula won't work.

Rick


"Jo" wrote in message
...
Thank you Rick. Once a formula gets complicated I lose interest!!

"Rick Rothstein (MVP - VB)" wrote:

And if there are, or can be, intervening blanks...

=MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)

Rick

"Max" wrote in message
...
Assuming your table is in Sheet1, in col A across, headers in row1,
data
from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx
in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for
number
of
rows in a sheet (minus the heading). I don't want to have to review
the
last
row every time it changes or might have changed.

what is the best function for counting rows?
 

Once a formula gets complicated I lose interest!!

You're missing out on all the fun of Excel!

I'm just the opposite. I don't get "interested" unless the formula is
complicated!


--
Biff
Microsoft Excel MVP


"Jo" wrote in message
...
Thank you Rick. Once a formula gets complicated I lose interest!!

"Rick Rothstein (MVP - VB)" wrote:

And if there are, or can be, intervening blanks...

=MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)

Rick

"Max" wrote in message
...
Assuming your table is in Sheet1, in col A across, headers in row1,
data
from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx
in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"Jo" wrote:
I just want a simple formula to automatically return a result for
number
of
rows in a sheet (minus the heading). I don't want to have to review
the
last
row every time it changes or might have changed.

what is the best function for counting rows?
 

"T. Valko" wrote:
.. I don't get "interested" unless the formula is complicated!

Biff,
There's an OP requesting for a helper col-free solution in this thread:
http://tinyurl.com/649r8g
It might interest you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik
---

what is the best function for counting rows?
 

See my reply in that thread.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
"T. Valko" wrote:
.. I don't get "interested" unless the formula is complicated!

Biff,
There's an OP requesting for a helper col-free solution in this thread:
http://tinyurl.com/649r8g
It might interest you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik
---