IF is one of the most popular Excel functions and very useful on its own. Combined with the logical functions such as AND, OR, and NOT, the IF function has even more value because it allows you to test multiple conditions in desired combinations. Continue reading
Comments on: Excel IF OR function with formula examples
by Svetlana Cheusheva, updated on
Comments page 4. Total comments: 222
I am trying to say that if One Cell = this amount add / subtract a Certain amount.
Can this be done??
EX: =IF(D6/7=E6,G6) OR (D6/7=E6,H6) OR (D6/7=E6,I6) OR (D6/7=E6,J6) OR (D6/7=E6,K6)
Thx...Dawn
Hi!
Sorry, I cannot understand your formula
=IF(AND(A2="VISHAL", B2="HP", C2=610), "6", "10"), IF(AND(A2="VISHAL", B2="HP", C2=2310), "15", "20")
WILL THIS WORK?????????
I NEED TO ENTER MULTIPLE RESULT IN A SINGLE CELL, FROM DIFFERENT CONDITIONS.
Hello!
Your conditions contradict each other. For example, if A2 = ”VISHAL”, B2 = ”HP”, C2 = 900 then the first condition will return 10, and the second - 20.
This isn't working. What am I doing incorrectly?
=IF((OR(E2=Daily, E2=Weekly)), Next Shift, ENTER DATE)
Column E indicates if a project is due daily or weekly. Column F would ideally calculate today+1 for daily or today+8 days for weekly.
Hi,
You must enclose text values in quotation marks, such as "Weekly".
What is "Next Shift, ENTER DATE"?
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=IF(E2="Daily",TODAY()+1,IF(E2="Weekly",TODAY()+8,""))
Hope this is what you need.
I got it! thank you. :)
Hi.. need help.
i have date today and start date, to calculate the case age but another column is the status of the case, close or open.. so the logic will be.. calculate the case age if the case is still open..
thank you in advance.
Hello!
In the condition of the IF function, write down the check that the case is open. If the condition is met, calculate the age using the DATEDIF function.
Hope you’ll find this information helpful.
I got this formula: =IF(OR(C2="Closed","--"),(SUM(A2-B2)))
but..
it's working but the other way around. it calculates the age if the case is marked as "Closed".
Hi, I would like to know a formula to show if something if greater than or less than a number to show a figure for example
11 years service - if the years service is more than 10 to show 2, if it is less than 10 but more than 5 to show 1 and if it is less than 5 to show 0.
hope this makes sense.
TIA
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
=IF(A1>10,2,IF(A1>5,1,0))
there are some proble with me in excell example
=if(a1<10,100,"enough",if(d1=0,"niu"))
Please I need your help how can I come up with the formula for this
45000 =0%
5000=15%
Next 2950000=30%
Excess 3000000=35%
Hello!
A similar question has already been asked many times on our blog. I think this answer will be helpful.
I need some help in constructing the formula to this:
I need to derive a result(column title) if ALW(column title) is 1.56 and up its Oversize, if ALW is 1.20-1.55 its Goodsize, if ALW is 1.10-1.19 its Undersize, if ALW is 1.0-1.09 its Offsize, and if ALW is below 1.0 its Runts
Hello!
I’m sorry but your task is not entirely clear to me.
What is the column title? In Excel and other spreadsheet applications, the column header is the colored row of letters used to identify each columnwithin the sheet, or workbook. Column title is a letter.
If your question is about an Excel cell -
=IF(A1>=1.56,"Oversize", IF(A1>=1.2,"Goodsize", IF(A1>=1.1,"Undersize", IF(A1>=1,"Offsize", "Runts" ))))
i need a formula like ( date of joinin - current date less than 365 days then the answer should be 0
Hello!
How to use Excel IF function with dates read in this article.
Hi,
I am trying to do the following if statements with the last if statement to add on an additional 1 week if P13 = "U" but I can't get this to work. Any help would be welcomed.
=IF(AND(O131,O133,O135),4,IF(AND(P13="U",2),TRUE)))))
Hello Joanne!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
Hi,
I am trying to do the following if statements with the last if statement to add on an additional 1 week if P13 = "U" but I can't get this to work. Any help would be welcomed.
=IF(AND(O131,O133,O135),4,IF(AND(P13="U",2),TRUE))))).
Thanks so much.
IF(A1="DELIVERY",THEN C1(CELL NO)*.020%,IF NO C1*.004% I NEED CORRECT FORMULA
How do I write the formula for... If either Cell A1 or Cell D1 contains a term, say "ENGLISH", then the consequent grade of ENGLISH from the C1 or F1 should be filled in cell G1.
Is it possible?
Hello Michael!
If I got you right, the formula below will help you with your task:
=IF(A1="ENGLISH",C1, IF(D1="ENGLISH",F1,""))
I hope it’ll be helpful.
=IF(E45="PA1",0.85,IF(E45="PA2",0.95,IF(E45="CB1",0.99,"")))
I NEED A FORMULA FOR CELL F45
IF CELL E45 = "PA1" THEN CELL F45= .85 AND IF CELL E45 = PA2 THEN CELL F45 READS .95 AND IF CELL = E45 - CB1 THEN F45 = .99
THANKS
Trying to write a formula that picks out the word grapefruit from D14 or the word recorder from D14 and gives a zero.. if those words aren't found then F14-E14. The formula works for just Grapefruit but when I add in the Or and Recorder it doesn't. What am I writing wrong? It's telling me to many arguments.
=If(Or(Is number(Search("Grapefruit"'D14,(Is number(Search("Recorder",D14),0,F14-E14))
Can I not make cell to cell comparison with if/or? Here is the formula I am using.
=IF(OR(D3<E3,D3=E3,"Good", "Review"))
Here is the formula I used after reviewing the responses to other questions on this forum. New formula works.
=IF(D3<=E3, "Good", "Review")
Hi Sam,
Your original formula would work as well. You just had to move the other bracket to close off the or( function.
=IF(OR(D3<E3,D3=E3),"Good", "Review")
Can you help me on the error in this formula. =IF(ISNUMBER($AH15),ANDIF($AH15>150,(" High Random Blood Sugar "&$AH15&" Mg.%. ", "")&" "&IF($AH15>150,"Urine Sugar "&$AI15&". ", ""),("")
AH15 is Number or Text "ND" i.e. Not Done.
Thank you.
=IF(OR(ISNUMBER($AH15),$AH15="ND"),IF($AH15>150,"High Random Blood Sugar"&$AH15&"Mg.%.","")&" "&IF($AH15>150,"Urine Sugar"&" "&$AI15&".",""),"")
The above formula seems to work for me.
I have a price range for warranty coverage. I need to see when sales either sold the item over or under the range for a warranty package. For example:
Min Product $ Range Max Product $ Range Product $ Sold
1000 1499.99 269.00
300 599.99 1049.00
1000 1499.99 578.00
600 799.99 1456.00
I need a formula that tells me if the product sold for $269.00 was "oversold" or "undersold" contract range? I tried =if(or(c1=B2,"oversold"))
It doesn't work. What am I doing wrong?
A = Min / B = Max / C = Sold
=IF(C1B1,"OVERSOLD","")
Something is wrong with the formula not being posted properly.
=IF(C1B1,"OVERSOLD","")
It should be:
=IF(C1 less than A1,"UNDERSOLD",IF(C1 greater than B1,"OVERSOLD","")
Hello,
I want to write a formula to write C1 as:
1 if A1>10 or B1>20
2 if 7<A1<10 or 10<B1<20
3 if 4<A1<7 or 5<B1<10
4 if 1<A1<4 or 1<B1<5
5 if A1<1 or B1<1
Thanks in advance
I have student totals,I want to apply comments, 400 and above should have good performance, 300-400 should have fair performance, below 300 should have poor performance,the cell for total is I
I need a formula in google spreadsheet that will:
+1 when the value is >=5,
+2 when the value is >=10,
+3 when the value is >=15,
+4 when the value is >=20,
+5 when the value is >=25
The formula I am currently using is:
=IF(F7>=5,H7+1,IF(F7>=10,H7+2,IF(F7>=15,H7+3,IF(F7>=20,H7+4,H7))))
This formula is working for the +1 when the value is >=5, but when the value is >=10, it is still adding +1.
Please Help!
Hello Tina,
You see, your first condition fits to all other conditions as well - the value is greater than 5. You need to limit each condition and check, for example, if the number is not only greater than or equal to 5 but also less than 10.
Your formula for spreadsheets should look like this:
=IF(AND(F7>=5,F7<10), H7+1, IF(AND(F7>=10,F7<15), H7+2, IF(AND(F7>=15,F7<20), H7+3, IF(F7>=20,H7+4, H7))))
You will find the info about the IF function in Google Sheets in this post.
I have 2 columns, work email(D2) & personal email(E2). I am trying to create a formula in a new field (preferred email) that says if D2 is blank use E2 (if there is a value) or if E2 is blank use D2 or leave blank. Is this possible?
Hi Sue,
If you love compact formulas, use this one :)
=IF(D2<>"", D2, IF(E2<>"", E2, ""))
That's awesome.
Hi,
If I get it right, your task is as follows: if a cell in Column D contains an email address, a formula is to bring it; if not, it should bring an email address from a cell in Column E; if both cells are empty, the formula has to bring nothing. I hope you do not mind lengthy formulas:
=IFS(OR(AND(N(ISBLANK(D2))=0, N(ISBLANK(E2))=0), AND(N(ISBLANK(D2))=0, N(ISBLANK(E2))=1)), D2, AND(N(ISBLANK(D2))=1, N(ISBLANK(E2))=0), E2, AND(N(ISBLANK(D2))=1, N(ISBLANK(E2))=1), "")
Someone please help me, i cant get this to work
In column C I enter one of 7 names.
Depending on the name I want different results in column N
So
If the name is
1 - Andy Black the result should be 400
2 - Mr Jet, Nina Sven or Mike Young the result should be 600
3 - Dr Joe, Miss Adams or Neil Foe the result should be 800
4 - Ms Hard the result should be 1000
5 - Mr Woo the result should be 1200
Which formula do I use to solve this?
Hi Björne,
The following formula suggests itself:
=IFS(C2="Andy Black", 400, OR(C2="Mr Jet", C2="Nina Sven", C2="Mike Young"), 600, OR(C2="Dr Joe", C2="Miss Adams", C2="Neil Foe"), 800, C2="Ms Hard", 1000, C2="Mr Woo", 1200)
Formula 2. If a cell is this OR that, then calculate
=IF(OR(D3>0,D390,D3180,D3270,D30,D390,D3180,D3270,D3<360),W3] are not coming
- need to know what to change to make it work .. than you
I am looking for the correct formula to use to return the greatest of two values. For example, if Q3 (5.89) is greater than R3 (7.452), I want S3 to show R3 value (7.452). If Q17 (28.86) is greater than R17 (3.105), I want S17 to show Q17 value (28.86).
Hi Jwalker,
I hope that your task may be expressed in the following way: if the value in Cell R3 is less than the value in Cell Q3, the value from Cell Q3 is needed; if the value in Cell R3 is more than the value in Cell Q3, the value from Cell R3 is needed. If so, here is the formula you could apply:
=IF(Q3 < R3, R3, Q3)
I need to validate customer order to ensure it is not less than minimum order value(MOV) and not less than minimum order qty. We validated order value on one column, filter out the order lines with order value lower than MOV and then validated order qty on another column. Is there shortcut to have all the validation performed under single column with a sophisticated nested if function?
hi everyone,
how can i formulate this one?
if >=6 : full assistance
if =4 or 5 : half assistance
if <= 3 : no assistance
Hi Farzaneh,
I hope the following formula will do the job:
=IFS(D2<=3, "no assistance", OR(D2=4,D2=5), "half assistance", D2>=6, "full assistance")
So how would I do this? If cell A1 is equal to 10, I want to multiply B1 by ten, but if A1 is equal to 25 I want to multiply B1 by four, but if A1 is equal to 50 I want to multiply B1 by 2.
Thanks in advance!
Hi Marty,
I think that both the IF function and the IFS function may help you with your task. Please choose whatever you like:
=IF(A1=10, B1*10, IF(A1=25, B1*4, IF(A1=50 ,B1*2)))
=IFS(A1=10, B1*10, A1=25, B1*4, A1=50, B1*2)
If you feel like exploring the subject, you may find it helpful to read the following articles:
'Excel Nested IF statement: examples, best practices and alternatives', 'Excel nested IF statement - multiple conditions in a single formula', and 'Use the new Excel IFS function instead of nested IF'.
Hi all
I want to differentiate the cell values into the crores, Lakh, Thousand, Hundred, Tens, Units
Example : -
123456789
12 Crores 24 Lakh 56 Thousand 7 Hundred 89
So how will i do can anyone here who can help me
Hi, Hoping someone can help.
I'm trying to write a formula using the IF, AND, or OR function but can't get the formula correct. It should be simple, really.
The conditions are;
If the SUM of Cells E4:G4 = between 10 and 15, then Cell G14 = 25
If the SUM of Cells E4:G4 is greater than or equal to 15, then Cell G14 = 50
Then there's one other result that I'm trying to achieve (in a separate cell but a similar formula)
If the SUM of Cells E4:P4 = between 400 and 600, then Cell P15 = 10
If the SUM of Cells E4:P4 is greater than or equal to 600, then Cell P15 = 20
I can't work out what I'm doing wrong, I wont paste what formulas i currently have to avoid causing a case of mass confusion.
THANKS IN ADVANCE
=IF(AND(SUM(E4:G4)>10,SUM(E4:G4)15,50,""))
=IF(AND(SUM(E4:P4)>400,SUM(E4:P4)600,25,""))
Completely butchered the original answer. This one should work.
=IF(AND(SUM(E4:G4>10),SUM(E4:G4=15,50,"")))
=IF(AND(SUM(E4:P4>400),SUM(E4:P4=600,20,"")))
I am trying to evaluate if the first date is a weekend or the time is after 5pm.
Each works on its own but is not working when combines with the OR
I am getting #NAME?
=IF(OR(WEEKEND(E2,2)>5,K19>TIME(17,0,0)),"OT", "REG")
Any Ideas
Apparently I just needed to retype and press the keyboard harder:) it worked the 50th time I typed it I don't know why as it looks exactly the same. But for now I will move on.
If F34 value = "Dealer", then used values Column K OR
If F34 value = "Trade", then use values Column M OR
If F34 value = "End User", then use values Column
If I want to reference three cells, what's the formula? (i.e =IF(F113-"x",(J126)),=IF(G113-"x",(K126)),=IF(H113-"x",(K126))
H126 want to be the value of one of three cells depending the selection of another value in three cells
Thanks in advance
Hi everyone
I need little help to construct formula from below pseudo code.
If{
customer = private AND account_status = active AND account_open_date >23-June-2006
THAN
If {
risk = high
THAN
Last Review date = 1st review date + 6 Months
}
Else If{
risk = medium
THAN
Last Review date = 1st review date + 12 Months
}
Else If{
risk = low
THAN
Last Review date = 1st review date + 12 Months
}
Else {
last review date = 24-June-2006
}
}
If {
customer = govt AND account_open_date 23-June-2006
THAN
If {
risk = high
THAN
Last Review date = 1st review date + 6 Months
}
Else If {
risk = medium
THAN
Last Review date = 1st review date + 12 Months
}
Else If{
risk = low
THAN
Last Review date = 1st review date + 12 Months
}
}
I need the response in column D , labeled "link", to substitute the number of the column with the actual entry in that column of the row. The desired results, column E, are in the "want" column. Any thoughts?
1 2 3 LINK WANT
J18.9 A41.9 1 J18.9
R41.82 E86.0 E43 2 E86.0
G20 R26.89 G30.9 1,3 G20, G30.9
Hello,
Thanks for your teach, but i think that is better to use brackets, especially if B2 can have several values
=IF(OR(B2={"delivered";"paid"});"Closed"; "")
instead
=IF(OR(B2="delivered", B2="paid"), "Closed", "Open")
Regards
Hi there!
Hope someone could help me. Trying to figure out my formula. I need to combine if, and & or using text logic. It’s quite simple but my formula can’t capture the correct results. Apparently I use the If and AND and it captures but my ‘’Not Applicable’’ logic won’t work. 😩
Here’s my formula for now
If(AND(s2=“Cleared”, t2=“Requested”, t2=“Not Applicable”), “Completed”, “Incomplete”)
FYI I used more than 10 columns. What I’m trying to capture is that some of my values in column has a “NOT APPLICABLE” value.
Thank you in advance.
Hi! Cell T2 cannot have 2 values at the same time.
=IF(OR(B2={"delivered";"paid"}),"Closed","") correct
Hi Victor,
I agree, this makes the formula more compact. Thanks for the tip!
Hi. Can you help me. i have the following. Column Q is a sum of hours for operations. Column Y is my set hour reset. Y2 Starts at 120 hours and ends at Y23 at 2640 hours. increments are in 120 hours.
In Columm Q i have the following formula =Sum(K3+Q2). on R3 i want to add a formula to do the following.
If cell Q3 is 120240 then subtract Q3-$Y$2, or if Q3 is 241360 then Subtract Q3-$Y$3, or if Q3 is 361480 then subtract Q3-$Y$4, if false then add K3+Q2
Whats the best way for me to write it.
i manage to write a formula but it turns the cell in Column R when ever the statement is true "true".
Very good article, thanks for sharing, Keep up the good work!
Hi there
please assist
if A>=2(Fail),if b>=3(Fail),but if A:B>=3(Fail)
How do i get this into one fomula
So how would I do this? If cell A1 is equal to 10, I want to multiply B1 by ten, but if A1 is equal to 25 I want to multiply B1 by four, but if A1 is equal to 50 I want to multiply B1 by 2.
Thanks in advance!
Ooops, sorry, didn't mean to reply to your question with my question.