Excel Formula for Returning Value Pass Only if All Other 6 Columns have the Value Pass with Blanks Included

[Zedrick13]

Hi guys,

Sorry to be a bother. I hope you can help. I have 7 columns, I want to apply a formula in the first column to return the value "Pass" ONLY if the next 6 columns are either all "Pass" or blank. If there is any one column that has the value "Fail", I want the first column to return the value "Fail".

I will really appreciate it if you can help. Thanks a lot!

Zed

 

[jtakw]

Hi,

What if the next 6 Columns contains a Mix of "Pass" and Blanks, no "Fail", is that a "Pass" ?

 

[jtakw]

Assuming so, and also assumes the Only values possible in the 6 Columns are "Pass", "Fail", or Blank, try this:

Book1
	A	B	C	D	E	F	G
1	Fail		Fail	Pass		Pass
2	Pass
3	Pass	Pass	Pass	Pass	Pass	Pass	Pass
4	Pass	Pass					Pass
5	Fail			Fail
Sheet581

Cell Formulas
Range	Formula
A1	=IF(COUNTIF(B1:G1,"Fail"),"Fail","Pass")

 

[Zedrick13]

If there is at least 1 Fail, its a Fail. To Pass, values for the six columns must only be Pass or Blank.

 

[Zedrick13]

Thanks, it worked!

Assuming so, and also assumes the Only values possible in the 6 Columns are "Pass", "Fail", or Blank, try this:

	A	B	C	D	E	F	G
1	Fail		Fail	Pass		Pass
2	Pass
3	Pass	Pass	Pass	Pass	Pass	Pass	Pass
4	Pass	Pass					Pass
5	Fail			Fail

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet581​

Worksheet Formulas Cell Formula A1 =IF(COUNTIF(B1:G1,"Fail"),"Fail","Pass") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Zedrick13]

Can we add one more condition? If columns B-G are all Blank, it should be "Unexecuted".

To summarize:
1. If columns B-G have at least 1 Fail, value for column 1 must be "Fail".
2. If columns B-G are a mix of Pass and blanks only, it's a "Pass".
3. If columns B-G are all blank, it's "Unexecuted".

Thank you so much. I hope you can help me one more time.

By the way, the 7 columns are not really next to each other, it's more like this:

A (B) C (D) E (F) G (H) I (J) K (L) M

The letters above with open and close parentheses have names of persons.

 

[Zedrick13]

This formula doesn't seem to work:

=IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)="","Unexecuted",IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)="Fail","Fail",IF(CHOOSE({1;2;3;4;5;6},Q33,S33,U33,W33,Y33,AA33)=OR("Pass",""))))

Does any have any idea how to fix it? The intention is:
1. If columns C;E;G;I;K;M have at least 1 Fail, value for column A must be "Fail".
2. If columns C;E;G;I;K;M are a mix of Pass and blanks only, it's a "Pass" for column A.
3. If columns C;E;G;I;K;M are all blank, column A returns value "Unexecuted".

I used the CHOOSE function to create an array for the non-adjacent cells.

 

[sandy666]

is that what you want?

Result	ColB	ColC	ColD	ColE	ColF	ColG
Unexecuted
Pass		Pass		Pass
Pass	Pass
Fail		Fail			Pass
Fail	Fail
Unexecuted
Fail	Pass	Pass	Pass	Fail		Pass