Quickly count used rows?

[Robert Crandal]

Suppose I have a 60 x 9 cell range that is initially empty.
Users will be able to fill data into each row. Is there a
quick and efficient method to count how many rows
contain data?? (A row is considered to contain data if
any one more cells of the 9 columns of that row contain
any type of data)

IMPORTANT:

I would like to be able to retrieve the count of used rows every
time data is added or deleted from the 60x9 cell range.

Also, I'm looking for a FAST way to count the number of rows that
contain data. I already know that I can scan each row one at a time
and get the count, but I'm interested in a faster technique since my
table will likely be much bigger than 60 rows. Isn't there a faster
scheme that handles the "Worksheet_Change" event to retrieve
the count of used rows in a range??

I'd appreciate any innovative ideas here. Thank you!

Robert

 

[Living the Dream]

Hi Rob

Assuming your cell Range Is A2 to I61 then

If the range is populated with Alhpa characters, then
=COUNTIF($A$2:$I$61,">""")


If the rnage is populated with Numeric, then
=COUNTIF(A2:I61, ">0")

or, if there is a mix of Alpha-Numerics
=COUNTA($A$2:$I$61,">""")

HTH
Mick.

 

[plinius]

Il 18/09/2012 10:48, Robert Crandal ha scritto:

Suppose I have a 60 x 9 cell range that is initially empty.
Users will be able to fill data into each row. Is there a
quick and efficient method to count how many rows
contain data?? (A row is considered to contain data if
any one more cells of the 9 columns of that row contain
any type of data)

IMPORTANT:

I would like to be able to retrieve the count of used rows every
time data is added or deleted from the 60x9 cell range.

Also, I'm looking for a FAST way to count the number of rows that
contain data. I already know that I can scan each row one at a time
and get the count, but I'm interested in a faster technique since my
table will likely be much bigger than 60 rows. Isn't there a faster
scheme that handles the "Worksheet_Change" event to retrieve
the count of used rows in a range??

I'd appreciate any innovative ideas here. Thank you!

Robert

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:I1,ROW(1:60)-1,))>0))

Is this enough FAST?
E.

 

[Robert Crandal]

That works for ONE row. My sheet has 60 rows and 9 columns.
Is this forumula missing something?

 

[Robert Crandal]

On second test, your formula does work. Interesting solution!

Thank you!

 

[plinius]

Il 18/09/2012 21:18, Robert Crandal ha scritto:

On second test, your formula does work. Interesting solution!

Thank you!

Glad to help. Thanks for the feedback ;-)