IF, OR, AND

[bob]

If F2 = "m" or "m3" or "a" or "a3" or "ftm," and J2 is not blank, I want to
return a value of 1.

Can anyone kindly help?

Thanks,
Bob

 

[Barb Reinhardt]

One way.

=IF(AND(LOOKUP(F2,{"a","a3","ftm,","m","m3"},{1,1,1,1,1})=1,ISBLANK(J2)=FALSE),1,"")

 

[bob]

Barb,

Excellent! Thank you very much.

Bob

One way.

=IF(AND(LOOKUP(F2,{"a","a3","ftm,","m","m3"},{1,1,1,1,1})=1,ISBLANK(J2)=FALSE),1,"")
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[joeu2004]

If F2 = "m" or "m3" or "a" or "a3" or "ftm,"
and J2 is not blank, I want to return a value of 1.

First, when you say "J2 is blank" (the opposite of "not blank"), do
you mean that it __appears__ blank, or do you mean that it really has
no formula? In other words, is the formula =if(true,"") blank or not
blank in your mind?

This is important because ISBLANK() is true only when J2 has no
formula or value, whereas J2="" is true whenever J2 appears blank.
(Well, technically, that should be TRIM(J2)="". But that is usually
overkill.)

Assuming you want the latter, one way to do what you ask without any
function overhead is:

=(J2<>"") * ((F2="m")+(F2="m3")+(F2="a")+(F2="a3")+(F2="ftm"))

But your subject line suggests that you are interested in an IF/AND/OR
solution per se, perhaps because you are trying to understand that
combination. In that case:

=if(and(J2<>"", or(F2="m",F2="m3",F2="a",F2="a3",F2="ftm")),1,0)

Or more simply:

=if(and(J2<>"", or(F2={"m","m3","a","a3","ftm"})),1,0)

Or more simply:

=--and(J2<>"", or(F2={"m","m3","a","a3","ftm"}))

Note: Replace J2<>"" with ISBLANK(J2)=FALSE if you truly want to know
if the cell is empty (i.e. no value or formula).

 

[ShaneDevenshire]

Hi,

Suppose you enter your list in the range A1:A5, then the formula is

=SUMPRODUCT((F2=$A$1:$A$5)*(J2<>""))

This allows for easy modifications.