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 117. Total comments: 4573
Using Google Sheets.
I have a formula that is using validated data to display a value in a field:
=IF(H3="0X0", "$0.00", IF(H3="12 x 2", "$120.00", IF(H3="15 x 3", "$220.00", IF(H3="19 x 4", "$380.00", IF(H3="10 x 7", "$350.00")))))
This works perfectly, however, I cannot get total of the values returned. I am using the following formula: =Sum(I3,G3,E3,C3)
Don't worry I figured it out: remove the '$' from the formula and use =Sum(I3+G3+E3+C3)
Using Google Sheets.
I have a formula that is using validated data to display a value in a field:
=IF(H3="0X0", "$0.00", IF(H3="12 x 2", "$120.00", IF(H3="15 x 3", "$220.00", IF(H3="19 x 4", "$380.00", IF(H3="10 x 7", "$350.00")))))
This works perfectly, however, I cannot get total of the values returned. I am using the following formula: =Sum(I3,G3,E3,C3)
I have three worksheets that contain about 50 customer names. How can I assign the salesman name (forth worksheet) to each customer? I would like to do something like: if sheet1 list customer xxx, yyy and zz,their salesmen is A.
Thanks for the advise
HOW TO FIND EARLIEAST DATE CELL CONTAINS TEXT AND D DATE . 28 29-Nov-15 02-Dec-15 09-Dec-15 55 06-Dec-15 09-Dec-15 16-Dec-15
NO THANKS AND I WILL FIND THE ANSWER
HOW TO DO THIS QUESTION
CALCULATE THE CHARGE VALUE BY USING IF FUNCTION
Name units charge
raja 250 condition
kumar 150 0 to 200 - 1UNIT - Rs 6
sasi 800 200 to 500 - 1UNIT - Rs 9
mala 469 >500 - 1UNIT - Rs 12
raciga 640
uthayan 68
susila 342
CALCULATE THE CHARGE VALUE BY USING IF FUNCTION
Name units CHARGE
raja 250
kumar 150
sasi 800
mala 469
raciga 640
uthay 68
susila 342
Thanks, found this really useful! :)
Hi Svetlana, I really love your site, it has helped me multiple times.
I'm currently trying to figure out a way to efficiently write a nested if statement that doesn't involve manually writing it, as I will have 52 nested ifs when finished. I was trying to concatenate the pieces of my formula, but I'm having no luck. Do you have any other ideas? Ultimately what I'm trying to do is have Excel look in one column for a value >0 and if it detects it, show a value from an adjacent cell....then I want to average those numbers.
I need a formula that does the following:
IF the value in cell B1 is BLANK, then return "BLANK"
IF the value in cell B1 is TEXT, then return "Available"
IF the value in cell B1&C1 is TEXT, then return "Filled"
I need help with a formula for conditional formatting.
IF cell D12 is blank and the date in cell C12 is within 14 days of today's date, then the text is formatted red.
Hi Carrie,
Does "within 14 days of today's date" mean 14 days in the past or in the future?
If the target date in C12 is less than 14 days from today.
So, target date is 11/15/15 it would be highlighted because it is due within 14 days.
Hi Carrie,
Select the cells (or rows) you want to format red beginning with row 12 and create a conditional formatting rule will the following formulas:
=AND($D12="", $C12>TODAY(), $C12-TODAY()<14)
Svetlana, it worked perfectly! Thank you for your assitance!
Hi Svetlana
I need a formula which shows the result of two different cell for example
the input from the two cell
1) 97130327 or 811045
2) negative(-) or positive(+)
result in cell
A) If first two character of the first cell is "97" & figure in second cell is positive then result will be "27" & if the second cell is negative then the result will be "37"
B) If first two character of the first cell is "81" & figure in second cell is positive then result will be "40" & if the second cell is negative then the result will be "50"
RESULT 1 CELL 2 CELL
37 97130127 (1000.00)
27 97130127 1000.00
50 811045 (1000.00)
40 811045 1000.00
Hello,
I am needing to integrate a formula into excel to calculate a tiered pricing schedule. The formula needs to assess if the cell value falls into one of 36 different ranges and than multiplies the cell value by a multiplier specific to that range. For example if the cell falls into the range of >5 and 15 and <=30 multiply by 9.33 and so on.
Schedule Example
Range Markup
1 5.01 to 15.00 9.37
2 15.01 to 30.00 9.33
3 30.01 to 55.00 9.30
............ ...
34 9505.01 to 9999.99 4.30
Example Correction: For example if the cell falls into the range of > 15 and <=30 multiply by 9.33 and so on.
Hi Svetlana,
I want to use a formula to match many specific, unique numbers to unique descriptions for two columns. Example is a 7 or 8 digit number (40307014) = specific description. I want to just be able to type in the unique number and have the description pop up automatically that matches the specific number. Do you have any formulas pre-written for this or advice on how to do this?
Thank you!
Hello Excel GURU,
Could you suggest a unique formula for below issue where the last column should automatically refill from formula. 10 till column 6 (for GOLD), 13 till column 12 (for DIAMOND), 25 till column 18 (for SILVER) and so on as I have big list for same criteria.waiting for your helpful response. Thanks in advance!
Serial No. GOLD 10
712755 $10.95
713296 $10.10
717453 $9.99
713812 $9.95
716291 $10.10
Serial No. DIAMOND 13
712755 $9.10
713296 $8.85
717453 $8.99
713812 $9.90
716291 $7.99
Serial No. SILVER 25
712755 $8.00
713296 $7.99
717453 $6.99
713812 $7.75
716291 $7.50
Hello,
I have stages called, 1-2,1-3,1-4 and so on and I need to called these all as Stage 1. Kindly help me how and which formula I can use for this.
Thanks,
Ashish
Hello. I have a cell that I input a simple IF statement into. For example, the formula was "=IF(C2<C3,"PASS","FAIL"). C2 and C3 are both elevations that were calculated with a simple formula also. An example of a formula for C2 or C3 might look like "=380.10 - (.02*10)" where 380.10 is a previous elevation and .02 is a slope in ft/ft and 10' is a width in feet. If the first formula returns a "pass" value, can excel tell me, and show me, which slope value made the statement true? The slope could be one of several different numbers such as .01,.02,.03,.04, and so on.
How do you nest formula across 3 or more columns to pull data from one column if another is blank? letters only no numbers or grades. I want to pull from Col 1, if not Col 2 then Col 3 when blank
Col 1 Col 2 Col 3 RESULT
1. aaa A aaa
2. bbb B bbb
3. - - - N/A
4. AA AA
5. C C
6. B+ B- B+
Thank You,
Bob
I HAVE THE BELOW DATA
IF DATE RANGE IS >361 show p1
if date range is 241 show p2
121 show p3
61 show p4
<=60 show p5.
Can u help me with the formula which can auto populate the data based on this 5 date criteria.
I have a date range of data based of which i want the data to populate.
the range is >361 it should show P1
241 show P2
Could you please help me to put the formula for the following conditions:
95-100 A
90-94 A-
87-89 B+
84-86 B
80-83 B-
74-79 C+
66-73 C
61-65 C-
56-60 D+
50-55 D
00-49 F
SUPER
hello!
I'm trying to create a formula wherein:
Cell value needs to check if it is Stop, Pending and Others.
Checks the Value in another cell it is pending or not then the condition in the table applies. wherein the cell value result will be the size.
Size Category Stop Pending& Others
X-Small <=1 <=1
Small <=3 <=2
Medium <=10 <=6
Large <=20 20 > 15
I'm trying to use a formula to return a value using multiple conditions as shown in this table:
FE Gross PVR
Units 1,450 1,500 1,550 1,600 1,650 1,700
220 32 33 34 37 39 40
210 31 32 33 36 38 39
200 30 31 32 35 37 38
190 26 27 29 32 33 34
180 25 26 28 31 32 33
170 24 25 27 30 31 32
Example: If Units are >189 and less than 1499 and less than 1550, then return the value of 27.
I would appreciate any assistance. Thank you!
for some reason the formulas post here show different what the one i write
=IF(A1>=37,10000,IF(A1>=28,1000),IF(A1>=19,100),IF(A1>=10,10),IF(A1>=1,1))
Hi!
You were almost there. You just have to close all IF's at the end of the formula, like this:
=IF(A1>=37,10000,IF(A1>=28,1000,IF(A1>=19,100,IF(A1>=10,10,IF(A1>=1,1)))))
i also use this
=IF(AND(A1>=1,A1=10,A1=19,A1=28,A1=37),10000,A1)
but it come back #VALUE
I tried all your exaples for an issu i have but non of them comes with the correct answer
i have a series o number for some score that need to be analyzed
12 16 21 33 45
i need to use a formula where is a number falls between a range of number to come back with another result this is my list
1-9 1
10-18 10
19-27 100
28-36 1000
37-45 10000
this is what I came up with
=IF(AND(A1>=1, A1=10, A1=19, A1=28, A1=37),10000,A1)))))
i also use this as a test
=IF(A1<=37,10000, IF(A1<=36,1000,IF(A1<=27,100, IF(A1<=18,10,IF(A1<=9,1)))))
but it only get th the first if statement
I am trying to write an IF statement formula for the following condition:
This is TEXT only, no numbers.
If Column F is blank, import the text value from Column K (same row)
If Column F is not blank, leave it as-is.
Hi,
My data reads:
Status Category
Successful Normal Minor.Production
Successful Normal Minor.Production
Successful Normal Minor.Production
Successful Standard-Minor
Successful Standard-Minor
Successful Normal Minor.Production
I want to compare both the columns for text and return a different text eg: "Successful Minor". I used this statement =IF(AND(C3 = "Successful", E3 = "Minor"), "Minor Successful", ) but this means the column B needs to specify minor. How do I do text search? Hope my question is clear. Appreciate your assistance.
Thank you kindly
Regards,
-V-
Hi .. I am trying to set a formula to calculate a hotel room rate based on the day of week. If Fri, Sat, Sun then $230 and if Mon-Thu $240. Person needs to enter arrival date 1/8/2016 .. then this reports back Fri (=c6 with the custom formatting of ddd)
Ex
Cell C6 has 1/8/2016, then C5 is set to =c6 (custom formatting of ddd). The formula I have is IF(C5="FRI","$230","$240").
Problem is C5 does not read FRI and is shows, but reads as =C6 and $240 is returned.
Any help in getting the formula to recognize day of week that is returned off a formula.
Thank you
I need help!
Conditions:
If sales are greater or equal to 10 then multiply the named cell RateA by Cost
If sales are greater or equal to 20 then multiply the named cell RateB by Cost
If sales are greater or equal to 30 then multiply the named cell RateC by Cost
Hello and please help.
Take Row A through L
If A1 equals K1 then L1 needs to move to B1
How to formulate this
=If(A1=K1;L1=B1) ?????
Hi Luke,
Here's the formula for B1:
=If(A1=K1, L1, "")
The formula will copy a value of L1 to B1 if A1=K1.
Sorry something went wrong ... below the Table again ...
I need a formula (If I guess) create a new Score from the NOT PASS (less than 50) an individually new PASS Score in another cell. I tried many things but failed ... I think it should be like ... The difference from PASS Score to NOT PASS Score plus some individually value added from the difference.
Ehm is ... Thank you all for help and understanding my bad English. I can explain it in German, but this is an english writing/reading forum. Smile
U1 U2 Part Read completed Write Point Score
79 87 10 10 10 10 10 100 50
78 99 6 7 8 6 5 57 55
78 95 5 5 8 6 5 50 58
76 77 5 3 8 8 5 12 16
79 89 6 3 8 4 8 23 35
62 19 6 2 5 5 5 19 26
71 14 5 4 8 L 5 53 45
73 49 4 3 8 6 7 32 31
34 9 5 5 8 5 5 27 23
77 87 5 7 8 5 5 38 29
71 17 5 2 5 4 8 9 18
Sorry ... I cant figure it out ... what happen to the table in my post. I formated in Courier ...
Hello ... I need a formula which counts the difference between the pass score >50 and the not pass score <50 and creates an individual pass score depending on the difference from the no pass score to the pass score.
Sorry my english ... I guess it will be an multiple IF and Formula ...
Thank you for advice and help
U1 U2 Part Read completed Write Point Sco
79 87 10 10 10 10 10 100 100 50
78 99 6 7 8 6 5 57 55
78 95 5 5 8 6 5 50 49 58
76 77 5 3 8 8 5 12 16 25
79 89 6 3 8 4 8 23 26 35
62 19 6 2 5 5 5 19 17 26
0 9
71 14 5 4 8 L 5 53 45 54
73 49 4 3 8 6 7 32 31 40
34 9 5 5 8 5 5 27 23 32
77 87 5 7 8 5 5 38 39 48
71 17 5 2 5 4 8 9 9 18
How can I put the formula for below case:
If "A" Column has various values in different rows like below,
A1 is 12000
A2 is 20000
A3 is 25000
A4 is 30000
A5 is 35000
A6 is 2043
A7 is 1916
I got the result like this
=IF(AND(A1>=12000,A1=2043,E3188<=2043), "SL", "others")
But i want to put it in one formula for whole rows
Hi Shiraz,
Do you mean if A1 is equal to or greater than 40, the profit will be 4% or $20 whichever is higher?
Anyway, you can use a formula similar to this:
=IF(A1>=40, MAX(A1*0.04, 20), MAX(A1*0.04, 15))
ill repeat again..if A1 is 20 , the profit will be 4% of $15 which ever is higher, and if A1 is 40 the profit is 4% or $20 which ever is higher....these are 2 different condition have to apply on cell..hope you understood.
just read A1 20 as a & A1 40 as b,
if A1 is (a) the profit will b 4% or $15 and if A1 is (b) the profit will b 4% of $20...
Shiraz,
I understand your conditions. But what if A1=1, or A1=21 or A1=50. How is the profit calculated? Or can A1 contain only 2 values (20 and 40)?
we have only two products 20 & 40 or you can read as apple & grapes...
for apple let us assume the amount $400 & grapes $600 , now i have to apply a formula to single cel " if A1 is apple then the profit should be 4% or $15 which ever is higher and if A1 is grapes then profit should b 4% or $20 which ever is higher " i hope you got it.
Assuming that A1 is either "apple" or "grapes" and B1 is amount ($), the formula is as follows:
=IF(A1="apple",MAX(B1*0.04,15),IF(A1="grapes",MAX(B1*0.04,20), ""))
thanks :)
hi, i need on formula for below condition
if A1 is 20 the profit will b 4% or $15 whichever is higher and if A1 is 40 the profit will b 4% of $20 whichever is higher.
I need a formula where I say if A2 (which is a persons name) is equal to a name in column B then bring me the information that it shows under this persons name in column D. How may I do this formula?
Hi Lysbeth,
I think you'd better use the VLOOKUP function for this task. A formula can be similar to this:
=VLOOKUP(A2,B2:D100, 3, FALSE)
hi svetlana, i'm having a problem figuring out how to put this in formula
G8 is equal to (46 to 35) is "NI"
G8 is equal to (34 to 23) is "L"
G8 is equal to (22 to 11) is "M"
G8 is equal to (10 to 0) is "H"
Hi Alvin,
You can use nested IF's like in the following example:
https://www.ablebits.com/office-addins-blog/nested-if-excel-multiple-conditions/
Hello.
I need to formula to evaluate a string, for example:
AB12345
CD34567
EF5678
G6789
I have 4 models ( AB CD EF G), and each model has 2 types, Summer and Winter.
Numbers between 1000 1999, 4000 4999 and 6000 6999, classified as Summer. Numbers between 3000 to 3999, 5000 to 5999 and 9000 to 9999 classified as Winter.
I'd like a formula to classify them fast, instead of analising them manually or using procvs, because i have a lot of them, and it gets very slow when i use filters, due to procv formulas.
Like this:
AB12345 "Summer AB"
CD34567 "Winter CD"
EF5678 "Winter EF"
G6789 "Summer G"
Maintain the letter/letters ( it's 1 letter or 2, depends ), and classify the numbers.
It it possible ?
Can someone help me or point me in the right direction please ?
Thank you !
Unable to help me ?
Hi, I am trying to automate a response according to 2 conditions (Likelihood and Consequence in a Risk Matrix). The Matrix provides for 25 scenarios (eg, Likelihood has 5 - Rare, Unlikely, Possible, Likely, Almost Certain. Consequence has 5 - Minimal, Minor, Moderate, Significant, Severe). There is a Likelihood column and a consequence column and a 3rd column which is the a level of risk which is the result of the 25 combinations of the other 2 columns. I thought of using "IF((AND formula but I understand this can only do up to 7 scenarios. Is there anlther formula I can uses?
Hello,
=IF(V4>=27, "Excellent", IF(V4>=19, "Good", IF(V4>=9, "Satisfactory", "Poor "))))
Can you help me regrading this formula because ist not working when try with Excel file , I have value (V4) in my file from 12 to 28,
When I try in single mode formula working properly,
Hi. I am making a simple formula for tests results. I have already a list for the percentage of the raw scores. All I wanna do is when I input the raw score (1st column) its equivalent percentage will automatically show in the next column. :)
Hi Hannah,
If my understanding of the task is correct, VLOOKUP is the right function for the job:
https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/
Hi,
I am having trouble combining the following rules:
1. If A:A ="Amy" AND B:B contains "Show", "Show"
2. If A:A ="Amy" AND B:B contains "Dress", "Dress"
3. If A:A does not equal "Amy", "other"
Thanks
Hi rue,
If you want to return a value from column B if column A contains "Amy", "other" otherwise, use the following formula:
=IF(A1="Amy", B1, "other")
If you need a formula for 2 conditions, "amy" in A and "show" / "dress" in B, use the following one:
=IF(AND(A1="amy",OR(B1="show", B1="dress")), B1,"other")
Hello, new here. Please help me.
Amount of Net Taxable Income Rate
over but not over
10,000.00 5%
10,000.00 30,000.00 500+ 10% of the excess over 10,000
30,000.00 70,000.00 2,500+15% of the excess over 30,000
70,000.00 140,000.00 8,500 +20% of the exces over 70,000
140,000.00 250,000.00 22,500+25 % of the excess over 140,000
250,000.00 500,000.00 50,000 +30% of the excess over 250,000
500,000.00 125,000 + 32% of the excess over 500,000
based on above tax table i would like to come up with a formula to find the tax due of the following taxable income:
1,070,018.83
277,667.62
400,066.46
191,497.18
877,016.35
504,028.75
116,979.02
748,277.72
Thank you in advance to those who can help.
Hi Svetlana,
I am unable to figure this out.
I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding "1 In Production" cell.
Many Thanks
I have this formula
If((AND(S1="CALIFORNIAN", T1=0.5, EXACT(UPPER(X1),X1), LEN(X1)>15)), TRUE, FALSE
This formula works good if all characters are upper case. But I want this formula to also do this, if it is not all upper case then change length to >17. So if my text is Proper the text string can be longer.
Thanks for your help
I need a formula!!!
can you please help me on this....
I have 4 cells with dates
If I enter date in first cell then value should be equal to 10%,
If I enter date in first and Second cells then value should be equal to 30%,
If I enter date in first and Second and third cells then value should be equal to 50%,
If I enter date in first and Second and third and fourth cells then value should be equal to 100%.
Pls help me
Hi im trying that
if input is >100000 and 200000 to < 300000 to be consider as 2 points
like this N number of times.
Means each lack to be consider as 1 point, how to make a formula in excel
Need to create a formula that works for the data below. Please help.
6.50 = Diamond
Thanks in advance.
Hi Svetlana,
You have helped me before with Excel and I need your help again.
I am developing a spreadsheet with details of research studies. I have a comments column which I want to fill in as "Closed" if the study end date in Column N has passed. At the same time for some studies I don't have study end date so unless the date is passed it is showing false whereas I want it to be blank if column N has no date or if teh study hasn't closed yet. Formula I am using is
=IF(AND(N18"",N180),IF((TODAY()-N18)>0,"Closed",IF((TODAY()-N18)<0,"")))
Many thanks for your help
BW
Jamil