IF formula can't figure out, please help

[Peter]

I am trying to figure out a formula to search selected cells(6 different
ones) and search if the word "Pass" is in any of the cells. If it is then I
want the cell with the formula to indicate "Pass" if true. If False then
"Fail".

At his stage I have the following for one cell, and it works

=IF('F88 LF2'!G4="Pass","Pass", "Fail")
------------------------------------------------------------
But this is what I had for more than one cell with no luck

=IF('F88 LF2'!G3,'F88 LF2'!J3,'F88 LF2'!M3,'F88 LF2'!P3,'F88
LF2'!S3="Pass","Pass", "Fail")

Can anyone give me guidance so I can figure this out?

Thanks

 

[Jacob Skaria]

Try
=IF(SUMPRODUCT((MOD(COLUMN(G3:S3),3)=1)*(G3:S3="Pass")),"Pass","Fail")

If this post helps click Yes

 

[Jacob Skaria]

Try
=IF(SUMPRODUCT((MOD(COLUMN('F88 LF2'!G3:S3),3)=1)*('F88
LF2'!G3:S3="Pass")),"Pass","Fail")

If this post helps click Yes

 

[Peter]

Thanks Jacob,

It worked on that sheet. I want to do it on other sheets as well. But the
sheets are slightly different but same endstate. What I can't get my head
around is where the ,3)=1)* comes into the formula. What I have at the moment
is..

=IF(SUMPRODUCT((MOD(COLUMN('F89 LF1'!E3:X3),3)=1)*('F89
LF1'!E3:X3="Pass")),"Pass", "0")

I got this from what u sent for my other question but isn't working at the
moment.

Please help again.

 

[Jacob Skaria]

Peter, just an explanasion would help

COLUMN('F88 LF2'!G3:S3) will return the column number of each cell

MOD(COLUMN('F88 LF2'!G3:S3),3) will divide the column number of each cell by
3 and return the reminder..

MOD(COLUMN('F88 LF2'!G3:S3),3)=1 is a condition. In your previous case the
columns are 7,10,13,16 etc; which all satisfy the condition mod(column(),3)=1
or the reminder is always 1 for these columns

So this will return the cells satisfying the conditions. Added to that we
have included the next condition = "Pass"..

So the below formula returns the number of cells which satisfy both these
conditions.
SUMPRODUCT((MOD(COLUMN('F88 LF2'!G3:S3),3)=1)*('F88
LF2'!G3:S3="Pass"))

For a different range /columns you will have to apply a different logic...

If this post helps click Yes