COUNTIF number to calculate multiple criteria

[warrior2411]

Greetings!

Currently, I have a spreadhseet where I calculate "Y", "N", and "0".

The COUNTIF formula I have calculating the "Y" and then converts them to percentages is as follows:
=COUNTIF(H$16:H$28, "Y")/(ROWS(H$16:H$28)*COLUMNS(H$16:H$28))

For example, if I have TOTAL 13 entries ("Y", "N", and "0"), then the "Y" would be 15% (2/13 = ~ 15)

I need a formula that will do the exact same (except not accounting for the "Y" criteria, but NOW calculating for BOTH "N" and "0"), then converting it to a percentage.

Using the example above, if I have TOTAL 13 entries ("Y", "N", and "0"), then the "N" and "0" would be 85% (11/13 = ~ 85)

Any thoughts?

Thanks!

[Glenn Kennedy]

It might be easier if you were to post your Excel sheet.A

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Glenn Kennedy]

No need. There are many ways to do this...

=(COUNTIF(H$16:H$28, "N")+COUNTIF(H$16:H$28, "o"))/COUNTA($H$16:$H$28) is but one.

Your Y formula can be simplified to

=COUNTIF(H$16:H$28, "y")/COUNTA($H$16:$H$28)

the *COLUMNS(H$16:H$28) doesn't seem to do anything...

[warrior2411]

Brilliant, works like a charm! Thanks!

[Tony Valko]

=(COUNTIF(H$16:H$28, "N")+COUNTIF(H$16:H$28, "o"))/COUNTA($H$16:$H$28)

That can be reduced to:

=SUM(COUNTIF(H$16:H$28,{"N","O"}))/COUNTA($H$16:$H$28)

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.