Formula for Filtering Multiple Cells

[arceaf]

Hi,

Hoping someone could help me with this problem below. I have recreated an excel sheet below. Column 1 is the color, Column 2 is the response (Yes/No), and column 3 is blank.

Is there a formula I can write in column 3 that will help me identify if, for a specific color, only "No" was selected on column B. For example - Blue is listed as "Yes" "Yes" "No" - so it would fail.

Red is listed as "No" and "No" - so, it would pass

Thanks!!!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1 [/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]No[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]No[/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]

 

[jondavis1987]

=IF(B1="Yes","Pass", "Fail")

or

=IF(AND(A1="Blue", B1="Yes"),"Pass","Fail")

The first formula will provide a pass/fail outcome based off of Yes or no in column B. The second formula will only produce a Pass if the value in column A is Blue and column b is Yes

 

[jondavis1987]

=IF(AND(A1="Blue", B1="Yes"),"Pass",IF(AND(A1="Red",B1="Yes"),"Pass","Fail"))

This is if you wanted to look for more than just one specific color. This formula will provide a Pass result if the value in A1 is blue or red and B1 is Yes.

 

[jondavis1987]

And I just realized I misread.

=IF(AND(B1="Yes",B2="Yes",B3="Yes"),"Pass","Fail")

If it's always in the same cell ranges this would do it

 

[Fluff]

How about
=IF(COUNTIF($A$2:$A$6,A2)=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2),"Pass","Fail")

 

[arceaf]

How about
=IF(COUNTIF($A$2:$A$6,A2)=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2),"Pass","Fail")

This was close but it did not work. I has instances where all cells were "Yes" for a color and it returned a pass. The ideas is that, since all cells are "Yes", it would return a "fail"...only if all calles are "No" would I want a pass.

Any changes you could make?

 

[Fluff]

In that case try
=IF(COUNTIF($A$2:$A$8,A2)=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"No"),"Pass","Fail")

 

[arceaf]

That worked! Thanks superstar, I'll mark this as closed

 

[Fluff]

You're welcome & thanks for the feedback