IF / COUNTIF statement to provide an overall outcome based on multiple options-CodePudding

I have hit a bit of a wall and it is probably an easy fix for someone. I am trying to place an overall grade on excel based on previous entries. Range is E73:E88 Possible outcomes on those ranges are PASS / N/A / DEVELOPING / 0.

I am trying to figure out a code to show: if all entries are 0 or N/A then the overall outcome is NOT GRADED. if the entries are all PASS or a mix of PASS or N/A then the overall outcome is PASS if any of the entries have DEVELOPING then the overall outcome is DEVELOPING.

I have been using IF and COUNTIF formula but I cant seem to find the correct outcome so I am unsure if I am using the correct method.

=IF(COUNTIF(E73:E88,"DEVELOPING"),"DEVELOPING",IF(COUNTIF(E73:E88,"PASS"),"PASS",IF(COUNTIF(E73:E88,"0"),"NOT GRADED","PASS")))

Any help will be greatly appreciated.

Thanks

CodePudding user response：

I think you want:

=IF(COUNTIF(A1:A10,"developing"),"developing",IF(COUNTIF(A1:A10,"Pass") COUNTIF(A1:A10,"N/A")=10,"Pass","Not Graded"))

Note I changed the test range here to A1:A10 and the second test is looking at the size of the range (in this case sum of "Pass" & "N/A" values must be 10. In your instance the size of the range needs to be equal to whatever range you are checking over. This can be made dynamic but I didn't do that here

CodePudding user response：

You should use ifna function:

=ifna(if(COUNTIF(E73:E88,"DEVELOPING"),"DEVELOPING",if(COUNTIF(E73:E88,"PASS"),"PASS", "not-graduated")),"not-graduated" )