COUNTIF on an autofilter?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gordo - 17 Aug 2006 18:54 GMT

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!

Reply to this Message

Biff - 17 Aug 2006 19:18 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> Thanks for any suggestions!

Reply to this Message

gordo - 17 Aug 2006 20:36 GMT

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!

Reply to this Message

Dave F - 17 Aug 2006 20:36 GMT

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

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

Reply to this Message

gordo - 17 Aug 2006 20:36 GMT

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.

> If you use advanced filter you can use formulas in your criteria.
>
[quoted text clipped - 8 lines]
> >
> > Thanks for any suggestions!

Reply to this Message

Sloth - 17 Aug 2006 20:44 GMT

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 to this Message

gordo - 17 Aug 2006 21:30 GMT

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-

> Use SUBTOTAL, as it ignores any hidden rows from filtering. It can do the
> following...
[quoted text clipped - 9 lines]
> 10 VAR
> 11 VARP

Reply to this Message

Sloth - 18 Aug 2006 14:53 GMT

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.

> Thanks Sloth,
>
[quoted text clipped - 20 lines]
> > 10 VAR
> > 11 VARP

Reply to this Message