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!

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

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!

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!

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!

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

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

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

hi
thanks for the formula,

SUMPRODUCT(SUBTOTAL(3,OFFSET(B1:B10,ROW(B1:B10)-ROW(B1),0,1)),--(B1:B10="something"))

the above formula works.

i need a help from this formula how to use greater than plus lesser than a number.

i replaced "something" with numbers (7), but i want to get the details less than 10