Share via

Pass or Fail from Multiple Cell Values in Excel (non numerical)

Anonymous
2016-07-07T09:36:02+00:00

Hi there.

I've been at this for hours (seriously) and I just cant get it right.  I have a range of data B3:B31 which needs to contain a P or F for a pass or fail.  I have conditionally formatted the cells already to show a red or green cell depending on the outcome.

Now the hard part...... im trying to insert a formula to determine whether the result of the data in B3:B31 results in a pass or fail.  Essentially 1 fail in this range results is an overall fail.  Only a 100% p score can be a pass  Can someone help me with the appropriate formula to do this?  Ive tried this =IF(B3:B31="p","Pass","Fail") and its telling me its wrong.

Thank you.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Lz365 38,191 Reputation points Volunteer Moderator
    2016-07-07T09:48:59+00:00

    Hi,

    Would the following work for you ?

    =IF(COUNTA(B3:B31)=COUNTIF(B3:B31,"P"), "Passed", "Failed")

    8 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-07-07T11:28:11+00:00

    Another way to get to work is as below

    =IF(SUBTOTAL(3,B3:B31)=COUNTIFS(B3:B31,"P"),"Pass","Fail")

    1 person found this answer helpful.
    0 comments
  2. Lz365 38,191 Reputation points Volunteer Moderator
    2016-07-07T11:33:53+00:00

    Logic is:

    1. We count the number of values (COUNTA) in B3:B31 (we might have 29, but only 5...)
    2. We count the number of values ="P" (COUNTIF) in B3:B31. It will only count the values equal to "P"

    ==> If both values equals this means there are only Ps in the range (B3:B31), if not, there's at least one non-P. This works in your case as you're supposed to only have F, P or blanks

    Hope this makes sense & helps.

    Feel free to use the Mark as Answer link on the proposed formula - this might help someone else...

    0 comments
  3. Anonymous
    2016-07-07T11:14:57+00:00

    YES!!!!  Oh my goodness that has made my day.  Is there any way you can break it down to help me understand what you did so in future I may know what im doing. 

    Thank you.

    0 comments
  4. Anonymous
    2016-07-07T09:38:22+00:00

    =IF(MAX(B3:B31)="P","Pass",IF(MAX(B3:B31)="F","Fail"))

    I also tried the above and its just telling me a constant Fail.  My knowledge of formulas is quite basic and many efforts have been a copy and paste from other queries to no avail.

    0 comments