Formula to calculate percentage

Copper Contributor


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







9 Replies

@Emmarky That would be


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



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


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.


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


Try this then: