Formula to count text string in column cells of filtered rows

[JSS]

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[T. Valko]

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"))

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx