Calcuating a sum where 'yes' and 'no' values invert

[The Grim Discovery]

Hello folks

This is crazing me.

As you'll see the formula below works out the percentage of counts of 'yes' against a total of counts of 'yes' and 'no' in the range of D13:D16. So far so ok.

=SUM(COUNTIF(D13:D16,"Yes")/(COUNTIF(D13:D16,"Yes")+COUNTIF(D13:D16,"No")))

[So if D13:D15 ="yes" and D16='"no" I'd get a result of 75%.]

But I want to extend the range to D13:D17 but where in D17 a 'No' effectively counts as a 'Yes' and vice versa. Each time I try and bolt this into the formula it all goes awry and doesn't return a correct % result. If anyone can a) make sense of this query and b) give me a hand, it would be appreciated.
Thanks

 

[AlanY]

how about

=SUM(COUNTIF(D13:D16,"Yes")+COUNTIF(D17,"No"))/(COUNTIF(D13:D17,"Yes")+COUNTIF(D13:D17,"No"))

 

[Dim Me as xlNoob]

I was also wondering why not use COUNTA as the denominator?

=SUM((COUNTIF(D13:D16,"Yes")+COUNTIF(D17,"No"))/COUNTA(D13:D17))

 

[AlanY]

it might have some rather than yes and no, eg. don't know

 

[The Grim Discovery]

Alan and Dim - cheers for your interest and help.
Alan, that cracked it for me - many thanks.
(And yes there are other values such as 'don't know', 'valid reason' and 'not applicable' as well as 'yes' and 'no'.)

 

[AlanY]

you're welcome