Using the COUNTIF Function with SUBTOTAL to calculate the % of 'Yes' answers

[Chippyiow]

Currently I have a column of data with Yes, No, N/A (I have converted these to blank cells so they are ignored) as the possible answers. I have been able to calculate the % of 'Yes' answers using the Formula:

=COUNTIF(A2:A103,"Yes")/COUNTA(A2:A103)

I would like the % of 'Yes' responses to update as I filter other columns within the table that I am working on. I understand that the SUBTOTAL function is a method of doing this.

Is there a way to use the SUBTOTAL function can be used with the COUNTIF function to calculate the number of 'Yes' answers within the filtered range of cells?

Any help would be greatly appreciated!

 

[XOR LX]

Hi,

=SUMPRODUCT(0+(A2:A103="Yes"),SUBTOTAL(3,OFFSET(A2,ROW(A2:A103)-MIN(ROW(A2:A103)),)))/SUBTOTAL(3,A2:A103)

Regards

 

[Chippyiow]

That's exactly what I needed.

Thank you very much!

 

[XOR LX]

You're welcome!

Cheers