If any cell in a range of cells A4:AF4 contain the number 2, pass or fail
if a cell contains a 2 want result to show value of fail

Assuming you mean, you have a row, A4:AF4 (32 cells). If any of the cells
have a 2 in it, then FAIL, otherwise pass?

=IF(COUNTIF(A4:AF4,2)0,"FAIL","PASS")

If this isn't what you mean, perhaps rephrase the question.

--
John C


"Nadine" wrote:

If any cell in a range of cells A4:AF4 contain the number 2, pass or fail
if a cell contains a 2 want result to show value of fail

Try this:

=IF(ISNA(MATCH(2,A4:AF4,0)),"fail","pass")

Yes, Thanks.

Now how do I change it, to show nothing until a cell has been filled in.
Keep cell that contains forumla empty, until it meets the criteria

"John C" wrote:

Assuming you mean, you have a row, A4:AF4 (32 cells). If any of the cells
have a 2 in it, then FAIL, otherwise pass?

=IF(COUNTIF(A4:AF4,2)0,"FAIL","PASS")

If this isn't what you mean, perhaps rephrase the question.

--
John C


"Nadine" wrote:

If any cell in a range of cells A4:AF4 contain the number 2, pass or fail
if a cell contains a 2 want result to show value of fail

No they have changed the criteria.

If specific cells containt a 2

K4,L4,P4,Q4,T4,U4,X4


"CurlerBob" wrote:

Try this:

=IF(ISNA(MATCH(2,A4:AF4,0)),"fail","pass")

For than one way to skin this cat...but I would use the countif formula...

=IF(COUNTIF(A4:AF4,2)0,"FAIL","PASS")


"Nadine" wrote:

If any cell in a range of cells A4:AF4 contain the number 2, pass or fail
if a cell contains a 2 want result to show value of fail

IF(OR(K4=2,L4=2,P4=2,Q4=2,T4=2,U4=2,X4=2), "FAIL","PASS")

you could also define a range on those columns and make a better formula.....



"Nadine" wrote:

No they have changed the criteria.

If specific cells containt a 2

K4,L4,P4,Q4,T4,U4,X4


"CurlerBob" wrote:

Try this:

=IF(ISNA(MATCH(2,A4:AF4,0)),"fail","pass")

When you say define a range, do you mean Name a range

"desertres" wrote:

IF(OR(K4=2,L4=2,P4=2,Q4=2,T4=2,U4=2,X4=2), "FAIL","PASS")

you could also define a range on those columns and make a better formula.....



"Nadine" wrote:

No they have changed the criteria.

If specific cells containt a 2

K4,L4,P4,Q4,T4,U4,X4


"CurlerBob" wrote:

Try this:

=IF(ISNA(MATCH(2,A4:AF4,0)),"fail","pass")

=IF(COUNTBLANK(A4:AF4)=32,"",IF(OR(K4=2,L4=2,P4=2, Q4=2,T4=2,U4=2,X4=2),
"FAIL","PASS"))
--
John C


"Nadine" wrote:

Yes, Thanks.

Now how do I change it, to show nothing until a cell has been filled in.
Keep cell that contains forumla empty, until it meets the criteria

"John C" wrote:

Assuming you mean, you have a row, A4:AF4 (32 cells). If any of the cells
have a 2 in it, then FAIL, otherwise pass?

=IF(COUNTIF(A4:AF4,2)0,"FAIL","PASS")

If this isn't what you mean, perhaps rephrase the question.

--
John C


"Nadine" wrote:

If any cell in a range of cells A4:AF4 contain the number 2, pass or fail
if a cell contains a 2 want result to show value of fail

Thanks John C.

I got this working, had to rework it a bit.

=IF(COUNTBLANK(H4:AF4)=25,"",IF(OR(K4=2,L4=2,P4=2, Q4=2,T4=2,U4=2,X4=2),"Yes","No"))


"John C" wrote:

=IF(COUNTBLANK(A4:AF4)=32,"",IF(OR(K4=2,L4=2,P4=2, Q4=2,T4=2,U4=2,X4=2),
"FAIL","PASS"))
--
John C


"Nadine" wrote:

Yes, Thanks.

Now how do I change it, to show nothing until a cell has been filled in.
Keep cell that contains forumla empty, until it meets the criteria

"John C" wrote:

Assuming you mean, you have a row, A4:AF4 (32 cells). If any of the cells
have a 2 in it, then FAIL, otherwise pass?

=IF(COUNTIF(A4:AF4,2)0,"FAIL","PASS")

If this isn't what you mean, perhaps rephrase the question.

--
John C


"Nadine" wrote:

If any cell in a range of cells A4:AF4 contain the number 2, pass or fail
if a cell contains a 2 want result to show value of fail