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

With from to dates in C2 and D2
=SUMPRODUCT((A1:A10=C2)*(A1:A10<=D2)*(B1:B10="Pas s"))

If this post helps click Yes
---------------
Jacob Skaria


"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

Try this...

Assume the full unfiltered range is B2:B15.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B15,ROW(B2:B15)-ROW(B2),0,1)),--(B2:B15="pass"))

--
Biff
Microsoft Excel MVP


"JSS" wrote in message
...
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

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