Yes No formula returning % help.

[caliskier]

How would you do this. I have a column, C, that has a bunch of names in it say Apple, Orange, Bananna. I want to write a formula that tells me for all the Apples in column C what percent of "yes" answers do I have to a yes/no question in column E. I am using y and n in column E. Do I need to use Vlookups? How would I do that?

[daddylonglegs]

To get the total number of "Apple"s in C with "Y" in E

=SUMPRODUCT(--(C1:C100="Apple"),--(E1:E100="Y")

to get a percentage of "Apple" with "Y" out of the total number of "Apple"s...

=SUMPRODUCT(--(C1:C100="Apple"),--(E1:E100="Y")/COUNTIF(C1:C100,"Apple")

or an array formula to do the same

=AVERAGE(IF(C1:C100="Apple",IF(E1:E100="Y",1,0)))

confirm with CTRL+SHIFT+ENTER

in all cases format as percentage and adjust ranges as necessary

[VBA Noob]

Hi,

Does this help

=SUMPRODUCT(--(C2:C100="Apple")*(E2:E100="Yes"))/COUNTA(C2:C100)

VBA Noob

[pinmaster]

Hi,

Maybe...

=100/(SUM(IF(C1:C10="apple",1))/SUM(IF((C1:C10="apple")*(E1:E10="y"),1)))

enter as an array using....CTRL+SHIFT+ENTER


Hope this helps!
Jean-Guy

[caliskier]

thanks guys, very helpful!!