Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
I'd like to base a COUNTIF calculation on the rows displayed after an
autofilter. I've been trying combinations of COUNTIF and SUBTOTAL without success. Basically, I set an autofilter on column-A then perform a COUNTIF on column-B to calculate occurrences of text values. Thanks for any suggestions! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
Hi!
Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B1:B10,ROW(B1:B10)-ROW(B1),0,1)),--(B1:B10="something")) Replace "something" with your criteria. Include the quotes. Biff "gordo" wrote in message ... I'd like to base a COUNTIF calculation on the rows displayed after an autofilter. I've been trying combinations of COUNTIF and SUBTOTAL without success. Basically, I set an autofilter on column-A then perform a COUNTIF on column-B to calculate occurrences of text values. Thanks for any suggestions! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
The solution that ended up working for me was provided Domenic in another post.
I add this function to several cells, each with a different value where "Pass" appears: SUMPRODUCT(SUBTOTAL(3,OFFSET(E3:E161,ROW(E3:E161)-ROW(A3),0,1)),--(E3:E161="Pass")) Basically, I have an autofilter on column-A and use several cells that perform COUNTIF on the column-E result set after the filter is applied. The countif values are then used in subsequent calculations, so I have to keep the filter and count ops seperate. These discussion groups are great! "gordo" wrote: I'd like to base a COUNTIF calculation on the rows displayed after an autofilter. I've been trying combinations of COUNTIF and SUBTOTAL without success. Basically, I set an autofilter on column-A then perform a COUNTIF on column-B to calculate occurrences of text values. Thanks for any suggestions! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
If you use advanced filter you can use formulas in your criteria.
See this for more info: http://www.contextures.com/xladvfilter02.html "gordo" wrote: I'd like to base a COUNTIF calculation on the rows displayed after an autofilter. I've been trying combinations of COUNTIF and SUBTOTAL without success. Basically, I set an autofilter on column-A then perform a COUNTIF on column-B to calculate occurrences of text values. Thanks for any suggestions! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
Thanks Dave,
An Advanced Filter does accept a formula in it's criteria but the filter operation isn't where my problem is. Autofilter satisfies my filter need. My issue is in calculating the number of occurrences in the result set. I use a couple of cells containing COUNTIF functions that search for specific results. The countif values are then used downstream in subsequent calculations unique to the particular criteria, so I need to keep the COUNTIF ops independant of the filter. Thanks for you reply and I hope the above clarifies my hurdle. "Dave F" wrote: If you use advanced filter you can use formulas in your criteria. See this for more info: http://www.contextures.com/xladvfilter02.html "gordo" wrote: I'd like to base a COUNTIF calculation on the rows displayed after an autofilter. I've been trying combinations of COUNTIF and SUBTOTAL without success. Basically, I set an autofilter on column-A then perform a COUNTIF on column-B to calculate occurrences of text values. Thanks for any suggestions! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
Use SUBTOTAL, as it ignores any hidden rows from filtering. It can do the
following... 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
Thanks Sloth,
The SUBTOTAL function will perform a count, but my problem was applying criteria to the filtered list, not just a total count. The trick here, as Biff points out, is to nest the SUBTOTAL function inside the SUMPRODUCT function. There's a few other things in there too, but that's the general approach. Thanks- "Sloth" wrote: Use SUBTOTAL, as it ignores any hidden rows from filtering. It can do the following... 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on an autofilter?
I'm sorry; I did misunderstand your question.
If you are going to use SUMPRODUCT anyways, why not use only SUMPRODUCT =SUMPRODUCT(--(1st criteria),--(2nd criteria)) where the first creteria is the one you used to filter the list, and the second is the one you need to add. OR.... use the "custom option in the autofilter to select multiple filter options (up to three). I doubt this would work though, looking at your responses. "gordo" wrote: Thanks Sloth, The SUBTOTAL function will perform a count, but my problem was applying criteria to the filtered list, not just a total count. The trick here, as Biff points out, is to nest the SUBTOTAL function inside the SUMPRODUCT function. There's a few other things in there too, but that's the general approach. Thanks- "Sloth" wrote: Use SUBTOTAL, as it ignores any hidden rows from filtering. It can do the following... 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal on Autofilter with Countif | Excel Worksheet Functions | |||
Autofilter not working correctly... | Excel Discussion (Misc queries) | |||
countif for only visible rows when combined with autofilter - possible? | Excel Discussion (Misc queries) | |||
Strange Results with Autofilter | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |