Help with formula !!

excel02021

New Member
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.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

Welcome to the Board!

Rich (BB code):
=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

New Member
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

New Member
got it thanks !!

• Joe4

Replies
14
Views
157
Replies
11
Views
418
Replies
7
Views
176
Replies
0
Views
92
Replies
10
Views
325 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

1,152,568
Messages
5,770,900
Members
425,651
Latest member
Mark Cashin We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back