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

Resources