IF function to give multiple results for TRUE

[moe10134]

I'm not sure how to explain this so I will do my best.

Question:Can you apply AND & OR statements to an IF statement?

I am looking for a formula that will give me multiple answers. I am looking for the result of a SUM. IF the sum is less than 9, enter SUM. If the SUM = 10, replace that with a 1. If the total is 11 or 22, give me 11 or 22. I need a find a formula that allows me a SUM of:

1: SUM of cells if less than 9 = SUM.
2: IF SUM = 10, result = 1 - possible REPLACE?
3: IF SUM = 11, result =11
4: IF SUM = 22, result = 22

I have tried numerous, IF statements with AND & OR and REPLACE & TRUE, FALSE statements, I just need to have a result of "SUM","1", "11",or "22' in an absolute cell. Could I use a possible HLOOKUP/VLOOKUP/INDEX formula for results? I have named cells as absolute and tried to figure it out that way and I still can't get the final result of :"SUM = (1-9)"; "1" if 10; and "11" or "22". Possibly looking for a multiple TRUE result.


i hope this makes sense. Please let me know if you need more clarification.

Thanks much!

 

[mrshl9898]

Sounds like you want an imbedded IF, like this:

=IF(SUM=9,"SUM",IF(SUM=10,1,IF(SUM=11,11,IF(SUM=22,22,"SUM NOT FOUND"))))


Test, if true then result, if false then next test etc.

 

[moe10134]

Not quite, it's not a text formula. It's more like a a SUM function with an IF statement, maybe? Something like (IF(A3=>9,[sum,A1+A2],if(A3=10,1,if(A3=11,11,if(A3=22,22) then FALSE would be SUM(A1+A2). I'm not sure when to do the math. It would require an array formula. My result should be a number.

With four possible results, SUM(A1+A2) result in A3 then/also {=SUM(if(A3=10,1,if(A3=11,11,if(A3=22,22,if(A3=>9),false would be,SUM(A1+A2)))))}.

So my results would be "5" if less than 9, 10="1", 11="11", 22="22".

It could also be broken up in 2 cells, =SUM(A1+A2=5) in A3. Then an IF: IF(A3=>9,A3,if(A3=10,1,if(A3=11,11, if(A3=22,22). So my result will be either number, 5, 1, 11, or 22. I might need more than any if statement. It might be an INDEX or something.

Hope that makes better sense that I am looking for is: {result of the sum. If the sum is =>9,enter the sum, if the sum equals 10=1,11=11,22=22)}

 

[barry houdini]

You could try this formula

=IF(A1+A2<10,5,IF(A1+A2=10,1,A1+A2))

In that version if A1+A2 < 10 then you get the result 5, if the sum is = 10 you get the result 1, otherwise you will get the actual sum, which fulfils your 11 and 22 conditions

 

[jtakw]

Hi,

Not sure if I understand your question correctly, does the following help?

Excel 2010

	A	B	C

#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col target="_blank" usertag.php?do='list&action=hash&hash=DAE7F5"' forum="" www.mrexcel.com="">#DAE7F5 " /><colgroup><col><col><col></colgroup><thead>
#DAE7F5 ;text-align: center;color: #161120">
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]220[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]220[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"]220[/TD]

</tbody>

#DAE7F5 ;color: #161120">Sheet1​

[TABLE="width: 85%"]
#FFFFFF " ><tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
#FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead>[TR]
#DAE7F5 ;color: #161120">[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10"]#DAE7F5 ;color: #161120">C3[/TH]
[TD="align: left"]=IF(A3<9,5,IF(A3=10,1,IF(OR(A3={11,12}),A3,SUM(A$1,A$2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



C3 formula copied down.

 

[moe10134]

I work better with Named Ranges or Named Cells than $Absolute$References. I named a few cells and came up with this IF statement.

($A$3) BN_SUM = A1+A2 = 5, ($A$4) named BN_TOTAL is cell $A$4 = cell reference of $A$3 . Then in $A$5 a backwards IF statement to reference BN_TOTAL is TRUE=5,1,11,22, if FALSE = BN_SUM ($A$3)


=IF(BN_TOTAL=10,1,IF(BN_TOTAL=11,11,IF(BN_TOTAL=22,22,BN_SUM)))

I don't know how to explain it!! But, I got a result of 5,1,11,22 in cell $A$5. Hope you all understand. Thanks for the great inspiration!