autofilter and subtotaling

[mark]

Hi.

A spreadhheet has lots of columns, two of which are

Type Priority

3S 1
4P 2
4P 3
3S 1
4C 3
4D 1

There are potentially thousands of rows... the Type can be
one of a bunch of things, but the priority will always be
1, 2, or 3.

I had been using the COUNTIF function to determine the
distribution in the Priority column.

However, it is now needed to be able to use Auto-Filter to
do things such as select only the 4P rows in the Type.

What I need to do is to determine the count of the 1, 2, 3
after the auto-filter is applied. The COUNTIF function
seems to still count the entire range, even if sume of the
rows are now hidden due to the filtering.

The Subtotal function can be used to subtotal only rows
left visible after the filtering, as such =subtotal
(9,range) , but the way I see to do that requires three
more columns... columns to determine whether not the
original row had a 1, 2, or 3, in it, and then apply the
subtotal to those columns.

When it was done with COUNTIF, it was only one cell per
each count. Is there a simple way to keep the count
(after filtering) in a single cell for each case.. 1, 2,
or 3?

 

[Debra Dalgleish]

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to count cells that contain a 2 in column D, after a filter
on another column, you could use the following, where there are no blank
cells in column A:

=SUMPRODUCT((D2200=2)*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

 

[Peter Atherton]

Mark

If you just want the count of all the 4Ps you do not have
to filter. Say column a is named Type and B is named
Priority try

=COUNT(IF(Type=D2,Priority)) entered as an array function
(Ctrl + Shift + Enter)

Regards
Peter

 

[mark]

Thank you, Debra.

I will take a look at it.

I suspected that it could be done with a fairly advanced
formula.

Mark

 

[mark]

Thanks.

It is the user's wish to be able to use the auto filter.