On your second formula is not alway TRUE

what if J2 =Y, K2=Y,M2=Y, and L2=N (the OP wants J2,K2,M2 not L2)


"T. Valko" wrote:

Try this:

=IF(OR(J2="N",K2="N",M2="N"),"N",IF(AND(LEFT(J2,2) ="SP",K2="Y",M2="Y"),"Y",""))

Or:

=IF(COUNTIF(J2:M2,"N"),"N",IF(AND(LEFT(J2,2)="SP", K2="Y",M2="Y"),"Y",""))

Biff

"Studebaker" wrote in message
...
I asked this a few days ago but I didn't realize I had other factors I
needed
to account for.
I need to modify the formula I am using:
=IF(OR(J2="N",K2="N",M2="N"),"N",(IF(AND(J2="SP*", K2="Y",M2="Y"),"Y","")))

I'm trying to create a function to automatically determine if an engine is
fully
assembled in column N.

If the answer is "N" (no) to either columns J, K or M then column N
must display "N" (no, the engine is not completely assembled). If the
answer
is "Y" (yes) to columns K and M and if column J has an SP # in it (i.e.
SP123-12) then column N must display "Y". If all columns J thru M are left
blank then column "N" must display nothing (blank). (By the way, I'm not
including the "L" column on purpose).
I've been getting "N"s on the rows I want to get "N"s but blanks on all
rows
I should be getting "Y"s for.

J K L M N
Belt Motor Oil Valves Engine Assembled?
1
2 N N Y N N (=IF function)
3 SP123-12 Y Y Y Y (=IF function)

Help! What am I doing wrong?

Thank you,

Studebaker