CODEWITHSUNDEEP
vbaexcelexcel-formula
cdc949494
cdc949494

Reputation: 19

Returning a value based on multiple conditions in excel

Consider the following data:

 Item | Overall | Individual | newColumn
 A    | Fail    | Pass       | blank
 A    | Fail    | Fail       | blank
 B    | Fail    | Pass       | issue
 B    | Fail    | Pass       | issue
 C    | Pass    | Pass       | blank

I have the logic built out for the first 3 columns already. There are two levels of fails in this data:

  1. overall, and
  2. individual.

If any of the individual fail, the overall fails. Sometimes the overall can fail even though all the individuals are fine. This logic is already built out.

I am trying to find a formula for the newColumn. If all the individuals are a pass for a given item (example item B), but the overall is still a fail, the cell should return the text "issue". It is ok if it returns issue twice, not sure if you can non-dupe that part. I've tried various forms of countifs/and/ors and creating columns that count distinct values but I always find a scenario where it will break the logic.

Upvotes: 1

Views: 483

Answers (2)

user2502611
user2502611

Reputation: 147

If you add a new column with the formula:

=IF(B2="Fail",IF(COUNTIFS(A:A,A2,C:C,"fail")=0,"issue",""),"")

Then this should work on the assumptions:

  • For each item if one of the overalls are false they are all false
  • The only two possible values are "Pass" and "Fail" for columns B & C

If you require the word blank instead of a blank cell then use:

=IF(B2="Fail",IF(COUNTIFS(A:A,A2,C:C,"fail")=0,"issue","blank"),"blank")

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Try this:

=IF(COUNTIFS($A$2:$A$6,A2,$C$2:$C$6,"Fail"),"blank",IF(B2="Fail","Issue","blank"))

As required

enter image description here

Upvotes: 2

Related Questions