# Counting Visible Cells

#### Paul Riley

##### New Member
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

### 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

#### Boller

##### Banned
Yes, have a look at SUBTOTAL in the Help file.

#### DominicB

##### Well-known Member
Good afternoon Paul Riley

Use the subtotal parameter of 2 :

=SUBTOTAL(2,A1:A100)

HTH

DominicB

#### Paul Riley

##### New Member
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
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

Replies
3
Views
221
Replies
5
Views
165
Replies
1
Views
39
Replies
1
Views
80
Replies
6
Views
92