IF function to give multiple results for TRUE

moe10134

Board Regular
Joined
Sep 8, 2005
Messages
157
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
Joined
Feb 6, 2012
Messages
1,794
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
Joined
Sep 8, 2005
Messages
157
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 8, 2005
Messages
157
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!
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top