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
 

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.

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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top