Count Visible Rows with formula

[adamsm]

Hi anyone,

The following formula counts the visible rows.

=SUBTOTAL(2,A6:A3006)

How could I make the formula to count the records as;

Lets say for example 3 records out of 300 records found.

Any help on this would be kindly appreciated.

 

[jasonb75]

Maybe

=SUBTOTAL(102,A6:A3006)&" out of "&COUNT(A6:A3006)&" Records Found"

 

[Jonmo1]

Try

=SUBTOTAL(2,A6:A3006) & " out of " & COUNT(A6:A3006) & " records found"

 

[adamsm]

Thanks for the help. If the formula is to be used in another sheet except the active sheet; How should I change the formula with the sheet's name?

The name of the sheet that is to be included in the formula is (MySheet).

 

[Jonmo1]

Just add the sheetname to the ranges

=SUBTOTAL(2,'Sheet Name'!A6:A3006) & " out of " & COUNT('Sheet Name'!A6:A3006) & " records found"

 

[adamsm]

Thanks for the help and I do really appreciate it. By the way if the sheet is not filtered the formula shows 0 records out of 0 records. Is there a way to make this not visible. and only visible when the sheet gets filtered?