combine if statements

[jasman]

hi. i have two if statements but i need to combine them so that if BOTH are true then enter "true" into the cell but if BOTH are false enter "false" into cell.

thanks

=IF(F7=L7,"TRUE",IF(I7=L7,"TRUE","FALSE"))

AND


=ISNUMBER(SEARCH("1",F7)) ......plus could you advise how i can ask this formula to also check cell 17 along with f7?) thanks

 

[jasonb75]

How about

=IF(OR(COUNTIF(L7,F7&"*"),COUNTIF(L7,I7&"*")),OR(ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},L7))))

 

[jasman]

that works perfect, thanks soo much!

 

[jasman]

how can i flip this formula so that if the conditions are met it returns FALSE instead of TRUE?

THANKS

 

[jasonb75]

=not(if(or(countif(l7,f7&"*"),countif(l7,i7&"*")),or(isnumber(search({0,1,2,3,4,5,6,7,8,9},l7)))))

 

[jasman]

thats great, can i add the folowing into that formula aswell?

IF(LEFT(R8,1)=L8,"TRUE", IF(LEFT(U8,1)=L8,"TRUE", "FALSE"))

 

[jasonb75]

Should that be R8 or R7? i.e. same row as the original formula?

 

[jasman]

sorry should be r7

 

[jasonb75]

In that case could you provide a clearer sample.

IF(LEFT(R8,1)=L8,"TRUE", IF(LEFT(U8,1)=L8,"TRUE", "FALSE"))

that logic will always be FALSE, L7 contains 2 characters so can never equal LEFT(R7,1)

Try posting a sample the same as you did in #9, but this time with all of the criteria, including any you haven't yet mentioned.

 

[jasman]

F I K L P R U
<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>B</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>C1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>TRUE</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>C1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=64>B1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>D</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>E</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>TRUE</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>E1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=64>E</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A
A
</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A
A2
</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A
A
</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>A
A1
</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>TRUE
FALSE
</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A
A1
</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=64>A
A2
</TD></TR></TBODY></TABLE>


These are rows 7, 8, 9 from my sheet.

Column P is where the formula should be. Basically it needs to look at column L and if the same value is NOT in the F,I,K but is in R and/or U with or without a number suffix then return true else return false.

So the rows should read in this example... TRUE, TRUE, FALSE, TRUE

my formula reads
=IF(NOT(IF(OR(COUNTIF($L7,$F7&"*"),COUNTIF($L7,$I7&"*"),COUNTIF($L7,$K7&"*")),OR(ISNUMBER(SEARCH({0,1,2,3,4,5,6},$L7))))),IF(LEFT(R7,1)=LEFT(L7,1),"TRUE","FALSE"),"FALSE")

 

[jasonb75]

Are you now saying that the entire content of L7 should be checked against F7, I7 and K7, and not just the letter as you asked originally?

If your original criteria still stands then (with the additional information you gave us), neither of your example above is correct.

Your table shows TRUE, TRUE, TRUE, FALSE, you say it should be TRUE, TRUE, FALSE, TRUE, but your criteria equate to TRUE, TRUE, FALSE, FALSE. Which is correct?