COUNTIF(AE66:AE1015,"Yes")" for visible cells only

[tonywatsonhelp]

Hi Everyone,
I cant believe this is so difficult I just want to count the number of "Yes" in a range but only whats not been filtered out

so i need "=COUNTIF(AE66:AE1015,"Yes")" for visible cells only

any idea how i can do this?

Thanks

Tony

 

[jasonb75]

The only functions that work with filtered rows are SUBTOTAL and AGGREGATE.

=SUMPRODUCT(SUBTOTAL(3,OFFSET( AE66 ,ROW( AE66:AE1015 )-ROW( AE66 ),0))*( AE66:AE1015 ="Yes"))

 

[tonywatsonhelp]

Thank you,
Worked perfectly