# IF function to give multiple results for TRUE

#### moe10134

##### Board Regular
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!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### mrshl9898

##### Well-known Member
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.

Last edited:

#### moe10134

##### Board Regular
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)}

Last edited:

#### barry houdini

##### MrExcel MVP
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

##### Well-known Member
Hi,

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

Excel 2010
ABC
1100
2120
385
49220
5101
61111
71212
813220
933220

#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>
</tbody>
#DAE7F5 ;color: #161120">Sheet1

Worksheet Formulas
CellFormula
#DAE7F5 ;color: #161120">C3=IF(A3<9,5,IF(A3=10,1,IF(OR(A3={11,12}),A3,SUM(A\$1,A\$2))))

#FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead>
#DAE7F5 ;color: #161120">
</thead><tbody>
</tbody>

#FFFFFF " ><tbody>
</tbody>

C3 formula copied down.

Last edited:

#### moe10134

##### Board Regular
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!

Replies
1
Views
186
Replies
7
Views
171
Replies
3
Views
231
Replies
18
Views
213
Replies
3
Views
83

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back