Check if Array contains any occurence of the letter "F"

[eddiegnz1]

without VBA, how do I display the word "Fail" in cell 'C2' if any cell in an array contains the letter "F"? The array is not the entirety of column 'A' rather just A1:A30.

Additional info:
the array contains only two letters, which are either the letter "P" or the letter "F". If any one (or more than one) of those cells contains the letter "F" then the result is "Fail".


Kind thanks,
Eddie

 

[CROY1985]

try this:

=IF(COUNTIF(a1:a30,"f"),"Fail","Pass")

I assumed the opposite of Fail would be Pass.

 

[dispelthemyth]

Edit:
If the cell contains just single letters try

=If(countif($A$1:$A$30, "F"), "fail",)



If they contain any other characters use

=IF(SUM(IFERROR(SEARCH("F",$A$1:$A$30,1),0))>0,"Fail","")

Press Ctrl + Shift + Enter as its an array formula

 

[Rick Rothstein]

without VBA, how do I display the word "Fail" in cell 'C2' if any cell in an array contains the letter "F"? The array is not the entirety of column 'A' rather just A1:A30.

Additional info:
the array contains only two letters, which are either the letter "P" or the letter "F". If any one (or more than one) of those cells contains the letter "F" then the result is "Fail".

Try this formula...

=IF(COUNTIF(A1:A30,"F"),"Fail","")

You did not say what you wanted to happen if there was no F, so I left it blank (the "" part of the formula).

 

[jamtay317]

I would use =IF(ISNUMBER( MATCH("F",$A$1:$A$31,0) ), "Fail", "Pass")