Formula to calculate percentage

Copper Contributor

Hi,

I have two columns one is Question and the other is Response.I want to calculate the percentage of some questions whose response is "yes", out of the total responses of yes and no excluding n/a.please let 

me know how to write the formula below. the questions for which we need to calculate percenatge are A1,A3,B1,B3,C1,C2,C4,D2,D4.

thanks in advance

 

 

 image.png

 

 

 

9 Replies

@Emmarky That would be

=COUNTIF(B2:B17,"yes")/COUNTIF(B2:B17,"<>n/a")

provided the data is in A1:B17 as shown below.

Riny_van_Eekelen_1-1693740743082.png

 

Hi @Riny_van_Eekelen, thanks for your response. But I don't want all the cells in the range B2:B17,
I am looking only for specific question responses A1,A3,B1,B3,C1,C2,C4,D2,D4.(i.e B2,B4,B6,B8)

@Emmarky See attached. Is that what you want?

Sorry nope, that is a different req, this is diff one. Both are calculated on different sheets.i am looking for an optimized formula similar to this one
IFERROR(SUM(COUNTIF(B2,"Yes"),COUNTIF(B4,"Yes"),COUNTIF(B8,"Yes"),COUNTIF(B10,"Yes"),COUNTIF(B11,"Yes"),COUNTIF(B13,"Yes"),COUNTIF(B15,"Yes"),COUNTIF(B17,"Yes")/SUM(COUNTIF(B2,"Yes"),COUNTIF(B2,"No"),COUNTIF(B4,"Yes"),COUNTIF(B4,"No"),COUNTIF(B8,"Yes"),COUNTIF(B8,"No"),COUNTIF(B10,"Yes"),COUNTIF(B10,"No"),COUNTIF(B11,"Yes"),COUNTIF(B11,"No"),COUNTIF(B13,"Yes"),COUNTIF(B13,"No"),COUNTIF(B15,"Yes"),COUNTIF(B15,"No"),COUNTIF(B17,"Yes"),COUNTIF(B17,"No")),"0%").

@Emmarky 

That is exactly what @Riny_van_Eekelen's formula does!

@Hans Vogelaar are you mentioning this one ??
=COUNTIF(B2:B17, "yes")/COUNTIF(B2:B17,"<>n/a"), but I don't require all the cells in the range B2:B17.

@Emmarky 

Take a look at the workbook that he attached in his latest reply. It contains a different formula!

OK in that sheet another column was added, I can't add in my sheet because it goes to another teams as the responses change dynamically

@Emmarky 

Try this then:

 

=SUMPRODUCT((B2:B17="yes")*{1;0;1;0;1;0;1;0;1;1;0;1;0;1;0;1})/SUMPRODUCT((B2:B17<>"n/a")*{1;0;1;0;1;0;1;0;1;1;0;1;0;1;0;1})