# Subtotal for text fields

F

#### Fred

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