If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
SUBTOTAL Second Count / sub-count of Filtered Visible Cells
Hi Excel Forum,
Is it possible to get a second count (sub-count) of Filtered Visible Cells summarising and distinguishing the different items (departments) listed below using SUBTOTAL,3 and COUNTIF in a Formula? Will COUNTIF 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 individual 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 Visible departments using either Formula Functions or VBA - please advise with 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 ----------- 16 TOTAL Count ----------- Kind regards, QTE --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
SUBTOTAL Second Count / sub-count of Filtered Visible Cells
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 -- Regards Frank Kabel Frankfurt, Germany "QTE " schrieb im Newsbeitrag ... Hi Excel Forum, Is it possible to get a second count (sub-count) of Filtered Visible Cells summarising and distinguishing the different items (departments) listed below using SUBTOTAL,3 and COUNTIF in a Formula? Will COUNTIF 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 individual 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 Visible departments using either Formula Functions or VBA - please advise with 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 ----------- 16 TOTAL Count ----------- Kind regards, QTE --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
SUBTOTAL Second Count / sub-count of Filtered Visible Cells
Hi Frank,
Thank you for the suggestion of using a Pivot Table. I tried it out but the results included the Filtered Hidden Rows as well as the Filtered Visible Rows. I require only the data of the Filtered Visible Rows. Does this mean that I first have to copy the Filtered Visible Rows to another location? The Filtered data will be constantly changing and a solution that can be performed on-the-fly would be more suitable. Is there any way of providing a solution using Formula or VBA to count the individual departments and then count the subset of departments within the Filtered Visible Rows to ultimately provide a summary count for each department? Frank Kabel wrote: *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 -- Regards Frank Kabel Frankfurt, Germany *[/color] Kind regards, QTE --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
SUBTOTAL Second Count / sub-count of Filtered Visible Cells
Hi Excel Forum,
Any suggestions. Kind regards, QTE --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need to count cells with conditional format | Florian | General Discussion | 3 | June 17th, 2004 02:11 AM |
How do I count cells with data? | Vasant Nanavati | Worksheet Functions | 3 | April 21st, 2004 11:54 AM |
Functions applied to filtered cells | Debra Dalgleish | Worksheet Functions | 3 | October 3rd, 2003 07:14 PM |
count blank cells? | Gregg Stone | Worksheet Functions | 2 | September 22nd, 2003 07:26 PM |
COUNT visible cells? | Gregg Stone | Worksheet Functions | 3 | September 22nd, 2003 03:07 PM |