Forum Discussion

Emmarky's avatar
Emmarky
Copper Contributor
Sep 03, 2023

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....
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 03, 2023

Emmarky That would be

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

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

 

  • Emmarky's avatar
    Emmarky
    Copper Contributor
    Sep 03, 2023
    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's avatar
        Emmarky
        Copper Contributor
        Sep 03, 2023
        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%").

Resources