Help with formula !!

[excel02021]

I need help with the formula: I tried everything including Countif - countif and counta, But this is what I want.

I have 5 columns = a,b,c,d,e
each column has yes, no and n/a as value (column d to e)
in columng a I need to find % of yes value.
Easy part is if column b, c, d, e has just yes or no as value.
So for example: B=yes, c=yes, d=yes,e=yes than a = 100%
another example: B=yes, c=yes, d=yes, e=no than a = 75%
so it is easy to write formula if it just yes or no, but there is another value n/a.
If the value is n/a than that column should not count.

so if B=yes, c=yes,d=yes,e=no than formula is countif(b1:e1,"yes")/counta(b1:e1,"no") which is 75% 0r it divides by 4 column.

But if the value is B=yes, c=yes, d=yes, e=n/a than it should divide by 4 column it only should divide by 4 column but only 3 column because any column with value n/a does not count.
so in this case if B=yes, c=yes, d=yes, e=n/a , value of a (answer) should be still 100% because column with n/a does not count and rest of them are yes.
but instead of n/a if it is no, than value will be 75%.

last one more example if B=yes, c=no, d=n/a, e=n/a than value of a = 50% because it only divides by 2 column (other two column with n/a value does not count.

Hope I was able to explain the problem.

after every try i cannot come with one formula which can resolve that issue.

 

[Joe4]

Welcome to the Board!

How about (for "yes"):

=countif(b1:e1,"yes")/(countif(b1:e1,"yes") + countif(b1:e1,"no"))

To check for other values (i.e. "no"), just change the value highlighted in red to the value you are looking for.

 

[excel02021]

thank you so much !! not sure, this is exactly what I was doing but opposite (I was adding and dividing later) not sure, it was BODMAS which was messing this up.
this formula works fine, so I did not understand what do you mean by changing the value in red

 

[Joe4]

I did not understand what do you mean by changing the value in red

I just mean if you wanted to find the percentage of "no", you would just change that red "yes" to "no".

 

[excel02021]

got it thanks !!