For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading
Comments on: Excel IF statement with multiple conditions
by Svetlana Cheusheva, updated on
Comments page 23. Total comments: 4464
if 400 kg = 17.5
If 2000KG=22
Based on above assumption how to calculate the unit price for 375KG
what is the best formula for that ?
I would like to know how to determine the effect of pest infestation on crop using the if statement. I am having trouble writing a formula. Can someone help me please.
Hello Michael!
For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Hello and thank you ahead of time! Been working on finding a code for my spreadsheet for quite some time now, so I am very grateful for this site! Please help!
I have two sheets in a workbook. Sheet 1 contains data to be retrieved into Sheet 2.
Sheet 2!A1 is the "ID Number" to look up in Sheet 1 Column A, and Match or Lookup? if Sheet 1 Column D is "Yes", then return "Purple", if Column E is "Yes" then return "Red",if Column F is "Yes" then return "Blue",if Column G is "Yes" then return "Black"
The code I am working with and return value in:
Sheet 2 Cell D4
VLOOKUP(A1,Sheet 1!A:A,IF(Sheet 1!D:D="Yes","Purple),IF(Sheet 1!E:E="Yes","Red),"IF(Sheet 1!F:F="Yes","Blue),IF(Sheet 1!G:G="Black")))
Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black
Hope I was able to explain what I am trying to achieve
=IF($F$24,C6*0.75,IF($F$2>=8,C6*0)))
hoping someone can help
im trying to sort my lab charges
if i have in column c I have "cbc" i want column e to have price for cbc which is $8.98
if in column c i have "bmp" then column e should show $14.56
etc
i have about 60 different labs and pricing. what is easiest way to do this so i dont have to manually enter the price each time
thanks!
Hello Aracely,
Please try a VLOOKUP formula. You can learn more about VLOOKUP in Excel in the above linked article on our blog.
Hope you’ll find this information helpful.
I have a worksheet with over 10K lines of data which requires multi IF statements based on different criteria. I can't seem to write or find and IF statement that computes in a range. Example. If the value is above x and below y, multiply by Z. Can you help?
Thank you
Hello !
For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
I have a spreadsheet with rows of data results for 5 tests that have been done and as such if the test has been done would expect to see either a PASS or a FAIL in each cell. However if one of test in that row is not done then that particular cell would be blank. Now I want to add a column which looks at each entry in the row and if any fails then it will remind the user to add some additional information, have tried using logic but I cannot seem to get it to ignore the cell if it is blank. Thus I am after something to solve the following
PASS, PASS, PASS, PASS, PASS = No Action required
PASS, " ", PASS, PASS, PASS = No Action required i.e ignore any blanks
PASS, PASS, FAIL, PASS, PASS = Add addition Info as it sees a FAIL
PASS, " ", FAIL, PASS, PASS = Add addition Info, again as it sees a FAIL
Anyway any help would be greatly appreciated as this is doing my head in...
Hello Andrew!
If I understand your task correctly, the following formula should work for you:
=IF(SUM(--(A1:E1="PASS"),--(A1:E1=""))=5, "No Action", "Action")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alexander,
Firstly thanks for the response, much appreciated. Now apologies, my fault as I should have probably mentioned that there were other cells between each of the entries which contains other information as such I need to look at each of these cells individually and then make a collective decision on the action, thus the spreadsheet breaks down as follows:
Cell 1 = Numeric data, Cell 2 = Time(hh:mm:ss), Cell 3 = Text, Cell 4 = Text & Cell 5 = Result (PASS/FAIL/"")
This then repeats itself another 4 times as each row has 5 probable tests, thus all I want to do is look at Cell 5 of each test and from those entries ascertain whether there is an action to be carried out for that row. i.e. if they are all PASS or blank then no Action is necessary, however if a FAIL is seen then ACTION is required.
Hope this makes sense and apologies for messing you about as I know you guys are likely busy.
Hello Andy!
Change the formula
=IF(SUM(--(E1="PASS"),--(E1=""), --(J1="PASS"),--(J1=""),--(O1="PASS"),--(O1=""), --(T1="PASS"),--(T1=""), --(Y1="PASS"),--(Y1=""))=5, "NO Action", "Action")
I hope it’ll be helpful.
I used to make a formulae using the following scenarios;
> 1,000 amount would appear in the column to all who are non regular status
> 500 for non regular
example: If employee is on a non regular status the equal amount due for employee we will are 500.
Hello Amay!
For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Hi. Please help
I have 23,000 in income. I need to pay 0% in the first 2,000.
3% up to 5,000.
4% from 5,ooo to 10,000
5% on anything above 10,000
I need all the calculation in one formula.
Hello Marina!
Hello
If I understand your task correctly, the following formula should work for you:
=((A1-10000)*0.05)*(A1>10000) + ((A1-5000)*0.04)*(A1>5000)*(A1<10000) + (5000*0.04)*(A1>=10000) + (A1*0.03)*(A1<5000) + (5000*0.03)*(A1>=5000)
I hope this will help, otherwise please do not hesitate to contact me anytime.
In other words, we don't pay anything in the first 2,000 and we pay 3% from 2k up to 5K.
Thank you so much again
Hello Marina!
Please use the following formula
=((A1-10000)*0.05)*(A1>10000)+((A1-5000)*0.04)*(A1>5000)*(A1<10000)+(5000*0.04)*(A1>=10000)+((A1-2000)*0.03)*(A1<5000)*(A1>2000)+(3000*0.03)*(A1>=5000)
Hi Alexander. You are awesome. Thanksfor the help. I review and everything looks good except the last part of the formula. That should be 0% anything 2,000 or less and from 2,000 to 5000 is 3%. We are supper close.
650 ((A1-10000)*0.05)*(A1>10000) correct
200 ((A1-5000)*0.04)*(A1>5000)*(A1=10000) correct
150 (A1*0.03)*(A1=5000) review
Hello Hino!
If cells G15 and G9 contains numbers, it is not quite clear why you get an error. I can suppose that there is a number in one of the cells and the other one contains a space or another non-printing character. Anyway, I recommend using the IFERROR function to process errors in formulas. You can find more info about this function here: https://www.ablebits.com/office-addins-blog/excel-iferror-function-formula-examples/
Alternatively, you may find our Remove Characters tool useful for searching and deleting spaces and other invisible symbols. The tool is available as a part of our Ultimate Suite for Excel. Feel free to install the add-in in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
I hope it’ll be helpful.
Hi Svetlana
I want to add two cells (G15+G9) and then use the result in another formula. However if one of the cells is empty I get a #value result. How can I get the result of either one of the cells is empty.
what formula should I write here?
90-94, with honors
95-97, with high honors
98-100, with highest honors
Please help.
Hello!
Please use the following formula
=IF(A1 >= 90,IF(A1 >= 94,"Honor",IF(A1 >= 97, "High Honor", "Highest Honor")),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
I have two products in kilograms with different Rates and if I sell my products more than first sell . I want continually minus .like
Qty Rate Amount Qty Rate Amount Sell qty sell rate Sell amount
12*15=180,. 10*17=170. Formula cell 15*....=....
Thanks for your providing this is good lesson for me.
Dear.
In a range i have 10 customer name under this name i want to count the customer repeated ie duplicate as 1 and only one condition if cash customer more than one count it all ie 3 cash customer and 7 other customer total is 10 customers please help me to create one formula
Regards
Noby Paul
Dear.
In a range i have 10 customer name under this name i want to count the customer repeated ie duplicate as 1 and only one condition if cash customer more than one count it all ie 3 cash customer and 7 other customer total is 10 customers please help me to create one formula
Regards
Noby Paul
How to write the formula of if cell contains the range ±0.25 then return YES otherwise return NO in the Excel.
Hi Samy,
Assuming you are comparing cell B1 against A1, the following formula will return "yes" if B1 equals A1±0.25, otherwise "no":
=IF(AND(B1>=A1-0.25, B1<=A1+0.25), "yes", "no")
Hi..pls help me for the below conditions.
condition 1: wen entering D4 value =A, then E4 value should be=A
condition 2: wen entering D4 value =blank, then E4 value should be =blank
condition 3: if D4A and D4blank, means it has some data. so that time E4 should be "B".
Give me formula for above condition.
Thanks..
Hi Praveen,
If I understand your task correctly, the following formula should work for you:
=IF(D4="A", "A", IF(D4="", "","B"))
I have a problem where I want to generate a dynamic list based on 2 variables (an odd and a dollar amount) e.g.
Variable 1 - 1.2
Variable 2 - $1.00
I have the following function:
=IF(countif($E$1:E1,"No")=0,(if(($A$1*C1)>($A$2*4), ($A$1*C1)-($A$2*2), if(($A$1*C1)>($A$2*8), ($A$1*C1)-($A$2*4), if(($A$1*C1)>($A$2*16), ($A$1*C1)-($A$2*8),(if(countif($E$1:E1,"No")=1,(if(($A$1*C1)>($A$2*8), ($A$1*C1)-($A$2*4), if(($A$1*C1)>($A$2*16), ($A$1*C1)-($A$2*8)))),($A$1*C1))))))))
Cell C1 is the value of Variable 1 * Variable 2. Column E, is if a condition has been met, and an amount has been deducted from the outcome in the list.
Example of the list:
1 $1.20 $0.20 Yes
2 $1.44 $0.24 Yes
3 $1.73 $0.29 Yes
4 $2.07 $0.35 Yes
5 $2.49 $0.41 Yes
6 $2.99 $0.50 Yes
7 $3.58 $0.60 Yes
8 $2.30 -$1.28 No
9 FALSE -$2.30 No
I want #9 to be $2.30 * Variable 1, until it hits (Variable 2 * 8). Once it hits that limit, it will then be reduced by Variable 2 * 4.
There are quite a few more "limits" that I would like to set, but I've reduced them to simplify. Any help or suggestions would be great.
Thanks.
Hi quick question
How can i do the following
Put conditional pricing based on the criteria..
If H2 is in special customer list (E2:E) than rate = D2 if not a special customer than rate is as listed in Pricing list according to gallon amount in cell J2
Thanks
https://docs.google.com/spreadsheets/d/1uk8MCtQ3ZdYR_KfJVWY4Svnx-Rmg11YuSePHxefn0jM/edit?usp=sharing
How to calculate through xl formula dragging for 2000 rows
Logic.1 Sum first numeric cell starting from A2 at E i.e for row 1 is A2, row2 B2, row 3 C3 and row 4 B4
Logic.2 Sum from 2nd numeric cell to end
i.e for row 1 B1:D1 for row2 B2:D2 for Row3 C3:D3 and row4 B4:D4
- A B C D E F
1 1000 2000 3000 4000
2 - 500 600 700
3 - - 5000 6000
4 - 9000 7000 8000
I'm trying to embed a MID statement for the answer if true. My forumula isn't working, however: =IF(B2=55,(mid(A2,4,2),B2).
Any ideas?
Hello, Annie!
Please try the following formula:
=IF(B2=55,MID(A2,4,2),B2)
Hi,
if it is in excel workbook in one sheet >95 and in other sheet <95 written there, so how we can take average of that( with sign).
please help me to resolve this issue.
Regards,
Vivek
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(OR (AND(Sheet1!A1<95,Sheet2!A1>95), AND(Sheet1!A1>95,Sheet2!A1<95)), AVERAGE(Sheet1!A1,Sheet2!A1),0)
I hope it’ll be helpful.
Please will you give me the function IF in a cell C3 depending of a value in cell B3 with two values , as follows:
in cell b3 with only two values conditions TRUE, with B3=1 or B3=3, in order that:
-for the TRUE two conditions: if B3=1 to give in cell C3 the value effect 5 , and
if B3=3 to give in cell C3 the value effect 10
-for the FALSE condition, if B3 has different value of 1 or 3, to give in cell C3 the value effect 0 (zero).
Thank you.
Hello Dim!
If I understand your task correctly, the following formula should work for you:
=IF(B3=1,5,IF(B3=3,10,0))
If there is anything else I can help you with, please let me know.
Please give me a functio for a cell, as follows:
in cell b3 with only two value conditions b3=1 or b3=3
in order that:
if b3=1 to give in cell c3 the value effect 5
if b3=3 to give in cell c3 the value effect 10
thank you.
Please give me a functio for a cell, as follows:
in cell b3 with only two value conditions b3=1 or b3=3
in order that:
than if b3=1 to give in cell c3 value effect 5
if b3=3 to give in cell c3 value effect 10
thank you.
Hi,
I have some data in which both column and row contains lot of data.
Just want to know that :
1.Column contains data in yellow colour.
2.Want to sum each row with yellow colour .
3.Total of yellow colour data pertains to different different user name.
4.Hence result should be total of yellow colour data in front of respective user name.
Kindly help on this.
Hi Jessica, I am trying to use IF function e.g. If M3:M519 = JNB, then to add an amount that is in the corresponding column cell e.g if M58 = JNB then to add the amount in the corresponding cell e.g. B58 = 195
Does that make sense?
regards Tracy
I need to set formula for the target with criteria as check region is tier I or II then ach.vs target % check , ach.vs ach %, industry growth %, cagr% and stretch the % with 5 or 10 below matrix;
Ach Vs Target Fy 2020 E Ach Vs Ach Fy 2020 E Market share Fy 2020 E YoY Growth Fy 2020 E CAGR Target Growth over PY ach
Tier I Tier II Tier I Tier II Tier I Tier II Tier I Tier II Tier I Tier II Tier I Tier II
65% 65% 0% 0% 2% 2% -15% -15% -10% -10% 5% 5%
80% 80% 10% 10% 8% 8% 1% 1% 1% 1% 10% 10%
90% 90% 30% 30% 10% 10% 5% 5% 5% 5% 15% 15%
100% 100% 50% 50% 15% 15% 10% 10% 10% 10% 20% 25%
125% 125% 90% 90% 18% 18% 20% 20% 20% 20% 25% 30%
Cell A2-A10 has serial numbers
Cell B2-B10 has YES or NO
i want a formula for C2,
IF cell B2 is yes then i need the cell A2's value in cell C2
IF cell B3 is no then i need the cell C3 to be empty
Calculate the following score sheet
NAME GA TWI EWE FRAFRA PERCENT REMARK
a. Use data validate to allow NAME to have not more than 8 characters.
b. When a cell in the name section is selected, the user should be prompted not to type more
than 8 characters in the Name column.
c. Apply conditional formatting to display marks in red color and one decimal place if it is
smaller than 50.
d. Populate your table with the following data:
e. Calculate PERCENT (Note: Percent is the average score for a student) and REMARK.
REMARK is awarded as below:
i. EXCELLENT if any of the marks obtained is more than 90.
ii. V. GOOD if percent is greater than 60
iii. GOOD if percent is greater than 50
iv. FAIL if percent is below 50 or any of the mark is below 60
NAME GA TWI EWE FRAFRA PERCENT REMARK
SATOKA 40 59 24 100
ME LEE 60 67 80 100
EFO LEE 98 1 72 90
LEE SON 88 99 84 60
YOU LEE 76 100 100 90
Please , can someone help me with an appropriate function for question e
We want customers to receive a coupon based on the department they purchased from.
Coupon amounts by department:
Electronics—$25 coupon.
Toys—$20 coupon.
Sports—$15 coupon.
Shoes—$10 coupon.
Every other department—$5 coupon.
Create a column named “Coupon.”
Create a formula that displays the coupon amount for each customer based on department
Hi
Please help me..
My cell values are as follows:-
A1 B1 C1 D1
62 31 0
In cell D1 I need to use a formula that will calculate the value in A1 if C1 =0, but if
C1 >0, then I need to SUM B1+C1
hi.
try this
=IF(C10,C1+B1,0)
Can you please help me to solve this
Fail+Pass+Pass = PASS
Pass+Fail+Pass = PASS
Pass+Pass+Fail = FAIL
Fail+Pass+Pass = FAIL
HOW ??????
Hi - I am trying to work out how to write a formula for the following:
If cellvalue1, "No"
If cellvalue is blank, ""
I have got the yes and no working by using this formula: =IF(E7>1,"No","Yes") but at the moment if there was nothing in cell E7, the cell I have the formula in shows "Yes" and I really want it to be blank (i.e. only say yes or no where there is an actual value in E7). Hope someone can help!
Thanks
kirstie
Hi Kristie, did you find any solution to this? I am in the same condition, looking for that function
please help in the below issue
i have count of 100 people name, and i have to add column B each person against 1 to 10 number.
So it will be like this
Mango 1
Mango 2
Mango 3
Mango 4............
please let me know the formula
its Cntr D i have used but i have large number of data so i can not use the same.
please suggest its urgent
I want multiple of this formula =IF(SUM(COUNTIF(A3,{"*ANCHORAGE*"})),"Alaska",IF(SUM(COUNTIF(A3,{"*Atlanta*"})),"Georgia"))
for several cities, but excel does not let me do more than two IF statements, is there another way to do multiple if statements in one formula?
You can nest as many IF statements as you'd like in Excel.
Just follow the formula:
=IF(Condition,True,IF(Condition,True,IF(...,False)...)
You're just replacing the False part of the IF statement with another IF statement every time. If the first condition isn't true, then it checks for the second, and then the third, etc.
Hello,
I want to calculate an expiry date of a product. The products either have a 5year or 10year working life depending on whether its construction is "rubber" or "synthetic". Over three columns I have MATERIAL, DATED FITTED, EXPIRY DATE. If the material is rubber then I enter =DATE(YEAR(K6)+5,MONTH(K6),DAY(K6)) in the EXPIRY DATE. If the material is synthetic I enter =DATE(YEAR(K6)+10,MONTH(K6),DAY(K6)). Is there a way to make the EXPIRY DATE Column decide automatically how many years to add depending on the contents of the MATERIAL column?
I'm Really struggling with this.
Many thanks.
Tom
hello!
try using if formula for the solution like
=if(A2= "rubber", whole condition with 5 years expiry, if(A2="synthetic", whole condition with 10 years expiry,"")
hi , i m trying to formula for this statment
if A1 greater then 20 twenty then add next row number..
A1 B2
25
32
20
5
I WANT IN B2 NOT MORE THEN 20. ADD NEXT ROW ABOVE 20
hi , i m trying to formula for this statment
if A1 greater then 20 twenty then add next row number..
A1 B2
25
32
20
5
I WANT IN B2 NOT MORE THEN 20. ADD NEXT ROW ABOVE 20
Hi Svetlana..
In Excel Solver, my variable cell output should be either 0 or 20.
how to add this constraint please.
I have an IF formula =IF(D8="X","1","") and now I want to add up the lines that have a 1 in them .. what type of formula will I need?
Take the quote out:
=IF(D8="X",1,0)
Hi Brad did you get any solution for that? I am also looking for the same.
Try =COUNTIF([Your Desired Range],"1")
Is it possible to write a formula for a whole column?
IE, Currently column 'C' divides 'D' but i need a formula that If C>D divide D by C.
Hi Gary,
Usually, you write a formula for the first cell using relative references (without $ sign), and then drag it down to as many cells as needed. This will copy the formula across the column and adjust the references for each row. For example, if your data begins in row 2, enter this formula in any cell in that row:
=IF(C2>D2, D2/C2, "")
And then, double-click the fill handle (a small square at the lower right-hand corner of the cell) or drag it over the cells where you want to copy the formula.
For more information, please see How to copy formula in Excel.
This is the formula I am trying to calculate - here are the criteria https://i.imgur.com/S3u7NJC.png
1 - this is the cell where I need the formula to calculate my cost price.
2 = "Paid" or "Traded" and
3 = the Transaction Type is "Transfer" then
4 - take the value from the cell in column "Adj EUR to AUD Rate" and divide it by
5 - (on the same row as he cell to calculate the cost price)
However only take the value in "4" if on the same row as "4"
6 = FX Transfer
7 = Xfer_Receipted
8 = the date matches the date in the same column as the row of the cell for "1" (the cost rate we are calculating" and
9 does not equal "0"
I hope that makes sense and appreciate any help
@Svetlana Cheusheva are you able to help with my query please?
Dear sir/mam
If sales value is =10 lakh then % incentive will be 4%.how we solve this with the help of nested if function
Hi there - this article is very helpful but I am struggling to create a formula to calculate a cost-price based on various criteria but not sure if these comments are being monitored still and if so should I upload an excerpt from my spreadsheet to show what I am trying to achieve?
=IF(AND(E6="FP",M6>=2,AND(A6>=$K$1,B6=2),AND(A6>=$K$1,B6<=$L$1)),"VD Promo","No Promo")
please help me to run the statement
=IF(AND(E6="FP",M6>=2,AND(A6>=$K$1,B6<=$L$1)),"VD","No Promo")
This is the correct one please help me to run the statement.
date 1 2 3 4 5 6
name total bonus normal
james 300 360 400 370 403 360 2193 393 1800
ken 340 350 334 342 401 359 2126 326 1800
geff 350 370 336 420 389 349 2214 414 1800
brian 300 390 337 436 376 57 1896 96 1800
robert 400 340 361 340 369 367 2177 377 1800
peter 365 366 352 345 346 301 2075 275 1800
nicholus 333 358 369 368 352 300 2080 280
judith 420 309 345 369 323 326 2092 292 1800
muati 440 470 321 370 310 326 2237 437 1800
19090 2890 16200
i need a formula to calculate the sum of any amount in excess of 300 from day 1 to day 6
e.g day 1 (x-300)+day 2(y-300) till the sixth day.remember the sum should not be affected by a negative value i.e if x is less than 300
Hi,
I'm trying to make the logic
if A1=B1 then true; A1=C1 then true; A1 not=B1 & C1 then False.... nested functions OR functions nt working. kindly advice.
Regards
may I know the excel formula if value below 10 and I need to plus 2.
eg. =<10+2