Excel IF statement with multiple AND/OR conditions, nested IF formulas, and more

In Part 1 of our Excel IF function tutorial, we started to learn the nuts and bolts of the Excel IF function. As you remember, we discussed a few IF formulas for numbers, dates and text values as well as how to write an IF statement for blank and non-blank cells.

However, for powerful data analysis, you may often need to evaluate multiple conditions at a time, meaning you have to construct more sophisticated logical tests using multiple IF functions in one formula. The formula examples that follow below will show you how to do this correctly. You will also learn how to use Excel IF in array formulas and learn the basics of the IFEFFOR and IFNA functions.

How to use Excel IF function with multiple conditions

In summary, there can be 2 basic types of multiple conditions - with AND and OR logic. Consequently, your IF function should embed an AND or OR function in the logical test, respectively.

  • AND function. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met; otherwise it returns FALSE.
  • OR function. In case you use the OR function in the logical test, Excel returns TRUE if any of the conditions is met; FALSE otherwise.

To better illustrate the point, let's have a look at a few IF examples with multiple conditions.

Example 1. Using IF & AND function in Excel

Suppose, you have a table with the results of two exam scores. The first score, stored in column C, must be equal to or greater than 20. The second score, listed in column D, must be equal to or exceed 30. Only when both of the above conditions are met, a student passes the final exam.

The easiest way to make a proper formula is to write down the condition first, and then incorporate it in the logical_test argument of your IF function:

Condition: AND(B2>=20, C2>=30)

IF/AND formula: =IF((AND(C2>=20, D2>=30)), "Pass", "Fail")

Easy, isn't it? The formula tells Excel to return "Pass" if a value in column C >=20 AND a value in column D >=30. Otherwise, the formula returns "Fail". The screenshot below proves that our Excel IF /AND function is correct:
Excel IF function with multiple AND conditions

Note. Microsoft Excel checks all conditions in the AND function, even if one of the already tested conditions evaluates to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.

In practice, a seemingly correct IF / AND formula may result in an error because of this specificity. For example, the formula =IF(AND(A2<>0,(1/A2)>0.5),"Good", "Bad") will return "Divide by Zero Error" (#DIV/0!) if cell A2 is equal to 0. The avoid this, you should use a nested IF function:

=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")

Example 2. Using IF with OR function in Excel

You use the combination of IF & OR functions in a similar way. The difference from the IF / AND formula discussed above is that Excel returns TRUE if at least one of the specified conditions is met.

So, if we modify the above formula in the following way:

=IF((OR(C2>=20, D2>=30)), "Pass", "Fail")

Column E will have the "Pass" mark if either the first score is equal to or greater than 20 OR the second score is equal to or greater than 30.

As you see in the screenshot below, our students have a better chance to pass the final exam with such conditions (Scott being particularly unlucky failing by just 1 point : )
An example of IF/OR formula

More formula examples can be found in Excel IF OR functon.

Example 3. Using IF with AND & OR functions

In case you have to evaluate your data based on several sets of multiple conditions, you will have to employ both AND & OR functions at a time.

In the above table, suppose you have the following criteria to evaluate the students' success:

  • Condition 1: column C>=20 and column D>=25
  • Condition 2: column C>=15 and column D>=20

If either of the above conditions is met, the final exam is deemed passed, otherwise - failed.

The formula might seem tricky, but in a moment, you will see that it is not! You just have to express two conditions as AND statements and enclose them in the OR function since you do not require both conditions to be met, either will suffice:

OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)

Finally, use the above OR function as the logical test in the IF function and supply value_if_true and value_if_false arguments. As the result, you will get the following IF formula with multiple AND / OR conditions:

=IF(OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)), "Pass", "Fail")

The screenshot below indicates that we've got the formula right:
Using IF with OR & AND functions

Naturally, you are not limited to using only two AND/OR functions in your Excel IF formulas. You can use as many logical functions as your business logic requires, provided that:

  • In Excel 2016, 2013, 2010 and 2007, your formula includes no more than 255 arguments, and the total length of the formula does not exceed 8,192 characters.
  • In Excel 2003 and lower, you can use up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.

Using multiple IF statements in Excel (nested IF functions)

If you need to create more elaborate logical tests for your data, you can include additional IF statements in the value_if_true and value_if_false arguments of your Excel IF formulas. These multiple IF functions are called nested IF functions and they may prove particularly useful if you want your formula to return 3 or more different results.

Here's a typical example: suppose you want not simply to qualify the students' results as Pass/Fail, but define the total score as "Good", "Satisfactory" and "Poor". For instance:

  • Good: 60 or more (>=60)
  • Satisfactory: between 40 and 60 (>40 and <60)
  • Poor: 40 or less (<=40)

To begin with, you can add an additional column (E) with the following formula that sums numbers in columns C and D: =C2+D2

An additional column that sums numbers in columns C and D

And now, let's write a nested IF function based on the above conditions. It's considered a good practice to start with the most important condition and make your functions as simple as possible. Our Excel nested IF formula is as follows:

=IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor "))

As you see, just one nested IF function is sufficient in this case. Naturally, you can nest more IF functions if you want to. For example:

=IF(E2>=70, "Excellent", IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor ")))

The above formula adds one more conditions - the total score of 70 points and more is qualified as "Excellent".

An example of nested IF functions

For more information about Excel IF with multiple conditions, please see How to use nested IF in Excel.

Using Excel IF in array formulas

Like other Excel functions, IF can be used in array formulas. You may need such a formula if you want to evaluate every element of the array when the IF statement is carried out.

For example, the following array SUM/IF formula demonstrates how you can sum cells in the specified range based on a certain condition rather than add up the actual values:

=SUM(IF(B1:B5<=1,1,2))

The formula assigns a certain number of "points" to each value in column B - if a value is equal to or less than 1, it equates to 1 point; and 2 points are assigned to each value greater than 1. And then, the SUM function adds up the resulting 1's and 2's, as shown in the screenshot below.
Using Excel IF in array formulas

Note. Since this is an array formula, remember to press Ctrl + Shift + Enter to enter it correctly.

Using IF function together with other Excel functions

Earlier in this tutorial, we've discussed a few IF formula examples demonstrating how to use the Excel IF function with logical functions AND and OR. Now, let's see what other Excel functions can be used with IF and what benefits this gives to you.

Example 1. Using IF with SUM, AVERAGE, MIN and MAX functions

When discussing nested IF functions, we wrote the formula that returns different ranking (Excellent, Good, Satisfactory or Poor) based on the total score of each student. As you remember, we added a new column with the formula that calculates the total of scores in columns C and D.

But what if your table has a predefined structure that does not allow any modifications? In this case, instead of adding a helper column, you could add values directly in your If formula, like this:

=IF((C2+D2)>=60, "Good", IF((C2+D2)=>40, "Satisfactory", "Poor "))

Okay, but what if your table contains a lot of individual scores, say 5 different columns or more? Summing so many figures directly in the IF formula would make it enormously big. An alternative is embedding the SUM function in the IF's logical test, like this:

=IF(SUM(C2:F2)>=120, "Good", IF(SUM(C2:F2)>=90, "Satisfactory", "Poor "))
Using IF with the SUM function

In a similar fashion, you can use other Excel functions in the logical test of your IF formulas:

IF and AVERAGE:

=IF(AVERAGE(C2:F2)>=30,"Good",IF(AVERAGE(C2:F2)>=25,"Satisfactory","Poor "))

The formulas retunes "Good" if the average score in columns C:F is equal to or greater than 30, "Satisfactory" if the average score is between 29 and 25 inclusive, and "Poor" if less than 25.

IF and MAX/MIN:

To find the highest and lowest scores, you can use the MAX and MIN functions, respectively. Assuming that column F is the total score column, the below formulas work a treat:

MAX: =IF(F2=MAX($F$2:$F$10), "Best result", "")

MIN: =IF(F2=MIN($F$2:$F$10), "Worst result", "")

If you'd rather have both the Min and Max results in the same column, you can nest one of the above functions in the other, for example:

=IF(F2=MAX($F$2:$F$10) ,"Best result", IF(F2=MIN($F$2:$F$10), "Worst result", ""))
Using IF with the MIN and MAX functions

In a similar manner, you can use the IF function with your custom worksheet functions. For example, you can use it with the GetCellColor / GetCellFontColor functions to return different results based on a cell color.

In addition, Excel provides a number of special IF functions to analyze and calculate data based on different conditions.

For example, to count the occurrences of a text or numeric value based on a single or multiple conditions, you can use COUNTIF and COUNTIFS, respectively. To find out a sum of values based on the specified condition(s), use the SUMIF or SUMIFS functions. To calculate the average according to certain criteria, use AVERAGEIF or AVERAGEIFS.

For the detailed step-by-step formula examples, check out the following tutorials:

Example 2. IF with ISNUMBER and ISTEXT functions

You already know a way to spot blank and non-blank cells using the ISBLANK function. Microsoft Excel provides analogous functions to identify text and numeric values - ISTEXT and ISNUMBER, respectively.

Here's is example of the nested Excel IF function that returns "Text" if cell B1 contains any text value, "Number" if B1 contains a numeric value, and "Blank" if B1 is empty.

=IF(ISTEXT(B1), "Text", IF(ISNUMBER(B1), "Number", IF(ISBLANK(B1), "Blank", "")))
Using IF with ISNUMBER, ISTEXT and ISBLANK functions

Note. Please pay attention that the above formula displays "Number" for numeric values and dates. This is because Microsoft Excel stores dates as numbers, starting from January 1, 1900, which equates to 1.

Example 3. Using the result returned by IF in another Excel function

Sometimes, you can achieve the desired result by embedding the IF statement in some other Excel function, rather than using another function in a logical test.

Here's another way how you can use the CONCATINATE and IF functions together:

=CONCATENATE("You performed ", IF(C1>5,"fantastic!", "well"))

I believe you hardly need any explanation of what the formula does, especially looking at the screenshot below:
Using the result returned by IF in another Excel function

IF function vs. IFERROR and IFNA

Both of the functions, IFERROR and IFNA, are used to trap errors in Excel formulas and replace them with another calculation, predefined value or text message. In earlier Excel versions, you can use the IF ISERROR and IF ISNA combinations instead.

The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA specialize solely in #N/A errors.

Here is the simplest example of the IFERROR formula:

=IFERROR(B2/C2, "Sorry, an error has occurred")
An example of using the IFERROR function in Excel

As you see in the screenshot above, column D displays the quotient of the division of a value in column B by a value in column C. You can also see two error messages in cells D2 and D5 because everyone knows that you cannot divide a number by zero.

In some cases, however, you may not want to trap all errors, but rather test the condition causing a specific error. For example, to replace a divide by zero error with your own message, use the following IF formula:

=IF(C2=0, "Sorry, an error has occurred", B2/C2)

And that's all I have to say about using the IF function in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

2,600 Responses to "Excel IF statement with multiple AND/OR conditions, nested IF formulas, and more"

  1. Barb says:

    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?

  2. SANDEEP RAJA JONNADA says:

    Hi Svetlana..
    In Excel Solver, my variable cell output should be either 0 or 20.
    how to add this constraint please.

  3. rashid says:

    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

  4. rashid says:

    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

  5. Tom says:

    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

    • M.Arif says:

      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,"")

  6. Dolores Marichal says:

    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?

    • Michael says:

      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.

  7. Adi says:

    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

  8. Kirstie Hardy says:

    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

  9. Laiba says:

    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 ??????

  10. Antoinette says:

    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

  11. Simon Jamboe says:

    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

  12. David Dercross Freeman says:

    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

  13. Roshan says:

    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

  14. Nilesh says:

    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%

  15. Tracy says:

    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

  16. Radhika Gupta says:

    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.

  17. Dim Di says:

    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.

  18. Dim Di says:

    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.

  19. Dim says:

    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.

  20. Vivek says:

    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

  21. Annie says:

    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?

  22. Sohail says:

    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

  23. Sholom says:

    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

  24. Dan says:

    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.

  25. Praveen says:

    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..

  26. Samy says:

    How to write the formula of if cell contains the range ±0.25 then return YES otherwise return NO in the Excel.

  27. Noby Paul says:

    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

  28. Noby says:

    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

  29. Chheang Eam says:

    Thanks for your providing this is good lesson for me.

  30. Hardeep Kumar says:

    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*....=....

  31. Al says:

    what formula should I write here?
    90-94, with honors
    95-97, with high honors
    98-100, with highest honors
    Please help.

  32. Nino says:

    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.

  33. 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/2017/09/27/excel-iferror-function-with-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.

  34. MARINA says:

    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.

      • Marina says:

        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

      • marina says:

        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

  35. amay says:

    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.

  36. Andrew McNicol says:

    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.

      • Andy says:

        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.

  37. Raef M says:

    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

  38. aracely says:

    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!

  39. Iayaz Pasha says:

    =IF($F$24,C6*0.75,IF($F$2>=8,C6*0)))

  40. AG says:

    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

  41. Michael Gaddafi Makena Church says:

    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.

  42. kau says:

    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 ?

  43. RAGUNATHAN says:

    =IF((B5+C5)<=25400,"900"(B5+C5)<=29900,"1100"(B5+C5)<=30400,"1300"(B5+C5)<=33400,"1500"(B5+C5)<=35400,"1700",IF((B5+C5)<=37300,"1800",IF((B5+C5)<=41100,"2300",IF((B5+C5)<=44500,"2600",IF((B5+C5)<=50200,"2900",IF((B5+C5)<=51600,"3100",3200))))))
    how can use these conditions in a formula excel doesnot allow morethan 8

  44. Ryan says:

    I have been working on a formula for an movie list of 1500 Dvds, Blu-rays, and 4ks that I'm backing up on 3 HDDs, 1 for each type. Now all the movies have a "type" column C2 which will only be a value "SD", "SD/HD", "HD", "HD/UHD", "UHD". A "on HDD" column I2 which will only be a value "Y", "N", "Y/Y", "Y/N", "N/Y", "N/N". These represent if the movie was copied to hard drive/s (some have read errors), I have 3 more columns 1 for each drive L2 = drive 1 SD, M2 = drive 2 HD, N2 = drive 3 UHD. Now if I have a DVD/Blu-ray movie (SD/HD) the Y/Y part matches the type so if the DVD SD did not make it to hard drive but the blu-ray did it would be "N/Y" my ? Is if I just want a "X" in columns L,M,N if the movie made it to the coralating HDD =IF(AND(OR(C2="SD",AND(I2 ="Y",OR(C2="SD/HD",AND(I2="Y/Y",OR(I2="Y/N", "X","") I know I'm on the right track, I will do that for each drive column changing out "Type" but I need help with the correct way to write out the formula, Please Help, Thanks.

    • Hello Ryan!
      If I understand your task correctly, the following formula should work for you:

      =IF(OR(
      AND(C2="SD",I2 ="Y"),
      AND(
      C2="SD/HD",OR(
      I2="Y/Y",I2="Y/N",I2="X",I2="")
      )
      ),TRUE,FALSE)

      For me to be able to help you better, please describe your task in more detail. Your explanation of the formula is very difficult to understand. It’ll help me understand it better and find a solution for you. Thank you.

  45. Tracy says:

    I need a formula that says if cell "M" is "true" and Cell "N" is "yes" then sum cells Q & T but if cell "M" is "false" and "N" is "no" the cell T but if cell "m" is "false" and cell "n" is "yes" cell T

  46. JSR says:

    =If(and(A2="A",B2="A" or "B"),"Yes","No")
    Using like this if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify

  47. JSR says:

    =If(and(A2="A",B2="A" or "B"),"Yes","No")
    Using like this in excel, if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify

  48. Swapnil says:

    1)If= first month(1 Jan to 31 Jan) all floors commission 2%
    2)If= second month (1feb to 29 Feb) floor wise commission
    Lower floor - 3%
    Middle floor - 3.5%
    Higher floor - 4 %
    3) if = third month ( 1 March onwards) onwards floor wise commission
    Lower floor - 2 %
    Middle floor - 2.5%
    Higher floor -3%

    Please help how to create formula in 1 cell in excel

    • Hello Swapnil!

      if you write data -
      A B C D E
      Month 1 2 3 2/10/2020
      Lower floor 2 3 2 Middle floor
      Middle floor 2 3.5 2.5
      Higher floor 2 4 3

      Please try the following formula:

      =INDEX(B2:D4,MATCH($E$2,A2:A4,0), MONTH(E1))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • Swapnil says:

        sorry I do not understand of this

        • A1 - Month, A2 - Lower floor, A3 - Middle floor, A4 - Higher floor, B1 - 1, B2 - 2, B3 -2, B4 - 2, C1 - 2, C2 - 3, C3 - 3.5, C4 -4 and so on. E1 - 2/10/2020 , E2 - Middle floor

          • Swapnil says:

            Thank you very much it's working
            but one more problem one Month Condition was changed. Condition is Date of period so formula not working...
            *Date of period
            1 ) 1 Jan 2019 to 2 Feb 2020 -commission all floors 2%
            2 ) 3 Feb 2020 to 29 Feb 2020 - commission floor wise( L, M, H) 3 %, 3.5%,4%
            3 ) 01 March 2020 to 31 march 2021* commission floor wise (L, M, H) 2%,2.5%,3%
            Can you please help .....

            • Hello Swapnil!
              Please try the following formula:
              =IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(YEAR(TODAY()),3,31),3,0))))), "Date out of range")
              Hope you’ll find this information helpful.

              • Swapnil says:

                Formula not working on pending period 01 april 2020 to 31-03-2021 ...working only march 2020 ..
                Third condition not completed.. Please help.

              • Swapnil says:

                And one more condition add
                3 types of sources in this
                X, y, z
                X and z eligible for all condition commission
                But y not eligible for all. Only eligible 2% commission in all types of condition

                Please help....

          • swapnil says:

            please help its very urgent

            • Hello!
              If you want to learn something, try to understand how the formula works, and not ask for help all the time.
              =IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(2021,3,31),3,0))))), "Date out of range")

  49. Aimal says:

    Hi there,

    I have a total of "overtime and regular hours". I want my regular to show the hours in total cell of OV and RE, but not exceed over 40.
    for example:
    Total of overtime and regular is 32.
    Regular cell show the 32.
    but when the overtime and regular cell is > 40, the regular cell show only 40.

    Can you please help me with a formula?

    • Hello Aimal!
      If I understand your task correctly, the following formula should work for you:
      in B21:
      =IF(SUM(A1:A20,B1:B3)>40, 40, SUM(B1:B20))

      where A1: A20 is overtime, B1: B20 is a regular time.
      If that's not what you wanted, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  50. ahmed says:

    I want to put this equation in cell H57
    1) if cell I54 value is = or than 20 and than 40, then multiply (I54 X N44)

    so please advise how to right this equation

  51. Jesse Payette-Steinberg says:

    Hi,

    Would the if function work for this?

    Port/City Vancouver Montreal
    Shanghai 27 34
    Shenzhen 28 35
    Guangzhou 35 42

    Example:
    Select: Shanghai, Vancouver
    Time: 27 days
    Select: Shanghai, Montreal
    Time 34 days

  52. Yixo says:

    I am trying to create a formula that will calculate the LTV (loan to value) I can use. Here is what I have so far.

    =IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%")))

    However, I want to add another two IF conditions that will increase each percentage by 5% if they have 740+ credit AND $120000 annual salary. Thanks in advance for the assistance!

    • Hello Yixo!
      If I understand your task correctly, maybe the following formula should work for you:

      =IF(AND(D4>=740000,D5>=120000), IF(D3>=850000, "80%", IF(D3>=500001, "85%", IF(D3<=500000, "90%"))),IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%"))))

      Hope you’ll find this information helpful.

  53. ratheesha says:

    hello, iam trying to counts pages based on if formaula, but i am not getting the answerer
    If pages are 1 to 5 my answer should be 1
    if more than 5 pages and less than or equal to 14 should be 2
    If anything more than 14 pages my answer should be 3 - but this i am not getting
    Here is my formula which i applied, can anyone suggest me on this
    =IF(B25,"2",IF(B215,"3")))

  54. Robin says:

    Hi, I am looking for a formula to build where if the answer in cell E10 = Yes, then populate information in cell D10. As an example, if Yes, then it would populate YM-SL. The information in column D is various sizes. Or I could create a formula in column E, that if Yes, populate with answer from D. Either formula would be helpful. Thank you

  55. Fareez says:

    I am trying to get a formula as per condition below:

    Condition 1 - 0<A6<=2
    Condition 2 - 0<B6<=2
    Condition 3 - N/A

    "OK" if criteria as below met.
    A6 B6
    1 1
    1 2
    2 1
    2 2
    N/A 1
    1 N/A
    N/A N/A

    "Not OK" if criteria as below met.
    A6 B6
    1 3
    2 3
    3 1
    3 2
    3 3
    N/A 3
    3 N/A

    Need help. Thanks

  56. Blaise says:

    I am trying to Populate a Date cell in L7 and I have two cells to choose from T7 and U7. I want L7 to be T7 if T7 has a date if not I want it to be U7 since that will always have a date. The Date in T7 will change due to plan optimization.

  57. lalit says:

    how to connect two condition under each have some conditions?

  58. Sylvia says:

    My apologies if you answered this already. I have a spreadsheet with Monday-Sunday in each column (A-G). State Names in Columns H-O. Unique People names in P column and their phone number in column Q.
    Monday
    Monday

    Monday
    Monday
    Monday
    Monday

    I want to know who is available to travel on Monday to Utah.
    I want the formula to provide me with a list of people and their phone number

  59. Khaled Ali says:

    I have this formula =IF((AND($F2>=VLOOKUP($C2&""&F$1,Sheet3!$C$5:$E$1200,2,FALSE),$F2=VLOOKUP($C2&""&G$1,Sheet3!$C$5:$E$1200,2,FALSE),$G2=VLOOKUP($C2&""&H$1,Sheet3!$C$5:$E$1200,2,FALSE),$H2=VLOOKUP($C2&""&I$1,Sheet3!$C$5:$E$1200,2,FALSE),$I2=VLOOKUP($C2&""&J$1,Sheet3!$C$5:$E$1200,2,FALSE),$J2=VLOOKUP($C2&""&K$1,Sheet3!$C$5:$E$1200,2,FALSE),$K2=VLOOKUP($C2&""&L$1,Sheet3!$C$5:$E$1200,2,FALSE),$L2=VLOOKUP($C2&""&M$1,Sheet3!$C$5:$E$1200,2,FALSE),$M2=VLOOKUP($C2&""&N$1,Sheet3!$C$5:$E$1200,2,FALSE),$N2=VLOOKUP($C2&""&O$1,Sheet3!$C$5:$E$1200,2,FALSE),$O2=VLOOKUP($C2&""&P$1,Sheet3!$C$5:$E$1200,2,FALSE),$P2=VLOOKUP($C2&""&Q$1,Sheet3!$C$5:$E$1200,2,FALSE),$Q2=VLOOKUP($C2&""&R$1,Sheet3!$C$5:$E$1200,2,FALSE),$R2=VLOOKUP($C2&""&S$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2>=VLOOKUP($C2&""&T$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2=VLOOKUP($C2&""&U$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2>=VLOOKUP($C2&""&V$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2=VLOOKUP($C2&""&W$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2>=VLOOKUP($C2&""&X$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,2,FALSE),$Z2<=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,3,FALSE))), "", 1)
    It is working normally but it is very long, Is their any way to make it easier.

  60. ENITY says:

    Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of L1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
    Thanks.

  61. Deb says:

    Hi I need to do a calculation total. I have a column called Quote/Won with one word in each row. I have a Labour £ column. I need to create a total of all labour revenue won. So if any figure in work labour column is won, the revenue figure shows in the total field. (single combined total field)
    eg.
    Total Labour revenue won £ 200
    Won/Quoted Column---- Work Labour £ Column
    Won £100
    Quoted £150
    Won £100

  62. jayabal mani says:

    Hi all,
    How to get different statement in one cell based on the different date ,
    For example:
    I have 5 document received date and 6 document sent date.
    from that once document sent from my side i need statement automatically "Sent"
    from my side document is pending statement should come " Pending"
    and if i entered any date in completed column statement should come " completed"

    • Hello Jaybal!
      I’m sorry but your task is not entirely clear to me. Excel cannot automatically indicate the status of a document. How does he know that you sent the document? It’ll help me understand it better and find a solution for you. Thank you.

      • jayabal mani says:

        I will receive document from vendor for review and once documents are okay i will approve that doc, if document rejected they will resubmit.
        In my tracker Approved mean i will enter code "A"
        if document reject i will enter code "C"...
        These sequence will happen more than 7 to 8 times.
        My requirement is Once i entered in C / A code i need result Approved /rejected in any one cell
        Exampple:
        (I need status automatically)
        First time doc received A2= C Status "Rejected"
        2nd time doc received C2= C Status "rejected"
        3rd time doc received E2=A Status " Approved"
        like this i have to check based on the code

  63. Shane says:

    I have two columns of data with the number set at 2 decimal places. column A for example in Cell A2 could read '10.2' and Cell B2 could read '11.3'. I want to be able to have cell C3 to state 'out of tolerance' if the range is outside of 10%. Is that possible?

  64. ENITY says:

    Thank you sir for your attention, if B1"", C1"", & D1"", it should give me the value of both K1, L1 & M1 & so on.

    "Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of M1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
    Thanks.

    • Hello Enity!
      Unfortunately, you did not give me detailed explanations. What formula did you use? Are you checking the condition in 15 cells at the same time? In which cell do you want to write the result? What should this result look like? How is the sum of the values of K1, L1 & M1 and so on? What are 15 arguments if there are only 10 columns between columns B and K? I can assume the following formula:

      =IF(SUMPRODUCT(--(B1=""),--(C1=""), --(D1=""),--(E1=""),--(F1=""),--(G1=""), --(H1=""),--(I1=""),--(J1=""))=1, K1,B1)

      But this is just a guess. And I'm not a telepath.

  65. jESSICA says:

    if AH1=Conus and D1=12, then 17
    if AH1=Conus and D1<12, then 15
    if AH1=Oconus, then 28

  66. Paul Schoon says:

    Hi
    I am using a history database and would like to make it a little more efficient. Its purpose is to code surnames using four characters but when there are fewer than four characters, to fill in any gaps with a full stop; for example KIM would become KIM. If I have a column of surnames, is there a formula that would allow me to identify those with fewer than four code letters and which would add the full stop for me. The current process requires me to check each entry and for a large database it takes an age.

    • Hello Pail!
      If I understand your task correctly, please try the following formula:

      =IF(LEN(TRIM(A1)) < 4,"less than 4",A1)

      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.

  67. ry says:

    hi,
    my requirement is, I have 3 columns. If column 1 value meets my requirement and the columen 2 text is "x", extract column 2 text. Else no. could you please help how I can write formula?

  68. rohit says:

    In same sheet read the 3 column V to x and match the condition of 25> cases in AC column and return the gift he qualified in AE column.

  69. Diane says:

    I am very new to excel but I have a very detailed comparison I do manually on an excel sheet currently and I think you maybe able to help me.

    I need to compare three columns and depending the message in those columns I need an out come.

    Data:
    A1 will contain update needed or blank
    B2 will contain coordinator needed or blank
    C2 will contain licensed or blank

    Anytime column C2 equals Not Licensed no matter what any of the other columns say then I need column D2 to say Ineligible

    If C2 is blank and b2 is coordinator needed and A1 is update needed the d2 needs to say coordinator and update needed

    If c2 is blank and b2 is blank but A1 is update needed then I need D2 to say Eligible update

    If all are blank then I need D2 to say No Action Needed

    Thanks for any help !

    • Hello Diane!
      If I understand your task correctly, please try the following formula:
      =IF(C2="Not Licensed","Ineligible", IF(AND(C2="",B2="coordinator needed",A1="update needed"),"coordinator and update needed", IF(AND(C2="",B2="",A1="update needed"),"Eligible update", IF(AND(C2="",B2="",A1=""),"No Action Needed", "" ) ) ))
      Hope this is what you need.

  70. Maria says:

    Hello, I need your help. I have a formula that is trying to figure out over and under numbers to meet goals.
    =IF($B$4<$C$1,"")
    actual B4=20 and C1=15(goal)
    If the number on B4 is less than 15, I have it giving us a blank, which works fine. But I cant figure out how to add another IF formula that will tell it to give me the number we are over our goal by. In this case, total is 20, goal is 15, answer should be 5. So how do I add an if to my formula?
    =IF($B$415, XXX)

  71. Sudipto says:

    Sir i want if ((1 to 2 = 2 , 2 to 4 = 3, 5 to 9 = 5 ))how to make that on if logic in single cell if any argument Parameter in between 1 to 2 must showing 2 , argument Parameter in between 3 to 5 = 3

  72. Lisa says:

    Hi I need a formula to state this
    In there will be Yes or No. If its Yes then use cell A1 if its No then use cell A2

  73. ivo baren says:

    =IF(O23="Inactive",0,IF(OR(L23=$L$3,L23=$L$4)=TRUE,$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))

    The last if functions works when on its own, but when added to the previous functions it does not work. I do not see where the issue is: only adding one extra agruement to the formula to say if additional something is "Yes" then please add 150 to the amount.

  74. Frank M. says:

    I am trying to figure out how to return one value in a cell by evaluating 6 other cells. For instance, if cell C2 has a valid value, return C2. If "NA" I need it to look at D2 and do the same evaluation and return the valid value if not "NA". So, column c2 = CAR1, columns d2 through h2 have NA. I want column B2 to say CAR1. If column d2 = CAR2 and column c2, e2 through h2 have NA. I want column b2 to say CAR2. I am guessing I need a string but cannot figure it out. Thanks!

  75. Jesse says:

    I am looking for help. My worksheet tracks work as well as issues. I need a formula for conditional formatting that will allow for a visual quick identification. This is what I think the formula should look like but I get an error. =IF(G22="Y","CORROSION",""(AND(H22="X","CLEANED"))); or =IF(G22="Y","CORROSION","",IF(G22="Y"(AND(H22="X"),"CLEANED"))
    The first part of the Formula works it is trying to add the second half that causes the error. Anyone have a suggestion?

    • Hello Jesse!
      Your formula contains errors. 1. You cannot use the = sign inside a formula. 2. The conditions AND and OR are incorrectly described. You have not explained how the formula should work, so I can’t fix it. To do it yourself, read the instructions Excel IF statement with multiple AND/OR conditions, nested IF formulas.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • Jesse Mahan says:

        On my spread sheet I have criteria that represent if an item is corroded or not, block 1 "item", block 2 is "inspected", block 3 "corroded", block 4 'cleaned/primed", and block 5 "notes". If it is corroded I put a "Y" in the corroded block. In my note block I have a formula, =IF(G4="Y","CORROSION",""), if this block has a "Y" it will show the word "CORROSION" this works perfectly. What I need to do is add to the formula that considers the corroded block with a "Y" and the clean primer block with a "X" to return with the word "REPAIRED". Is there a way to do this? I need this as a quick reference so at a visual glance the item can be easily identified as repaired.

  76. Mona says:

    Please help me correct these formulas
    =(F32); IF(F16:F21="0",) - H47 = F32 but if cells F16:F21 = 0 then H47 Should = 0%
    Also I require a formular If C11="good",15; IF C11="Satisfactory",9; IF C11="Poor",0; IF C11="N/A",N/A)
    Thank You

  77. Lisa says:

    If column A is the equally weighted value of a score and column B is the score (0-5 or N/A), and a score of N/A is entered on a row, I want the weight to be removed from the row and equally redistributed across all scored rows, thereby increasing the weights in column A.

    Is this possible?
    Said another way, if all rows had a numeric score, then all weights would be 3%, for example. However, if there were several rows that got an N/A, for each N/A, the 3% value of the weight would be equally redistributed across column A to show what the final, equally weighted values would be.
    Thanks for your help! I hope this question makes sense!

  78. Rachel says:

    I need a formula that looks at a date range on 1 sheet but returns the value on a different sheet, looking at values on the original sheet but only IF a different column on the original sheet shows certain TEXT.
    I'm doing the formula on a sheet named 'April!'
    Data is on 'Ian!' sheet
    So column B6:B500 on Ian! will have the date
    Column J6:J500 has the income but it is dependant on the value in column C whether it shows as 'New' or 'Renewal'
    Please help, thank you

    • Rachel says:

      I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!

      =SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))

    • Hello Rachel!
      I’m sorry but your task is not entirely clear to me. I need more details to help you. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Write down what specific criteria must be met so that the value of sheet 2 is written to sheet1. Thank you.

  79. Surya says:

    Hi All,

    I need an excel formula to TEXTJOIN of multipel cells with a matching condition of two column vaues. For example.

    Column 1 has Managername Manager1, Manager2, Manager3, Manager4, Manager5
    Column 2 has Employees EMP1, EMP2, EMP3, EMP4,EMP5, EMP6 etc
    column 3 has OnBench, InProject,InProject,InProject,OnBench

    I need to list this into other sheet of the same workbook, with the below condition.
    List of all employees, with join text separated by comma into single cell against that Manager name who are OnBench.

    Can anybody help with this?

    Thanks a lot in advance.

  80. Mike Merrick says:

    Ok so I'm trying to compile a file that shows if a particular person has been called during the week.
    Each Day has a sheet that populates a persons detail from a unique identifier (Tech Id) and can be marked as YES or NO as to whether they have been called that day.
    Tech ID, Name, Called?
    I then have a weekly review sheet that i need to populate. same column lay out
    So if the Tech ID in A2=FS999 and the corresponding tech ID = YES on any of the daily sheets then it should fill in on the weekly tracker. I Just cant for the life of me get my head around the complexity of so many IF's and OR's .
    Can Anyone Advise?

    • Hello Mike!
      If I understand your task correctly, the following formula should work for you:
      1. To calculate data for several conditions on several sheets, you can use the formula

      =SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"),"FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes"))

      2. Use this formula in your condition

      =IF(SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"), "FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes")) > 0,"Yes","No")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  81. Pravin says:

    Kindly help me to get percentage in multiple amount different percentage ratio
    For example:
    60000 to 100000 = 5.5%
    100001 to 150000 = 8%
    150001 to 200000 = 12%
    200001 and above 15%
    How to calculate if i have many column in different amount and need to get percentage as per above how to use formula, kindly help urgently. Appreciate any can help me in this regards urgently.
    Thank you
    Pravin Rupapara

    • Hello Pravin!
      You may use the FREQUENCY function to calculate the number of values in the particular range:

      =FREQUENCY(B2:B100,F2:F5)

      Where B2:B100 - your data range and F2:F5 - the cells that contain your lower bounds (i.e. 60000, 100000, 150000, 200000)

      Select the range of empty cells (G2:G6, for instance) that has one cell more than F2:F5. Paste =FREQUENCY(B2:B100,F2:F5) in the formula bar and apply it as an array function by pressing Ctrl+Shift+Enter. Then divide every resulting numbers by the total of values (the formula for the last one would be =COUNTA(B2:B100)

      Enter these formulas into H2:H6 and you'll get the percentage you need.

  82. SHRIKRISHNA IKKAR says:

    I want the reason for result of IF formula. That if result came was PASS then why? Is it due to column 2 or 3.

  83. SHRIKRISHNA IKKAR says:

    Formula is= if(and(T3>3,U3>=9,v3<2,w3="NO",X<25),"eligible","ineligible")

    If result come ineligible then I want to know that due to which column this result came.
    Please guide
    Regards

  84. Luisa Pilapil says:

    Formula is =IF(OR(AND(G16="D",Q16>300000),AND(G16="V",Q16>300000),AND(G16="I",Q16>1000000),AND(G16="ORC",$L$5>600000)),"Y","")
    It's returning the first two conditions "D" & "V" correctly but not the last two conditions. What am I missing? Please help. Thank you.

    • Hello Luisa!
      I copied the formula from a blog in Excel and made sure that it works. Write in detail what results you want to get. 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.

      • Luisa Pilapil says:

        Thank you for your reply.
        What I'm trying to accomplish has several conditions, so if:
        Service column has "D" and total of more than 300K the result should say "Y" for yes.
        OR
        Service column has "I" and total of more than 300K the result should say "Y" for yes.
        OR
        Service column has V and total of more than 1M the result should say "Y" for yes.
        OR
        "H" total column is more than 600K the result should say "Y" for yes.
        I hope this is clear explanation. Thank you so much for your help.

  85. ENITY says:

    Gud day Sir, please I have a large document that is up to 20 pages & maybe more. I want the heading which is in row 1,2,3,4,5 to show at the top of each page, I used the print title option and it worked perfectly. Now I want the bottom note which is up to eight rows to show at the bottom of each page. How can I make that work?
    Please I need help on that.
    Thanks.

  86. Tim says:

    How do I extend this formula to retrun "Agency" if employer column does not = J Smith Ltd.
    Employer Source Fomula
    J Smith Ltd Direct =IF(C4="J Smith LTD","Direct")
    ABC Recruit Agency
    XYZ Recruit Agency

  87. AB says:

    WHAT SHOULD BE FORMULAE CONSIDERING BELOW CONDITIONS:
    - Cell is greater than equal to 5 = P-HOME
    - Cell is less than 5 = HLWP
    - Cell is "0" OR "NULL" OR "#N/A" = LWP

  88. Anoop Mishra says:

    HI,
    Please suggest how automate in sheet B
    Sheet A.
    01-05-2020 02-05-2020 03-05-2020
    Branch Cash Transfer Cash Transfer Cash Transfer
    A 200 90 500 152 5165 2132
    B 500 100 700 515 6516 98
    C 700 930 785 515 212 19
    Sheet B.
    Date 02-05-2020
    Branch Cash Transfer
    A ? ?
    B ? ?
    C ? ?

    • Hello Anoop!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  89. Sarah Marsh says:

    I am trying to work out an IF formula for the following. I have a spreadsheet that details stock and whether it is in date, out of date or expiring soon. I need the cell to show whether an item expires one month from today, is in date or out of date. I am struggling to work out the IF formula - this is what I have come up with so far, but am unable to get any further. Any help would be greatly appreciated by this newbee to Excel

    =IF(E3<TODAY()*AND(-365-335),"Exp 1 mth",IF(E3<TODAY()-365,"Out of date","In date"))

  90. Vincent Rene Namoc says:

    if the 1st row is greater than the other row then the answer is 1
    if the 1st row is lesser than the other row then the answer is 0
    if the 1st row is 0 than the other row is 0 then the answer is 1
    if the 1st row is equivalent to the other row then the answer is 0

  91. Pascale Faddoul says:

    Hello,
    I need the following formula:
    if in column A I have the word "Revision", I need to put it in column B but not at same level (-1 level).
    Exemple: if A4=Revision: 2.1--> put all content in B3

    Thank you very much

  92. Matt says:

    Hi,

    I order supplies for my company and have created a sheet that has 13 tabs, one for each 4 week period and a Summary tab at the end. On each tab I have:

    ItemOrdered Qty Item# Supplier Cost$ DateOrdered DateReceived QTY/CS

    I need to create a formula where IF a certain item is ordered (say triggered by Item#), THEN, the value in the Qty column is tallied as a sum on my Summary tab. Does that make sense?

    Thanks in advance!
    Matt

  93. Alex G says:

    Hi,
    Great article!
    In my example below, is it possible to add to the formula so that results in column 'C' would also take into consideration the value in column 'B' and (subtract 10 if 'B'=1) or (subtract 20 if 'B'=2)
    Hope this makes sense, thank you!
    A B C
    1 35 1 150
    2 33 150
    3 40 2 200
    4 41 200

    =ifs(and(A1>=30,A1=38,A1<42),200)

    • Hello!
      Your formula does not work. I did not understand what result you want to get. If there are already numbers in column C, then they cannot be changed by the Excel formula. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

      • Alex G says:

        thanks for the response.
        I didn't realize I pasted wrong the formula
        here is the correct one I use:
        =ifs(and(A1>=30,A1=38,A1=" and "<") from Column 'A' which are size values. Now I want to add additional criteria (Column 'B') which is a quality grade (A,B,C or can be switched to numbers 1,2,3) so the calculated value (price) in column 'C' would be less by 10 if 'B=1', less by 20 if 'B=2', less by 30 if 'B=3', if 'B=blank' leave it as it is.
        Hope this makes sense and thank you for all your help!
        Alex

        • Alex G says:

          ...here it is again, for some reason when I publish the comment it changes the formula pasted.
          I'll try again with no "=" sign
          ifs(and(A1>=30,A1=38,A1<42),200)

        • Alex G says:

          ifs(and(A1>=30,A1=38,A1<42),200)

          • Alex G says:

            ...Never mind, the forum messages will change the formula every time I submit it.

          • Alex G. says:

            ifs(and(A1>=30,A1<38)=38,A1<42),200)

          • Alex G says:

            I'll put it in words:
            if "A1" is "greater than or equal to" 30 and "less" than 38, than 150, if "A1" is "greater than or equal to" 38 and "less" than 42, than 200
            hope this makes sense as it won't let me paste the formula in the chat correctly.

            • Hello Alex!
              If I understand your task correctly, the following formula should work for you:

              =IF(B1<>"", (IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,""))) - (B1*10), IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,"")))

              I hope this will help, otherwise please do not hesitate to contact me anytime.

  94. Phatima says:

    Hello, how do write a formula for this? If total is between .01%-.99%, output should be $40, if between 1% and 1.99% - output should be $210. I tried IF and also IF(AND with , but I can't get it to work...help please. Thank you so much!
    0.01% ---> 0.99% $40.00
    1% ---> 1.99% 210.00
    2% ---> 2.99% 240.00
    3% ---> 3.99% 270.00
    4% ---> 4.99% 300.00
    5% ---> 5.99% 330.00
    6% ---> 6.99% 370.00
    7% ---> 7.99% 410.00
    8% ---> 8.99% 450.00
    9% ---> 9.99% 490.00
    10% ---> 10.99% 540.00
    11% ---> 11.99% 590.00
    12% ---> 12.99% 640.00
    13% ---> 13.99% 690.00
    14% ---> 14.99% 740.00

    • hello Phatima!
      Write your values in columns A, B, C. A1 - 0.01% B1 - 0.99% C1 - 40.00, etc. If the final value is written in F1, then the formula for determining the output will be as follows:

      =VLOOKUP(F1,A1:C15,3,1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  95. Jennifer says:

    Hello,
    I'm hoping to add a calculated field to my pivot table and need a little help.
    A B C calc field
    1 "A only"
    1 1 "A & B"
    1 "C only"
    1 "B only"
    1 1 1 "All 3"
    1 1 "B & C"
    1 1 "A & C"

  96. cindy says:

    I'm stuck, wonder if someone can help me:
    I need ranges-
    if cell value is =1500 and =3000 and =5000 "5000+"

    I know it's where I'm putting the () but I can't seem to get it right?

    • Hello Cindy!
      I’m sorry but your task is not entirely clear to me. I hope you have studied the recommendations in the above tutorial. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.

      • Cindy Decker says:

        sorry, it didn't type out correctly
        If h2 is less than 1500, then "<1500", if h2 is greater than or equal to 1500 AND less than 3000, then "1500-2999", if h2 is greather than or equal to 3000 AND less than 5000, then "3000-4999", if h2 is greater than 5000, then "5000+"

        • Hello Cindy!
          Please try the following formula:

          =IF(D2 < 1500,"<1500", IF(D2 < 3000,"1500-2999",IF(D2 < 5000,"3000-4999", "5000+" ) ) )

          The second way:
          Write your values in columns A, B, C. A1 - 0 B1 - 1499 C1 - <1500 A2 - 1500 B2 - 2999 C2 - 1500-2999, etc. If the final value is written in D1, then the formula for determining the output will be as follows:

          = VLOOKUP (D1, A1: C4,3,1)

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  97. Michelle says:

    Hi,
    I'm trying to write an "If" statement if 3 variables in different cells match up with each other then "Y" else "N"

    If [A2]=[C2]=[D2]THEN "Y" ELSE "N" ENDIF

  98. Mr.Priyank Khanna says:

    Hello,
    Just want to say thank you , you doing great job, lord shower grace on you.
    I was writing my problen but suddenly answer clicked, thank you again.
    Thank you so much!

  99. Keith Williams says:

    I am trying to compare a sales amount in a cell to a commission schedule and based on teh sales figure it will bring multiply the sales times the correct commission %. I know this is not correct but I am not sure where I am going wrong

    =IFOR((AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)

  100. Keith Williams says:

    updated but still not correct

    =IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard