# Formula to calculate percentage

Copper Contributor

# Formula to calculate percentage

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.

9 Replies

# Re: Formula to calculate percentage

@Emmarky That would be

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

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

# Re: Formula to calculate percentage

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)

# Re: Formula to calculate percentage

@Emmarky See attached. Is that what you want?

# Re: Formula to calculate percentage

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%").

# Re: Formula to calculate percentage

That is exactly what @Riny_van_Eekelen's formula does!

# Re: Formula to calculate percentage

@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.

# Re: Formula to calculate percentage

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

# Re: Formula to calculate percentage

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

# Re: Formula to calculate percentage

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})