Nested IF statement with multiple criteria AND a range

[CarrieAWalton]

Hi all,

Really hoping you can help with this one.

I have a range of cells (J2:AH2) which all have one of 4 options in: Yes, No, Partially, or N/A.

I want cell I2 to generate a 'score' based on the contents of the cells in the range. So:

If any cell in the range = Yes, count 1, if Partially, count 0.5, if No, count 0, and if N/A don't count it at all (ignore completely).

I then want the resulting score output as a percentage. So if 20 of the 25 cells have either yes, no, or partially in then the total would be divided by 20. If all 25 cells have Y. N. P then the total will be divided by 25.

I hope that makes sense.

I've fumbled about with the IF & COUNTIF functions but it doesn't seem to be working for a range and for multiple criteria together.

I bet it's actually really simple

 

[CarrieAWalton]

Never mind, I've figured it out:

[B]​[/B]=SUM(COUNTIF(J3:AH3,{"Partially","Yes","No"})*{0.5,1,0})/COUNTIF(J3:AH3,"<>N/A")