Calculation of % of "yes" in a column

[russellday]

Hi I have a set of columns G7:I25 of results that contain yes,no or N/A. I would like to count the "yes" and "N/A results and then work out as a % of all results.

I have the formula below which counts yes and then works a % of yes+no but want to include also the N/A



=IFERROR(COUNTIF(G7:I25,"Yes")/(COUNTIF(G7:I25,"Yes")+COUNTIF(G7:I25,"No")),"")

Thanks in advance

 

[frank_AL]

russell,

See if this formula gives you the desired result. This formula adds the number of Yes and #N/A and then divides by Yes plus No plus #N/A

=IFERROR((COUNTIFS(G7:I25,"Yes")+COUNT(ISNA(G7:I25)))/((COUNTIF(G7:I25,"Yes")+COUNTIF(G7:I25,"No")+COUNT(ISNA(G7:I25)))),"")

 

[FormR]

Hi, here is another option, please note that this assumes the N/A's are the literal text, not #N/A formula errors.

=IFERROR(SUM(COUNTIF(G7:I25,{"Yes","N/A"}))/SUM(COUNTIF(G7:I25,{"Yes","No","N/A"})),"")

If the range can only contain "Yes", "No" or "N/A" then this can be simplified to.

=IFERROR(SUM(COUNTIF(G7:I25,{"Yes","N/A"}))/COUNTA(G7:I25),"")

 

[russellday]

russell,

See if this formula gives you the desired result. This formula adds the number of Yes and #N/A and then divides by Yes plus No plus #N/A

=IFERROR((COUNTIFS(G7:I25,"Yes")+COUNT(ISNA(G7:I25)))/((COUNTIF(G7:I25,"Yes")+COUNTIF(G7:I25,"No")+COUNT(ISNA(G7:I25)))),"")

Excellent Thanks

 

[russellday]

Excellent Thanks ​

 

[russellday]

Hi, here is another option, please note that this assumes the N/A's are the literal text, not #N/A formula errors.

=IFERROR(SUM(COUNTIF(G7:I25,{"Yes","N/A"}))/SUM(COUNTIF(G7:I25,{"Yes","No","N/A"})),"")

If the range can only contain "Yes", "No" or "N/A" then this can be simplified to.

=IFERROR(SUM(COUNTIF(G7:I25,{"Yes","N/A"}))/COUNTA(G7:I25),"")

Excellent Thanks ​