Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count text string in column cells of filtered rows
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count text string in column cells of filtered rows
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count text string in column cells of filtered rows
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count text string in column cells of filtered rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Advanced Filtered Rows | Excel Worksheet Functions | |||
How do I count the number of filtered rows | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions |