Counting Visible Cells

Paul Riley

New Member
Joined
Feb 20, 2004
Messages
41
Hi,

In a large data set with auto filters I can use the function =SUBTOTAL(9,Range) to sum data only in the visble rows.

Is there a similar function to COUNT the number of visible rows when the filters are switched on & off?

Thanks,

Paul
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good afternoon Paul Riley

Use the subtotal parameter of 2 :

=SUBTOTAL(2,A1:A100)

HTH

DominicB
 

Paul Riley

New Member
Joined
Feb 20, 2004
Messages
41
HIDDENROWS

Hi,

Thanks for identifying the SUBTOTAL(2 or 102, Range) function. This works fine for counting if the range has numbers - how can I get it to work if the range has text? i.e count the number of visible rows in a text only data base?

thanks,

Paul
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi Paul Riley

In that case, I'm not sure there is one, but would be quite happy to be proved wrong...

You could try using this custom function :

Code:
Function CountMyRange(UsrRng As Range)
Dim cell As Range
Application.Volatile
CountMyRange = 0
For Each cell In UsrRng
If Not cell.EntireRow.Hidden = True Then
CountMyRange = CountMyRange + 1
End If
Next cell
End Function

Paste the code into an ordinary blank module, and then use this formula to count your filtered rows :

=CountMyRange(A1:A100)

HTH

DominicB
 

Watch MrExcel Video

Forum statistics

Threads
1,113,848
Messages
5,544,640
Members
410,627
Latest member
georgealice
Top