Using the following User Defined Function will get you the result whatever
criteria autofiltering is using:
Public Function fCount(rr As Range, s As String) As Long
Application.Volatile
Dim r As Range
fCount = 0
For Each r In rr
If r.EntireRow.Hidden < True Then
If r.Value = s Then
fCount = fCount + 1
End If
End If
Next
End Function
UDFs are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:
=fCount(B1:B100,€¯Pass€¯)
To remove the UDF:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To learn more about UDFs, see:
http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
--
Gary''s Student - gsnu200906
"JSS" wrote:
What is the formula to return the number of specified matching text in a
colukn but only for the rows showing after a filter is applied?
Example: How many incidences of "Pass" are there if a filter is applied to
see only dates 1/1/2009 and 3/1/2009? I tried =COUNTIF and I get the total
of all "Pass". Not the filtered data.
Date Result
1/1/2009 Pass
2/1/2009 Fail
3/1/2009 Pass
4/1/2009 Pass