You are here
Using multiple IF statements in Excel
The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g. sales are greater than $1000) and calculate a different value for each outcome. However, sometimes you need to work with situations where there are more than two possible outcomes. That's where multiple, or nested, IF functions come in handy. In this tutorial we'll cover how to use nested IF functions to calculate sales commission for a team of sales people, given a range of different commission rates.
This lesson assumes you are already familiar with the IF function. If you aren't, you can learn how to use IF statements here.
Remember that the IF function works by evaluating a logical test  a calculation which can only be TRUE or FALSE, or 1 or 0. It then includes a calculation to perform if the logical test is TRUE, and another calculation if it is FALSE.
Nested IF functions work by replacing one or both of the TRUE/FALSE calculations with another IF function.
Calculating commission for a sales team based on monthly sales
Imagine you have a sales team of five people, and you need to calculate their commission for the month based on their sales figures.

You put the data into the following table:

Your commission plan works as follows:
 If someone sells less than $400 in a month, they get 7% commission.
 For sales between $400 and $750, they get 10% commission.
 For sales between $750 and $1000, they get 12.5%
 For sales over $1000, they get 16%

Rather than calculate each of these commission figures individually, you decide to use a nested IF formula instead. The logical tests you would use in this case are these:
 Is commission less than $400? If TRUE, then calculate commission.
 If FALSE, then is commission less than $750? If TRUE then calculate commission.
 If FALSE, then is commission less than $1000? If TRUE then calculate commission.
 If FALSE, then calculate commission (because it must be more than $1000  we don't need to do another logical test for this).

The formula to represent this to calculate commission for Bob looks like this (I've highlighted the logical tests in bold to help you understand now the formula is put together:
=IF(B4<400,B4*7%,IF(B4<750,B4*10%,IF(B4<1000,B4*12.5%,B4*16%))) 
This formula can be confusing the first time you look at it. Let me run through it again.
 The logical test in the first IF statement checks if the sales figure in B4 is less than $400. If it is, it calculates commission at 7% and stops calculating. Otherwise, it must be greater than or equal to $400, so we move on to the next IF statement.
 The logical test in the second IF statement checks if the sales figure in B4 is less than $750. We already know it must be more than $400 or we wouldn't have got to this point. If it is less than $750, it calculates commission at 10%. Otherwise it must be greater than or equal to $750, so we move on to the next IF statement.
 The logical test in the third and final IF statement checks if the sales figure in B4 is less than $1000. If it is, it calculates commission at 12.5%. Otherwise, it must be greater than or equal to $1000, so it calculates commission at 16%. At this point there are no more IF statements, no more logical tests we need to do, and we have our answer.

Here's how our spreadsheet example looks once the formula has been entered into the Commission column. I've put the formula for each sales person in the cell next to the commission calculation so you can see how it looks:
 Check Rajiv's commission figure in the table above. Rajiv sold $750 in the month, which is right on the threshold between 10% and 12.5%. What commission does he get? He actually gets 12.5% because the formula checks whether his sales were less than $750, not less than or equal to $750. That means he receives commission at 12.5%. So it's important to pay attention to how you construct your logical tests.
Some things to remember when using nested IF functions
Nested IF functions are very powerful, but there are some things to consider before you dive in and start using them.
 As you can see from the commission formula above, using nested IF statements gets complicated quite quickly. Trying to decipher this takes a moment or two, especially if you haven't looked at the spreadsheet in a while.
 Making changes is also challenging  imagine if you decided to introduce another commission threshold of $850. You'd need to add another IF function into the formula.
 You can have up to 64 IF functions nested in a formula in Excel 2007, 2010 and Excel 2013. Excel 2003 only supported 7 IF functions in one formula. That said, if I find myself needing more than 10 I start considering other approaches to my problem  managing that many IF functions in one formula quickly gets complicated, never mind if you have to manage 20, 30, 40 or more.
 The VLOOKUP function can sometimes be a better solution in a scenario like this. You can see a really good example of where you should learn how the VLOOKUP function works in one of the comments below.

That's not to say that VLOOKUP is automatically a better solution:
 A commission spreadsheet like the one shown above is a good example where using VLOOKUP would be a better approach.
 However, if you needed to use different calculations depending on the outcome of each logical test, then nested IF statements may well be a a better way to go.

An example might be where sales people who exceed $1000 in sales also get a bonus of $100. The formula would then look like the following, which is not so easy (although not impossible) to do with a VLOOKUP function:
=IF(B4<400,B4*7%,IF(B4<750,B4*10%,IF(B4<1000,B4*12.5%,B4*16%+100)))
Finally, I've supplied a copy of the spreadsheet I used for the example above. You can download it here.
Want to learn more? Try these lessons:
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat  we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.
Comments on this lesson
Nested IF statement
Use this formula IF(E1<5999,150,IF(E1<60007999,300,IF(E1<800011999,400,"Some stmnt here")))
which use the IF statement with the Cell reference and check the last statement.
Make sure that the number of
Make sure that the number of If's you use are the same in terms of number with the number of closing bracket
If Statement
Need help. wrote following If statement, but all it gets me is True or false.
=IF(D5>89%,"A",If D5>79%,"B", IfD5>69%,"C")))))
Tryding to equate the percentages to a letter grade
Try without the % sign
I'd try this:
=if(d5>.89,"A",if(D5>.79,"B",if(d5>.69,"C","Fail")))
Note how I used the numeric value rather than a % value.
The example you gave was also missing the () in the correct places. Also, if you don't have a value for "Fail" as shown in my example, it will show FALSE as the result in the cell. You could put "" if you want the cell to be empty if the grade is not above 69%.
Also consider whether you should use >=.9 rather than >.89  this will be more accurate if 90% is required for an A grade (your example would assign an A to a grade of 89.5%.
You can use the percent
You can use the percent symbols your missing the parenthesis after your IF
I don't even know where to start..
First, I would like to say thank you for your terrific lessons and taking the time to respond to the questions your visitors ask; it is very kind and helpful.
I hope you can help me, as I don't even know where to start. I am to create a formula in which the changes will be consistent throughout. Here is an example of what I am trying to achieve:
I have a dollar amount in cell M2. This dollar amount is sales numbers.
Column N is titled Blue Shirts, O is Red Shirts, P is Green Shirts, Q is Pink Shirts and R is Orange Shirts.
There is a bonus for each shirt the salesmen sells; the bonus is also determined by their total sales.
If the salesrep sells less than 750, there is no sales volume bonus. If they sell between 751 and 1,200 there is a 2% bonus; between 1,201 and 2,000 there is a 3% bonus; if they sell between 2,000 and 2,500 there is a 4% bonus, Etc. Etc.
If they sell a Blue shirt, the bonus is 0%, Red shirts is a 2% bonus, Green is a 1.3% bonus, pink is a 1.3% bonus and orange is a 1.3% bonus. ** This bonus will be on top of the sales volume bonus!
Each column for the color of shirts sold has an â€˜Xâ€™ marking if they sold that color.
Is it possible to create a formula using an IF formula which will allow me to calculate the sales volume bonus AND the bonus for color of shirts sold so it is all tied in together?
I hope this makes sense, it is very hard to explain! This is the formula I have so far for just calculating the sales volume bonus (something is not working right, I am having a hard time making it consistent all the way down the rows):
=IF(M2<400000,M2*Data!$C$3,IF(M2<400001,M2*'Volume Bonus'!$C$5,IF(M2<600001,M2*'Volume Bonus'!$C$6,IF(M2<900001,M2*'Volume Bonus'!$C$7,IF(M2<1200001,M2*'Volume Bonus'!$C$8,IF(M2>1800001,M2*'Volume Bonus'!$C$9))))))
I thank you so much in advance for even taking the time to look at this problem, I sure hope you can help!
I should have been more clear..
For an example, the end result will tell me if the salesrep sold 450, they receive a 2% bonus; they also sold a red shirt and a orange shirt (which makes it a total of 3.3% bonus. How much is the total bonus given?
This will need to be done for a few hundred rows. Each column for the shirt colors have an X in the row to indicate if they sold that color...
Complicated Sales Commission Calculation (with download)
Hi Kristin
What I love about Excel is that there are many ways to skin this cat. Here's one which doesn't even use any IF functions (and stays considerably less complicated as a result).
First, let me show you how I interpreted your scenario:
I've calculated the Sales and Shirt bonuses in two separate columns, but the formulas could easily be combined into the same column once you've got it working.
I've also made an assumption that if some sells <750 then they get no bonus at all. That's why sales person B and C get nothing even though they both sold red shirts. It's easy to remove that assumption from the example, as you'll see.
I also created two separate tables to contain the threshold sales values and the commissions for each colour so that I can keep my formulas simple. I'd normally put them on a separate sheet to the sales data, but for this example they're on the same sheet:
Note that I have named each of the cells in the Shirt Bonus table so I can refer to the formulas by name. I won't cover how to do that here, but will come back to it in another lesson. Suffice to say that the cell containing the bonus for Blue is called, well, Blue. Naming a cell lets me refer to it by name in a formula rather than by using $F$14.
I then created the formula to calculate the sales commission based on actual sales (Sales Bonus). For this, I used a VLOOKUP() function to compare the sales with the values in the Sales Bonus Table. Here's the formula for sales person A:
=VLOOKUP(B2,$B$14:$C$18,2,1)
You can learn more about how to use VLOOKUP here.
I calculated the Shirt Bonus like this:
=((C2="X")*Blue+(D2="X")*Red+(E2="X")*Green+(F2="X")*Pink+(G2="X")*Orange)*(B2>750)
As I promised, no IF functions at all. Here's how this works.
 It looks at the value in C2 (the Blue column) to determine if it contains "X".
 If it does, the value of (C2="X") is returned as TRUE, or 1.
 If it doesn't, the value of (C2="X") is returned as FALSE, or 0.
 I then multiply the result by the value in Blue. If it doesn't contain "X", then the result will be multiplied by 0 which gives 0. Otherwise it's multiplied by 1, which gives whatever is in Blue (or Red, and so on)
 I repeat this for each column.
 Finally, I then look at the sales value to determine if it is >750. Again, the result is either 1 or 0. I multiply this by the rest of the formula. So if the sales value is <750, the result of the whole calculation is 0, otherwise the value is allowed to stand as calculated.
If you want to pay a bonus on shirt sales regardless of the $ value of sales, simply change this formula like so:
=(C2="X")*Blue+(D2="X")*Red+(E2="X")*Green+(F2="X")*Pink+(G2="X")*Orange
Having done this, I then add the two commission values together in column J, and calculate the $ bonus in column K. Once again, you could combine all of these into a single formula if you want.
The only shortcoming of this approach is the need for the two tables for Sales and Shirt bonuses. However, while it might be a shortcoming, it also gives you greater flexibility to extend the number of shirt colours in future, and also to change the threshold sales levels and related commission amounts.
Hopefully this gives you the result you're looking for  if not, please let me know!
I've attached my worked example here for you to dissect and reuse as you see fit. If you'd like more help, feel free to get in touch directly via the contact form.
David
In Between
How would you do in between ranges like this:
=IF(G4<100,100G4,IF(G4>100<105,105G4,IF(G4>105<115,115G4,,IF(G4>115<125,125G4,)))
Using IF on ranges
There are three ways to achieve this.
Option 1
In your example, you are doing this:
If G4 < 100 then the result is 100G4. If G4 is not < 100, then move on to the next IF statement.
You have then tried to use G4>100<105, i.e. if G4 is more than 100 but less than 105. The thing to remember is that we already know that G4 is greater than 100, because it failed the test in the first IF statement. We wouldn't even be using the second IF statement if it had passed that test. So your function could look like this:
=IF(G4<100,100G4,IF(G4<105,105G4,IF(G4<115,115G4,IF(G4<125,125G4))))
Remember that each IF statement is only evaluated if the previous IF statement tested FALSE.
Bear in mind that the way I've written this function assumes that if G4=100, then the calculation will be 105G4. Is that what you wanted? If not (i.e. if G4=100, then calculate 100G4) then you should change each IF statement to <=, rather than <.
Option 2
The other option you have is to use the AND function as well as the IF function, like this:
=IF(G4<100,100G4,IF(AND(G4>=100,G4<105),105G4,IF(AND(G4>=105,G4<115),115G4,IF(AND(G4>=115,G4<125),125G4))))
This is longer, more complicated, but allows for a situation where you need to be specific about the parameters of the test you're performing. Note that the AND function requires that both statements be TRUE for the AND test to return TRUE to the IF function. As a side note, you could also use the OR function here if only one of the statements needed to be true.
Again, notice that I've used the <= symbol in each AND  if I hadn't, the formula would not always have returned the correct result.
Option 3
You can also use a VLOOKUP function rather than an IF function. I'd be inclined to take this option since it allows you to create a table of threshold values that the VLOOKUP function references, and you can change or add to those values at any time without having to rework your formula.
You can read more about the VLOOKUP function here (opens in a new tab).
Thank you! I test both, I
Thank you!
I test both, I wanted to avoid doing a vlookup to another table, but that is a great option. Both option 1 and 2 work, I went with option 2 and modified it to handle %.
=IF($G31<1,1$G31,IF(AND($G31>=1,$G31<1.05),1.05$G31,IF(AND($G31>=1.05,$G31<1.15),1.15$G31,
IF(AND($G31>=1.15,$G31<1.25),1.25$G31,"Achieved Top Tier %"))))
I really appreciate you taking the time to respond and so quickly!!!
Another Choice for Zero
Hi! Please can you help me? I need an IF equation for an answer of zero that returns different text than a false response. Thanks!
Situation (a) Plan is more than or equal to Actual = answer of Well Done!
Situation (b) Plan is less than Actual = answer of Oops...Over!
Situation (c) Plan=zero and Actual=zero = answer is no text at all.
=IF text question
can you help me please
i need a formula that will return a text initial based on the text in another cell;
for instance
if cell C20 contains "JAIL" (w/out quotations) then T20 will show "J" (w/out quotations)
i have been working on this for some time and can only get it to return #name?
thanking you in advance!!!
laura
THANK YOU!!! after just a few
THANK YOU!!! after just a few minutes of searching your lessons i figured it out..
=IF(C20="JAIL","J","")
and it was simple as i thought it would be.. i just couldn't see the forest for the trees i suppose :)
laura
how to use If and() & 0r() fuction
which funtion is used and how to get greatest number among them which can be written as e.g "first NO is greatest".plz solve my problem
Help
Hi,
I wonder if you can help. I am trying to Apple this formula for about 90 players using VB how do I do so?
=IF(G2:G15=AR4,AS4)
What I need to do is nested IF Statements however Excel only allows 7, so I thought maybe I could create a function in VB Excel.
What I need i to do is auto populate a cell A with if Cell B (Player Name) matches Cell C (Player Names)
Ok so to put it in pseudo code:
IF( Player Name entered in Cell A is = Player name on a list of players in Cell B, Take that Players Position in Cell C and put it in his Postion Column in Cell D)
E.G. I enter Tom, it looks in Cell B, if Tom Exists in Cell B, it looks for his Playing Position e.g. Defender and auto populates his Position in Cell D so resulting in it tell me Tom is a Defender.
I now want that to do so for any given cell in A.
Hope I make sense, and I hope you can help. Thanks in advance
Hi I have a similar scenario
Hi
I have a similar scenario but it has an accumulation on rows so will try to explain.
I have a sales threshold of Â£10,000 before i earn commision, in my spreadsheet i need to record products sold and the order value. To help with the formula i have created two columns, "Order Value" and "Claimable Value". So for instance in row one i sell something for Â£8,000 so enter this in to the "Order Value" column, in the next column "Claimable Value" i used the formula =IF(B2<(A1),"0",SUM(B2(A1))). So in A1 i enter my threshold of Â£10,000, then in Cell B2 i enter Â£8,000 (Order Value) so the value then returned in C2 (Claimable Value) is Â£0....perfect. If i changed the "Order Value" to Â£12,000 in B2 then C2 returns Â£2,000....perfect as i know i can then apply a percentage commision to C2.
Now for my problem, when i move to the next line i can't figure out the cummulative formula to take in to account multiple lines. In my sheet i have a potential for 30 lines, as soon as i get over Â£10,000 in any line i want the "C" column to show the amount. So stick with the first example above in B2 i enter Â£8,000 so C2 returns Â£0, then on the row below in B3 i sell something for Â£4,000 because B2 & B3 exceed A1 by Â£2,000 (Â£10,000) i need the calculation to enter Â£2,000 in to C3. I could then move to the next line and enter Â£3,000 into B4, but this time i have already exceed Â£10,000, the calculation has given me the value in C3 but with this new sale i need to discount that as i am already over my threshold so get paid on the Â£3,000 in B4 so need this entered in to C4. Reason being each line has the potential to get paid at a different percentage which i enter else where so each distinct row needs to return it's value taking in to account the previous row and the threshold, to say i am stuck is an understatement.
As i said the formula above works perfectly for one row but expanding that to the rest is getting impossible, can you help?
Calculate sales as a running total
It looks like there are a couple of things you need to do here.
First, you should use an absolute reference to A1 in your formulas. In the example you used for C2, it would look like this:
=IF(B2<$A$1,"0",SUM(B2$A$1))
The $ signs make the reference to A1 absolute. Not matter where you copy this formula within your spreadsheet, this formula will always refer to A1.Then, when you copy the formula in C2 down to C3, the formula in C3 will look like this:
=IF(B3<$A$1,"0",SUM(B3$A$1))
Notice that the formula still refers to A1. Notice also that I removed the brackets you had around A1 in your example, since they aren't needed, and removing them simplifies the formula.
However, the real problem you've got is that this formula doesn't take account of additional sales in subsequent lines. Therefore, the formula you really need in C2 is one which calculates a running total of your sales, like this:
=IF(SUM($B$2:B2)<$A$1,"0",SUM($B$2:B2)$A$1))
In both cases, the SUM function here will add up all the values between B2 and B2. This may seem odd, but it makes more sense when you look at this formula after copying and pasting it into C3:
=IF(SUM($B$2:B3)<$A$1,"0",SUM($B$2:B3)$A$1))
Now the SUM function is calculating the sum of all the values between B2 and B3, i.e. your accumulated sales for those two lines. Therefore, if the cumulative total remains below the threshold value in A1, your commission will be 0. Otherwise, the commissionable amount will be the cumulative total minus the threshold value.
Let me know how you get on with this solution. You can read more about absolute and relative references here, and more about calculating a running total here. Both of these links will open in a new window. If you're still stuck, either post your questions here or contact me directly.
MULTIPLE IF  WITH ADDITION
I have a complex formula and need to consider variables AND addition. Here's my scenario.
 My users will determine the variables by marking 'Y' or 'N' on each of 8 store segments
 My formula must take each of the 8 criteria into consideration and pass that column by (if it's an 'N') or use the field for calculation if it's marked 'Y'
 In some scenarios, ALL 8 store segments could be in selected
Here's my formula, and it's not performing as intended:
=IF($M8="y",$M12*V$9,IF($M8="N",0)+IF($N8="y",$N12*V$9,IF($N8="N",0))
+IF($O8="y",$O12*V$9,IF($O8="N",0)+IF($P8="y",$P12*V$9,IF($P8="N",0)
+IF($Q8="y",$Q12*V$9,IF($Q8="N",0)+IF($R8="y",$R12*V$9,IF($R8="N",0)
+IF($S8="y",$S12*V$9,IF($S8="N",0)+IF($T8="y",$T12*V$9,IF($T8="N",0))))))))
To simplify for your reference, here's two segments of the formula:
=IF($M8="y",$M12*V$9,IF($M8="N",0)+IF($N8="y",$N12*V$9,IF($N8="N",0))
M8 denotes the first store segment
M12 denotes distribution centers n8 denotes the second store segment
V9 denotes the number of pieces per location
My question is this? How can I ADD the IF's together? A simple SUMIF won't work as I need the individual IF statements to function independently, then add the results together. HELP!
Any suggestions?
Try using a logical test on each criteria
My first thought is you probably don't even need to use the IF function for this scenario. I would try something like this:
=($M12*V$9*($M8="Y"))+($N12*V$9*($N8="Y"))+etc
What you'll find is that ($M8="Y") will evaluate to TRUE if M8 contains "Y" and FALSE if M8 doesn't contain "Y". In Excel, TRUE=1 and FALSE=0. So each segment in the formula will be multiplied by 1 or 0 depending on whether the check cell contains "Y". Those segments that don' t have "Y" will therefore be calculated as 0, and ignored in the final total.
If you want to idiotproof this a little, you could allow for different inputs in M8 by replacing ($M8="Y") in the formula with the following:
(OR($M8="Y"),($M8="Yes"),($M8="y")))
This will ensure the formula works for a variety of inputs. The OR statement will evaluate each logical test within it separately (you can have as many as you like). If any of the tests are true, then the OR function will return TRUE. If all of them are not true, then the OR statement will return FALSE.
Hopefully this helps  if not, please let me know.
Help
Hi all,
i need to create a formula in excel sheet like:
if there is many type of cell like numeric and alphabet then how i create a multiple IF condition formula in sheet for exaple
if
H then H
if
W then W
if
CL then CL
if
Time 8:15 then P
kindly help
Checking if a cell contains text or numbers
It depends whether you want to test for specific text values, like H, W, CL, or whether you want to have one set of tests if the value is text, otherwise another set of tests if the value is a number (time is stored as a number in Excel).
You can use the ISTEXT() function to check whether a cell contains text. It will return TRUE if the value in the cell is a text value, and false if it is a number. This is a useful function if you have imported some data into a spreadsheet and need to check whether numbers have been imported as numbers or text values.
For example:
=IF(ISTEXT(A1),value_if_true,value_if_false)
The value_if_true and value_if_false would each be another IF statement, depending on what you want.
In terms of testing whether a value contains a certain time, you will need to use the TIMEVALUE() function.
For example, let's assume you've entered a time into cell A2 by typing 8:15 directly into the cell. Excel will recognise this as a time and store it as a number, but format it as a time.
Your IF function would then look like this:
=IF(TIMEVALUE("8:15")=A2,value_if_true, value_if_false)
Note that if you wanted to use 8:15pm, you would need to either enter the time as "20:15" or "8:15 PM".
Hi,
Hi,
Kindly help me to create formula for the below table:
achvmnt target %incentive
<90 0
9099% 50%
100% 100%
101,102â€¦199% 101,102â€¦199%
200 &> 200% 200%
if the acheivement is < 90% i should get 0 as the incentive % if the acheivement ranges from 9099% incentive% should automatically show 50%,if it ranges from 101% to199% incentive % should be 101,102 means 102 like that it should follow if the incentive acheived by employee is more than 200% it should restricted to 200%
calculate income tax
kindly help me to create a formula:
i need to crate a calculator of income tax
if income <=200000 no tax
if income >200000 to 500000 10% tax
if income >500000 to 1000000 20% tax
if income >1000000 then 30% tax
i want it to be created in nested if .
Calculating Income Tax
Hi Tarun
This is a fairly simple scenario which a formula containing nested IF statements will handle easily:
Let's assume that the income amount is in cell A1:
=IF(A1<=200000,0,IF(A1<500000,10%,IF(A1<=1000000,20%,30%)))
This formula will calculate the amount of tax. To calculate the tax amount, you could use this version of the formula instead:
=IF(A1<=200000,0,IF(A1<500000,A1*10%,IF(A1<=1000000,A1*20%,A1*30%)))
I hope this helps!
Regards
David
Calculating incentives
Hi Priyanka
I've just posted a response to another comment directly below this one which looks at a very similar sales commission example. You should be able to use that example as the basis for solving your problem.
I would also recommend you consider using a VLOOKUP function instead. Your incentive table is fairly complicated and (I suspect) subject to change over time. Keeping the table in a separate location on the spreadsheet and referring to it from a VLOOKUP function will make it much easier to change the contents of the incentive table. You can read our VLOOKUP lesson here (opens in a new tab).
Regards
David
Help with Nested IF/AND with text Evals included
Hi!
I have a formula I have been working on for awhile now and I am close but not quite there and hoping you can help.
I have the following table example.
Level # Shifts Y or N?
A 3.1
AG 1.5
AD 0
P 3.5
A 2.25
AG 2.55
P 2.75
AD 2
The Levels have the following criteria to equal Yes
A=>3
P=>3
AG=>2
AD=>0 (it may not have a value in the cell at all)
I have come up with the following formula that work until I attempt to add in the "AD" part. Ideally if the cell reference contains "AD" I would prefer it just to return "AD", but will take what I can get. Can you help me sort the last part out so that I have 1 formula that I can use for all of it?
=IF(AND(A2="A",B2>=3),"Yes",IF(AND(A2="AG",B2>=2),"Yes",IF(AND(A2="P",B2>=3),"Yes","No")))
Thanks.
IF/AND statement
Hello,
I'm trying to write an IF statement that will automatically calculate the number of total hours per individual. For example to calculate Peter's total hours (E2) the statement will look at cells A2:A5 and B2:B5 to determine if his name exists. If the statement meets that first test it will determine if $C>0, then sum the hours of each person.
Ultimately the result in column E will read Peter: 10, Tim: 6, Sarah: 14, John: 10
A B C D E
1 Name #1 Name #2 Hours Name Total Hours
2 Peter Tim 2 Peter
3 Sarah Peter 8 Tim
4 John Sarah 6 Sarah
5 Tim John 4 John
Any help is appreciated, thank you,
Bryan
Try using SUMIF instead
Hi Bryan
Here's what I understand you're after:
 Each person gets one row where their name is first, so you have as many rows as you have people.
 There will also be one row where their name is second.
 Your objective is to add up the total hours for rows that include their name in the first or second column.
I would suggest using the SUMIF function for this rather than an IF statement. You can read the lesson on SUMIF here (opens in a new window).
The formula you need would include two SUMIF functions, one to look at the name in the first column and one to look at the name in the second. You'll also need to use absolute refererences in the formula. You can read more about absolute and relative cell references here (opens in a new window).
The SUMIF function looks at a range of cells, compares each value to a criteria cell (or range of cells) and, and adds up the value in a third range of cells whenever there is a match. Because you've got two columns on which you want to do this, you need to use two SUMIF functions in one formula..
Here's an example of one of the formulas you'd need:
=SUMIF($A$1:$A$4,D1,$C$1:$C$4)+SUMIF($B$1:$B$4,D1,$C$1:$C$4)
Below is a screenshot of a quick spreadsheet I created to illustrate your example:
Pay careful attention to the use of absolute and relative references in the formula. I've created this formula in such a way that you can copy and paste the formula down from row 1 to row 4 without it breaking. However, if you wanted to add another row later, you'd need to edit the formula in the first row to include the new row ($A$5, $C$5, etc). Another option would be to name the range of cells and reference that name in the formula. Adding rows would then involve redefining the named range, which is a fairly simple process.
I've attached the spreadsheet I created for this example for you to download.
I hope this helps.
David
This is exactly what I was
This is exactly what I was looking for. Thank you.
You're welcome
Hi Bryan
Glad I could help!
Regards
David
If Statement between sheets
Hi,
I need an IF Statement that will compare the value of a cell in one sheet to a cell in another sheet, if they are the same, it needs to take the value of a different cell from sheet 2 and put it into the a cell in sheet 1. Any ideas?
Kelsey Layne
Need help in conditional formatting
if a1 is todays date and a6 is the due date i want a7 to come up with "paid" if paid on or before due date and "due" if passed due date
Comparing dates
Hi Vinesh
You don't say which cell contains the date when it was actually paid. For the sake of argument, I'll assume the following:
 A1 contains today's date
 A2 contains the date it was paid (blank if not yet paid)
 A6 contains the date it was due
If this sounds right, you could use a formula like this:
=IF(A2<=A6,"Paid on or before",IF(A1>=A6,"Due",IF(A1<A6,"Not yet due","")))
This formula does the following:
 Check to see if the bill was paid on or before the due date. Will return "Paid on or before" if it was, otherwise ...
 Check to see if todays date is after the due date, in which case return "Overdue", otherwise ...
 Check to see if today's date is before the due date, in which case return "Not yet due", otherwise ...
 Return "Due Today".
=if(AE14:AE14<=75,"B",if(AE14
=if(AE14:AE14<=75,"B",if(AE14<80,"D",if(AE14<85,"AP",if(AE14<90,"P", if(AE14<101,"A")))))
anyone who could help me on this formula..... thanks in advance....
More information please ...
Hi Lanie
Can you explain what the problem is?
I can see a couple of possible problems:
 Change AE14:AE14 to just AE14 in the first IF statement.
 You haven't VALUE_IF_FALSE value in the final IF statement. What happens if AE14 is not less than 101?
David
Hi David..
Hi David..
I am doing my class record, and i want the descriptive rating automatically displayed once the periodic rating are computed.
for example:
B2 C2
B2 cell is for periodic rating
C2 cell is for descriptive rating
here are the legend:
>74 below is B
>7579 is D
>8084 is AP
>8589 is P
>90100 is A
if the grade is 98, then on the descriptive rating must be displayed A...
Thanks for you help :) God bless..
Hi Lanie
Hi Lanie
Here's the formula I would use:
=IF(A1<75,"B",IF(A1<80,"D",IF(A1<85,"AP",IF(A1<90,"P",IF(A1<98,"A","A+")))))
The spreadsheet this comes from looks like this:
However, this is a scenario in which I'd use a VLOOKUP function rather than multiple IF statements in a single formula. It allows you to create a simpler formula, and you can change the gradebook more easily without having to change all of the individual gradebook formulas. You can read about the VLOOKUP function in this lesson.
Regards
David
hi thanks much for your help.
hi thanks much for your help... it works...
actually my first option is the vlookup function, but am having a problem in the pointing system, which is for example 89.90, i don't want to list all the decimal places for the vlookup, so i've come up to use the if statement formula.. :) :) :) many thanks to you.....
i still another questions if you don't mind? :D
More help?
Hi Lanie
Of course  happy to help. Fire away!
David
Multiple Conditions for IF Statement
I'm trying to do a formula that says if a cell is greater than one cell and less than another cell then I will have one message if true and another if false.
Using the AND function to evaluate multiple conditions
Hi Michele
You could do this in a couple of ways.
Let's assume you have the following cells:
 A1 contains the value you are comparing.
 A2 contains the value you want A1 to be greater than
 A3 contains the value you want A1 to be less than
The simplest option would be to use a single IF function combined with the AND function, like this:
=IF(AND(A1>A2,A1<A3),"Message if true","Message if false")
The AND function allows you to include two or more logical tests  in our case, these are "A1>A2" and "A1<A3". They are logical tests because each test will return either TRUE or FALSE. The AND function will also return TRUE or FALSE. For the AND function to return TRUE, all of the included logical tests must evaluate to TRUE. Therefore, this IF function will only return "Message if True" if both tests are true. The alternative to AND is OR, where only one of the statements would need to be true for the OR function to return TRUE.
Another option to solve your problem would be as follows:
=IF(A1>A2,IF(A1<A3,"Message if true","Message if false"),"Message if false")
This is clearly less efficient because we need to include the "Message if false" twice. In a very large spreadsheet you'd probably find that this solution slows the calculation speed of the spreadsheet. However, there are situations where it is more flexible than my first solution. However, I'd always start with the first solution because it's simpler!
I hope that helps.
David
Excel2007
if i type in d5 or e5 then it will automatically perform this formula in f5 cell "f4+d5e5". but problem is if i input any one of d5 or e5 this have to perform that formula. please help.
Count with multiple conditions
Hello, I'm trying to find the formula for the count, with two if statements. I want the count of cells, if C2:C450 shows "N" and E2:E450 is "ACCORD". So for example, in order to be included in the count, C35 would have to show N, AND E35 would have to show ACCORD. Can you help me? I've tried so many different things and can't get it right!
Here's what I currently have, but it's obviously not giving me a value.
=COUNTIF(C2:C450,"N")*AND(E2:E450,"ACCORD")
need help to calculate time by category
Hi all genius...
i need help on how to create a formula to as below for running 2.4km based on the given time:
Age category A is 20~24, B is 25~29, C is 30~34, D is 35~39, & E is 40<above
For category A, Grade A is below 12min, B < 13min, C < 14min, & above 14mins id Failed.
pls help me ... thank u all...
need help to calculate time by category
Hi all genius...
i need help on how to create a formula to as below for running 2.4km based on the given time:
Age category A is 20~24, B is 25~29, C is 30~34, D is 35~39, & E is 40<above
For category A, Grade A is below 12min, B < 13min, C < 14min, & above 14mins id Failed.
pls help me ... thank u all...
solving discount of different percentages
hi
help with formula to generate discount for the items where laptop is given a discount of 12% tv = 9% and radio = 8%
the table is as follows:
item price discount
laptop 12000
radio 1000
laptop 45000
radio 15000
tv 50000
laptop 50000
radio 5000
laptop 65000
radio 2000
tv 21000
laptop 75000
Need help to calculate the Over Time amount based on day & OT hr
Hi,
Below is the sample date , I need help to calculate the OT based on day "Sunday" and based of "Total Duration". I has mentioned the sunday OT rate card also.
EmployeeName Date In Time Out Time Total Duration Ot Rate
abcd 01 Sep 2013 11:54:45 16:53:25 4:58:40
abcd 2Sep13 11:54:45 16:53:25 4:58:40
abcd 3Sep13 11:54:45 16:53:25 4:58:40
abcd 4Sep13 11:54:45 16:53:25 4:58:40
abcd 08 Sep 2013 10:24:52 16:03:51 5:38:59
Sunday OT Rate
<5=120
>5<8=230
>8=300
Calculating overtime with multiple IF functions
Hi Abraham
You can do this using a formula containing multiple IF functions like this:
=IF(E1<5,E1*120,IF(E1<8,E1*230,IF(E1>=8,E1*300)))
In this example, E1 contains the duration for the person you are calculating overtime for.
Note that my formula uses a slightly different table to yours. Your example would not have paid overtime for exactly 5 hours or exactly 8 hours. My formula will pay 230 for 5 hours and 300 for 8 hours. If you want to pay 120 for 5 hours and 230 for 8 hours, then use this formula instead:
=IF(E1<=5,E1*120,IF(E1<=8,E1*230,IF(E1>8,E1*300)))
I hope this helps.
Regards
David
Mutliple If/And with ranges
Hello. I'm trying to make a formula that will input the cost of a unit by two given variables.
This is what I was able to come up with so far:
Quantity Stitch Count Unit Cost
22 2000 ???
The scenario is that the person can input the quantity and stitch count of the item, and the unit cost will calculate the amount based on both arguments.
So I was able to come up with this formula to do that:
=IF(AND(AND(D3>=0,D3<=23),AND(E3>=0,E3<=5000)),D3*2.5,)
However I need to add a number more arguments in order to calculate any option the person could put in. When I try to add to the formula I receive an error for Value.
Stitches 123 2435 36+
0 â€“ 5000 $2.50 $2.00 $1.50
50006000 3.00 2.30 1.75
60007000 3.50 2.60 2.00
70008000 4.00 2.90 2.25
The stitches will go up to 16000, so I anticipate it to be a long formula.
Is this possible to accomplish? I hope this wasn't too confusing....
Nevermind
Nevermind! I was able to figure it out
Need "If" formula
Hi,
I have date & time as 10/14/2013 12:24:00 PM in cell A2 and in cell A3 I have data & time as 10/15/2013 00:15:00 AM, I need a "IF" formula in cell A4 which gives me "Ontime" if the cell A3 date and time is within 10/14/2013 23:59:00 and If date and time is greater than 10/15/2013 00:01:00 AM it should give me as "Delay in report"
Help Needed: If/Then, Multiple Conditions
In my scenario we have three Material Markups: A and B are specific to Items L and R, and C which applies to all other items
We also have three Item Types: L and R are specific and S is a standard which not L or R
Here is the logic:
If ITEM = A, then Markup L, FALSE use Markup S
If ITEM = B, then Markup R, FALSE use Markup S
If ITEM <> A or B, then Markup S.
The formula I came up with will not address all the variables. Any help would be appreciated! Thank you.
Use a formula with nested IF statements
Hi D
You need to use a formula like this:
=IF(item=A,markup L,IF(item=B,markup R,markup S))
 The first IF function evaluate the item to see if it is A.
 If it is, then Markup L is applied.
 If it is not, the next IF function determines if the item is B.
 If it is, then markup R will be applied.
 If it is not B, Markup S will be applied.
Because the IF functions are nested in this way, S will be applied if the item is neither A nor B. The only time the second IF function will be used is if the item is not A, which means that if the second IF function finds that the item is not B, then we already know it is not A, so Markup S must therefore be applied.
This is obviously a simple example. You could have a scenario where you have items of type A,B,C,D,E,F with markups to match. In a more complex scenario such as this, I'd consider using the VLOOKUP function instead of a formula with nested IF statements as we have here. You can read our VLOOKUP lesson here.
Regards
David
multiple 'ifs'
Hi, I am trying to get the results of a test to include an average of various parts of the test, that means that the numerical result will not necessarily be a whole number. Then I want convert the number in to a grade, A1, A1+1 etc. For some reason the formulae I have created don't work; can you see why? I've been over it several times and have reached a dead end!
=IF(B13<1,"A1",IF(B13=1,"A1",IF(B13<=1.5,"A1",IF(B13>1.5,"A1",IF(B13=2,"A1",IF(B13<=2.5,"A1",IF(B13>2.5,"A1+1",IF(B13=3,"A1+1",IF(B13<=3.5,"A1+1",IF(B13>3.5,"A1+2",IF(B13=4,"A1+2",IF(B13<=4.5,"A1+2",IF(B13>4.5,"B1",IF(B13=5,"B1",IF(B13<=5.5,"B1",IF(B13>5.5,"B1+1",IF(B13=6,"B1+1",IF(B13<=6.5,"B1+1",IF(B13>6.5,"B1+2",IF(B13=7,"B1+2",IF(B13<=7.5,"B1+2",IF(B13>7.5,"C1",IF(B13=8,"C1",IF(B13<=8.5,"C1",IF(B13>8.5,"C1+1",IF(B13=9,"C1+1",IF(B13<=9.5,"C1+1",IF(B13>9.5,"C1+2",IF(B13=10,"C1+2",IF(B13<=10.5,"C1+2",IF(B13>10.5,"C2",IF(B13=11,"C2",IF(B13<=11.5,"C2",IF(B13>11.5,"C2+"))))))))))))))))))))))))))))))))))
thanks for any advice!
Multiple ifs
I have to solve a formula which divides the gross between 2 companies with min comp. If its <$24/day co1 gets 100% if >$24<$48 Co2 gets 100%
>$48 goes 50% to both companies. Can I use IF formula for this?
# of Days "Gross
Collected"
36 578.60
24 449.75
32 642.00
42 680.50
31 581.75
25 562.75
24 563.75
42 1,056.25
21 554.25
42 1,045.00
18 595.00
thanks
Yes, the IF function will do this for you
Hi Egs
The IF function will work for you here but you'll need two formulas  one to calculate company 1's share of the gross and one to calculate company 2's share of the gross.
You have described three possible outcomes, which means we need two IF functions in each formula.
Lets assume that the # Days is in cell A1, and the Gross is in B1. We'll calculate company 1's share in C1 and company 2's share in D1.
In C1, enter this formula:
=IF(A1<24,B1,IF(A1>=48,B1*50%)
In D1, enter this formula:
=IF(A1<24,0,IF(A1<48,B1,B1*50%)
This should solve the problem you've described.
Regards
David
coming up with False
Hi Dave,
I am trying to make this work but no success. It's coming up with False value. I have min comp for 2 companies.
Co1 gets (31days*$24) at any values. Sp if the values is < 24 co1 gets it all. then if value is >$24/day Co2 gets >24*31 up to (48*31days) 100% then whatever is over both min comps goes 50% to both co.
thanks a bunch
Your logical tests are the problem
Hi Steve
In this case, the fourth IF function is the problem. The logical test in that function is B13>1.5. If this test is TRUE, the IF function will return A1, and Excel will ignore everything that comes after it. The way you've set it up, this test will always be true. Reading through the formula, it looks to me like the logical test in in this case should be B13<2. This will catch all values greater than 1.5 and less than 2.
In addition, you've repeated this error multiple times by having multiple IF functions that use a greater than test rather than a less than test. You'll need to fix each of those in the same way.
Two further points:
 Our VLOOKUP lesson will show you a method of returning grades that is WAY easier than using multiple IF functions.
 If you want to stick with multiple IF functions, you can write this function in a much simpler way.
To simplify this function, look at the first portion of your formula:
=IF(B13<1,"A1",IF(B13=1,"A1",IF(B13<=1.5,"A1"
In this case, you've tested B13 for three separate values, yet the TRUE value has been "A1" every time. Why not just write this:
=IF(B13<=1.5,"A1"
If you decide later to have different grades for different values below 1.5, then add back more IF functions. But for now, you've got two IF functions that you dont' need and which are simply complicating your formula. And if that scenario is a possibility, then a VLOOKUP function will make like a lot easier for you.
Hopefully that helps.
Regards
David
David, many thanks for your
David, many thanks for your excellent advice! I am changing the whole thing to use vlookup as you suggest and it already works as it should! Thanks for taking the time, much appreciated.
Need Help with Nested IF Function based on multiple criteria
I need an IF Function to calculate the Rating column based on two percentages. (the EOY Group Results with the EOY Achieved result). Any assistance would be great. Example of what I need calculated is listed below.
Performance Element EOY Group Results EOY Achieved Results Rating
New Hire Retention Rate 82.9% 76.5%
Criteria is as follows:
If the Achieved Result is
1 5 or less percentage points below the EOY Group Result (the rating result should return a 4)
2 Less than 8 but more than 5 percentage points below the EOY Group Result the rating result should return a 3)
3 Less than 10 but more than 8 percentage points below the EOY Group Result (the rating result should return a 2)
4 More than 10 percentage points below EOY Group Result (the rating result should return a 1)
Here is what I have so far but it doesn't seem to be working for me. Based on my calculations for the about two percentages I should be getting a rating result of 3 but each time I play with the formulas I come up with a 4 or a 1
Any help would be greatly appreciated.
=IF(C7<B710%,1,IF(C7<B7+10%>B7+8%,2,IF(C7+5%<B78%,3,IF(C7<B75%,4))))
You need to nest the IF statements in the right order
Hi Paul
The problem with your formula is the way you've structured it. Specifically, your logical test in the second and third IF statements. Excel can't evaluate a logical test like "C7<B7+10%>B7+8%", so it will return FALSE every time. So your formula is currently returning 1 if C7<B710%, or 4 in all other cases. There is a way for the IF function to handle complex logical tests like the ones you've used, by using the AND or OR logical functions, which you can read about here. However, I think you can handle your requirements with just nested IF statements. Keep reading to find out how.
Looking at your criteria for assigning ratings, the first test is fine.
The second test should be C7<B78%. This test will only be required if the first test returned false. In other words, the only time this test is needed is if C7 is NOT less than B710%.
Similarly, the third test should be C7<B75%. The first two IF statements already established that C7 is not less than B710%, and C7 is not less than B78%.
Finally, it looks to me like the fourth test should actually be C7<B7. If the formula gets as far as evaluating this test, then we know that the value in B7 must be greater than or equal to B75%. However, I assume that this formula should return anything if C7=B7 or C7>B7. Therefore, C7<B7 is the right test.
With all that in mind, here's what the final formula should look like:
=IF(C7<B710%,1,IF(C7<B78%,2,IF(C7<B75%,3,IF(C7<B7,4,""))))
Note that I've added a value_if_true to the final IF statement. This will ensure that nothing is returned if C7>=B7. If you leave the final value empty (which is OK when using the IF function) then the formula would return FALSE in this scenario.
I hope this solves your problem  let me know if it doesn't. Also, you might find the VLOOKUP function helpful as an alternative to using nest IF statements. You can view our VLOOKUP lesson here.
Regards
David
Calculations using percentages instead of $ figures
Hi there. I do understand how this example is functioning, but I'm trying to use it with a group of percentages....and it's not functioning. What am I missing? Example: If the margin% cell is 049% then calculate the total sale cell by 0%. If the margin cell is 5054.99%, then calculate the total sale cell by 3%. Am I barking up the wrong tree?
Hi Marie
Hi Marie
Based on what you've described, I'd write the IF function like as follows:
Let's assume that the Margin% cell is A1 and the Sales cell is A2:
=IF(A1<50%,0,A2*3%)
This formula looks at the Margin% cell in A1 and checks if it is less than 50%. If it is, then the IF function returns zero. If not, then it returns the result from multiplying the Sales cell by 3%. In this case it would return 3% of the sales value. If you wanted to return the sales value plus 3% instead, then use A2*103%.
Note that you only need one IF function in this scenario, because there are only two possible outcomes (049% or 50%54.99%)
If you had a third possible outcome, 55%60%, with a percentate of 3.5%, then the IF function would look like this:
=IF(A1<50%,0,IF(A1<55%,A2*3%,A2*3.5%))
You could keep on extending it in the same way for additional outcomes. However, once you get past 34 IF functions in the formula, I'd suggest you consider using a VLOOKUP function instead, where you look up the margin amount in a table which returns the correct percentage to multiply the sales amount by. This has three advantages over multiple nested IF functions:
 The formula is a lot less complex.
 You can change the percentages in the table and all of the formulas automatically recalculate (i.e. you don't have to edit the formula to reflect the new percentages)
 You can make the table larger (more rows) without having to add extra IF statements to handle the additional outcomes.
You can view our VLOOKUP lesson here.
Hopefully this helps. Let me know if I've missed anything.
David
David, Thanks so much, I'm
David, Thanks so much, I'm pleased to report that at the 11th hour, I figured it out on my own. Her
Pleas assist help me to correct this formula
=IF(E1<5999,150,IF(60007999,300,IF(800011999,400,)))
for the table below
Gross Income (Ksh) Monthly Contribution (Ksh)
Less than 5,999 150
6,000  7,999 300
8,000  11,999 400
12,000  14,999 500
15,000  19,999 600
20,000  24,999 750
25,000  29,999 850
30,000  49,999 1,000.00
50,000  99,999 1,500.00
Over 100,000 2,000.00