COUNTIF on an autofilter?

[Guest]

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!

 

[Biff]

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

 

[Guest]

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!

 

[Guest]

If you use advanced filter you can use formulas in your criteria.

See this for more info: http://www.contextures.com/xladvfilter02.html

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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-

 

[Guest]

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.

 

[Guest]

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.