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
83
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Assuming so, and also assumes the Only values possible in the 6 Columns are "Pass", "Fail", or Blank, try this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Fail</td><td style=";"></td><td style=";">Fail</td><td style=";">Pass</td><td style="text-align: right;;"></td><td style=";">Pass</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Pass</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Pass</td><td style=";">Pass</td><td style=";">Pass</td><td style=";">Pass</td><td style=";">Pass</td><td style=";">Pass</td><td style=";">Pass</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Pass</td><td style=";">Pass</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Pass</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Fail</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Fail</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet581</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">B1:G1,"Fail"</font>),"Fail","Pass"</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
83
If there is at least 1 Fail, its a Fail. To Pass, values for the six columns must only be Pass or Blank.
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
83

ADVERTISEMENT

Thanks, it worked!

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

ABCDEFG
1FailFailPassPass
2Pass
3PassPassPassPassPassPassPass
4PassPassPass
5FailFail

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

Worksheet Formulas
CellFormula
A1=IF(COUNTIF(B1:G1,"Fail"),"Fail","Pass")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Zedrick13

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

Zedrick13

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,800
is that what you want?

ResultColBColCColDColEColFColG
Unexecuted
PassPassPass
PassPass
FailFailPass
FailFail
Unexecuted
FailPassPassPassFailPass
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,950
Members
409,848
Latest member
Blomsten
Top