# 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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### 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
415
Replies
18
Views
497
Replies
3
Views
134
Replies
1
Views
567
Replies
0
Views
326

1,141,011
Messages
5,703,725
Members
421,311
Latest member
tanujath

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back