Subtotal for text fields

[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