Replace COUNTIF formula so an autofilter can be used to subtotal results

[TreetopRobin]

I'm embarassed to say I found the perfect solution on Chandoo yesterday, but didn't save it. Argh.


A B C

1

2

3 Team Company Win/Loss

4 East ABC W

5 West DEF L

6 South HIJ

7 East XYZ L

8 South LMN W

9 West ZYX L

10 North QRS W


I need a formula in C1 that returns the number of times L appears in C4:C10

With an AutoFilter on Row 3 the value should change if I filter on a company or team

ex: if I filter on the West Team C1=2; no filter C1=3;filter on East team C1=1

 

[Hui]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C4:C10,ROW(C4:C10)-MIN(ROW(C4:C10)),,1)), --(C4:C10="L"))