Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal for text fields
I have a one page report that shows, by department, the status of each
section within that department, for Budget, schedule, customer satisfaction etc. in a Red/Yellow/Green style. At present all we do is have a table at the top that displays % of Red, Amber and Green overall. =COUNTIF(A$8:A$37,"Red")/SUM(COUNTIF(A$8:A$37,"Green")+COUNTIF(A$8:A $37,"Yellow")+COUNTIF(A$8:A$37,"Red")) =COUNTIF(B$8:B$37,"Red")/SUM(COUNTIF(B$8:B$37,"Green")+COUNTIF(B$8:B $37,"Yellow")+COUNTIF(B$8:B$37,"Red")) =COUNTIF(C$8:C$37,"Red")/SUM(COUNTIF(C$8:C$37,"Green")+COUNTIF(C$8:C $37,"Yellow")+COUNTIF(C$8:C$37,"Red")) The above are repeated for Yellow and Green What I am trying to achieve is that, when I filter on a specific department, I get the % Red, Yellow and Green (totaling 100%) for that department alone. I've looked at Subtotal(3,A$8:A$37) and that shows me the number of records in a department, but i'm uncertain how to then get the totals I need, and maintain the ability to get the overall totals when I "Select All", and turn the filter off. Regards Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PIVOT TABLE - SUBTOTAL FOR CALCULATED FIELDS | Excel Discussion (Misc queries) | |||
Find Text the Apply a SubTotal Next to Text Address | Excel Programming | |||
Adding text to the subtotal row | Excel Worksheet Functions | |||
keystrokes for highlighting fields in subtotal | Excel Discussion (Misc queries) | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions |