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

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi,

What if the next 6 Columns contains a Mix of "Pass" and Blanks, no "Fail", is that a "Pass" ?
 
Upvote 0
Assuming so, and also assumes the Only values possible in the 6 Columns are "Pass", "Fail", or Blank, try this:


Book1
ABCDEFG
1FailFailPassPass
2Pass
3PassPassPassPassPassPassPass
4PassPassPass
5FailFail
Sheet581
Cell Formulas
RangeFormula
A1=IF(COUNTIF(B1:G1,"Fail"),"Fail","Pass")
 
Last edited:
Upvote 0
If there is at least 1 Fail, its a Fail. To Pass, values for the six columns must only be Pass or Blank.
 
Upvote 0
Thanks, it worked!

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

ABCDEFG
FailFailPassPass
Pass
PassPassPassPassPassPassPass
PassPassPass
FailFail

<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]
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top