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
Fail		Fail	Pass	Pass
Pass
Pass	Pass	Pass	Pass	Pass	Pass	Pass
Pass	Pass	Pass
Fail	Fail

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet581​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=IF(COUNTIF(B1:G1,"Fail"),"Fail","Pass")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Result[/td][td=bgcolor:#5B9BD5]ColB[/td][td=bgcolor:#5B9BD5]ColC[/td][td=bgcolor:#5B9BD5]ColD[/td][td=bgcolor:#5B9BD5]ColE[/td][td=bgcolor:#5B9BD5]ColF[/td][td=bgcolor:#5B9BD5]ColG[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Unexecuted[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pass[/td][td][/td][td]Pass[/td][td][/td][td]Pass[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pass[/td][td=bgcolor:#DDEBF7]Pass[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Fail[/td][td][/td][td]Fail[/td][td][/td][td][/td][td]Pass[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Fail[/td][td=bgcolor:#DDEBF7]Fail[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Unexecuted[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Fail[/td][td=bgcolor:#DDEBF7]Pass[/td][td=bgcolor:#DDEBF7]Pass[/td][td=bgcolor:#DDEBF7]Pass[/td][td=bgcolor:#DDEBF7]Fail[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Pass[/td][/tr]
[/table]