Which Formula to use in Excel 2013 for Calculating yes/no into %

[Droiyan3]

Dear MRExcel Forum!
Thanks for having me here! i am an Technical Support Engineer and to be honest i find using excel in my day to day job as super valuable - its the best

I have constructed a questionnaire( 17 questions ) for my peers to answer for which the response can be either Yes,NO, or NA in ( C13:C34) . I used a data validation to allow them select only one of those 3 choices .


For column C35 i would like it to the following logic: Sum up all the Yes and substract all the NO's and then show me in percentage the result. If there is N/A then dont use it for calculation.

I am a super beginner in formulas and was thinking of using something like sumif but i am not sure even after watching the training how to make it use that.

I would be grateful if anyone would have some tips ?

thanks a lot
Droiyan3

 

[Droiyan3]

so an example would be : if out of 17 questions , 1 is NA, 14 are yes and 2 are no then we should find out how much is 14 questions in % out of 16 ? in this case it would be 87.5

thank you

 

[AhoyNC]

Try:

=COUNTIF(C13:C34,"Yes")/(COUNTIF(C13:C34,"Yes")+COUNTIF(C13:C34,"No"))

 

[mikerickson]

I interpret the OP request "Sum up all the Yes and substract all the NO's and then show me in percentage the result."

to be (number of Yes - number of No) / (total Yes + total No)

the formula =(COUNTIF(C13:C34, "Yes")-COUNTIF(C13:C34, "No")) / (COUNTIF(C13:C34, "Yes")+COUNTIF(C13:C34, "No"))

will do that

 

[AhoyNC]

I'm not sure Mike. OP states what you say in Post 1, but in Post 2 OP states answer should be 87.5%???
Anyway, one of our formulas should work for him.

 

[Droiyan3]

Try:

=COUNTIF(C13:C34,"Yes")/(COUNTIF(C13:C34,"Yes")+COUNTIF(C13:C34,"No"))

apologies for the confusion - your formula was spot on and it drastically reduced my headache ! thank you so much!! both of you for the valuable input!




I have a secondary questionnaire that i need to create also an countif formula and would be very great-full if you could assist me on that as well.

The questionnaire has a range is from 10 q =C39:C48 for which i have I have 6 responses which are ( Totally Satisfied, Very Satisfied, Somewhat Satisfied, Neither Satisfied nor Dissatisfied ,Somewhat dissatisfied,Very Dissatisfied ,Totally Dissatisfied .)

Totally Satisfied & Very Satisfied are counted as correct good( yes) response while everything else is counted bad ( no)

I need the formula to sum up all the Totally Satisfied & Very Satisfied and then substract the rest of the responses and show me the value in percentage result. I am assuming that for this we would need to use the countifs​ formula but wasnt so successful with it :D

 

[AhoyNC]

Excel Workbook
	C
39	Totally Satisfied
40	Very Satisfied
41	Totally Satisfied
42	Neither Satisfied nor Dissatisfied
43	Very Satisfied
44	Totally Satisfied
45	Very Dissatisfied
46	Totally Dissatisfied
47	Very Satisfied
48	Very Satisfied
49
50	70.00%
Sheet

 

[Droiyan3]

Thanks a lot! that helped out a lot!