SUBTOTAL Second Count / sub-count of Filtered Visible Cells

[QTE]

Hi Excel Forum,

Is it possible to get a second count (sub-count) of Filtered Visibl
Cells summarising and distinguishing the different items (departments
listed below using SUBTOTAL,3 and COUNTIF in a Formula? Will COUNTI
work on Filtered Visible Cells?

Example:
Column P has non-numeric Filtered data:
Departments:
103/9
103/1
103/4
103/2
103/9
103/2
103/2
103/7
103/4
103/5
103/4
103/7
103/1
103/6
103/8
103/3
---------------------
16 TOTAL Count
---------------------

The ciriteria used will return a mix of departments and an individua
department may be listed more than once. The above Total Count of 16
gives a Count of all Filtered Visible Cells; i.e. departments.
However, I would also like a summary Count of the departments.

If it is possible to summarise the Count of the above Filtered Visibl
departments using either Formula Functions or VBA - please advise wit
a working example. Example Summary Count of Departments -

Summary Data:
103/1 =2
103/2 =3
103/3 =1
103/4 =3
103/5 =1
103/6 =1
103/7 =2
103/8 =1
103/9 =2

 

[Frank Kabel]

Hi
I would use a pivot table for this. Using the department as row item
and the count of department as data item. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[QTE]

Hi Frank,

Thank you for the suggestion of using a Pivot Table.

I tried it out but the results included the Filtered Hidden Rows a
well as the Filtered Visible Rows. I require only the data of th
Filtered Visible Rows. Does this mean that I first have to copy th
Filtered Visible Rows to another location? The Filtered data will b
constantly changing and a solution that can be performed on-the-fl
would be more suitable.

Is there any way of providing a solution using Formula or VBA to coun
the individual departments and then count the subset of department
within the Filtered Visible Rows to ultimately provide a summary coun
for each department?

*Hi
I would use a pivot table for this. Using the department as row item
and the count of department as data item. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

*[/QUOTE]

Kind regards,
QT

 

[QTE]

Hi Excel Forum,

Any suggestions.


Kind regards,
QT