Excel IF statement with multiple conditions

The tutorial shows how to create multiple IF statements in Excel with AND as well as OR logic. Also, you will learn how to use IF together with other Excel functions.

In the first part of our Excel IF tutorial, we looked at how to construct a simple IF statement with one condition for text, numbers, dates, blanks and non-blanks. For powerful data analysis, however, you may often need to evaluate multiple conditions at a time. The below formula examples will show you the most effective ways to do this.

How to use IF function with multiple conditions

In essence, there are two types of the IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of your IF formula, you should use one of these functions:

  • AND function - returns TRUE if all the conditions are met; FALSE otherwise.
  • OR function - returns TRUE if any single condition is met; FALSE otherwise.

To better illustrate the point, let's investigate some real-life formulas examples.

Excel IF statement with multiple conditions (AND logic)

The generic formula of Excel IF with two or more conditions is this:

IF(AND(condition1, condition2, …), value_if_true, value_if_false)

Translated into a human language, the formula says: If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false.

Suppose you have a table listing the scores of two tests in columns B and C. To pass the final exam, a student must have both scores greater than 50.

For the logical test, you use the following AND statement: AND(B2>50, C2>50)

If both conditions are true, the formula will return "Pass"; if any condition is false - "Fail".

=IF(AND(B2>50, B2>50), "Pass", "Fail")

Easy, isn't it? The screenshot below proves that our Excel IF /AND formula works right: Excel IF statement with multiple AND conditions

In a similar manner, you can use the Excel IF function with multiple text conditions.

For instance, to output "Good" if both B2 and C2 are greater than 50, "Bad" otherwise, the formula is:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad") Excel IF function with multiple text conditions

Important note! The AND function checks all the conditions, even if the already tested one(s) evaluated 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 statement may result in an error because of this specificity. For example, the below formula would return #DIV/0! ("divide by zero" error) if cell A2 is equal to 0:

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

The avoid this, you should use a nested IF function:

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

For more information, please see IF AND formula in Excel.

Excel IF function with multiple conditions (OR logic)

To do one thing if any condition is met, otherwise do something else, use this combination of the IF and OR functions:

IF(OR(condition1, condition2, …), value_if_true, value_if_false)

The difference from the IF / AND formula discussed above is that Excel returns TRUE if any of the specified conditions is true.

So, if in the previous formula, we use OR instead of AND:

=IF(OR(B2>50, B2>50), "Pass", "Fail")

Then anyone who has more than 50 points in either exam will get "Pass" in column D. With such conditions, our students have a better chance to pass the final exam (Yvette being particularly unlucky failing by just 1 point :) Excel IF function with multiple OR conditions

Tip. In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i.e. a cell can be "this" or "that"), then you can build a more compact formula using an array constant.

For example, to mark a sale as "closed" if cell B2 is either "delivered" or "paid", the formula is:

=IF(OR(B2={"delivered", "paid"}), "Closed", "")

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

IF with multiple AND & OR statements

If your task requires evaluating several sets of multiple conditions, you will have to utilize both AND & OR functions at a time.

In our sample table, suppose you have the following criteria for checking the exam results:

  • Condition 1: exam1>50 and exam2>50
  • Condition 2: exam1>40 and exam2>60

If either of the conditions is met, the final exam is deemed passed.

At first sight, the formula seems a little tricky, but in fact it is not! You just express each of the above conditions as an AND statement and nest them in the OR function (since it's not necessary to meet both conditions, either will suffice):

OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Then, use the OR function for the logical test of IF and supply the desired value_if_true and value_if_false values. As the result, you get the following IF formula with multiple AND / OR conditions:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

The screenshot below indicates that we've done the formula right: IF with multiple AND & OR statements

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

  • In Excel 2007 and higher, you have no more than 255 arguments, and the total length of the IF formula does not exceed 8,192 characters.
  • In Excel 2003 and lower, there are no more than 30 arguments, and the total length of your IF formula does not exceed 1,024 characters.

Nested IF statement to check multiple logical tests

If you want to evaluate multiple logical tests within a single formula, then you can nest several functions one into another. Such functions are called nested IF functions. They prove particularly useful when you wish to return different values depending on the logical tests' results.

Here's a typical example: suppose you want to qualify the students' achievements as "Good", "Satisfactory" and "Poor" based on the following scores:

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

Before writing a formula, consider the order of functions you are going to nest. Excel will evaluate the logical tests in the order they appear in the formula. Once a condition evaluates to TRUE, the subsequent conditions are not tested, meaning the formula stops after the first TRUE result.

In our case, the functions are arranged from largest to smallest:

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

Naturally, you can nest more functions if needed (up to 64 in modern versions). Nested IF statement in Excel

For more information, please see How to use multiple nested IF statements in Excel.

Excel IF array formula with multiple conditions

Another way to get an Excel IF to test multiple conditions is by using an array formula.

To evaluate conditions with the AND logic, use the asterisk:

IF(condition1) * (condition2) * …, value_if_true, value_if_false)

To test conditions with the OR logic, use the plus sign:

IF(condition1) + (condition2) + …, value_if_true, value_if_false)

To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

For example, to get "Pass" if both B2 and C2 are greater than 50, the formula is:

=IF((B2>50) * (C2>50), "Pass", "Fail") IF array formula with multiple AND conditions

In my Excel 365, a normal formula works just fine (as you can see in the screenshots above). In Excel 2019 and lower, remember to make it an array formula by using the Ctrl + Shift + Enter shortcut.

To evaluate multiple conditions with the OR logic, the formula is:

=IF((B2>50) + (C2>50), "Pass", "Fail") IF array formula with multiple OR conditions

Using IF together with other functions

This section explains how to use IF in combination with other Excel functions and what benefits this gives to you.

Example 1. If #N/A error in VLOOKUP

When VLOOKUP or other lookup function cannot find something, it returns a #N/A error. To make your tables look nicer, you can return zero, blank, or specific text if #N/A. For this, use this generic formula:

IF(ISNA(VLOOKUP(…)), value_if_na, VLOOKUP(…))

For example:

If #N/A return 0:

If the lookup value in E1 is not found, the formula returns zero.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return blank:

If the lookup value is not found, the formula returns nothing (an empty string).

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return certain text:

If the lookup value is not found, the formula returns specific text.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE)) If #N/A error in VLOOKUP

For more formula examples, please see VLOOKUP with IF statement in Excel.

Example 2. IF with SUM, AVERAGE, MIN and MAX functions

To sum cell values based on certain criteria, Excel provides the SUMIF and SUMIFS functions.

In some situations, your business logic may require including the SUM function in the logical test of IF. For example, to return different text labels depending on the sum of the values in B2 and C2, the formula is:

=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))

If the sum is greater than 130, the result is "good"; if greater than 110 – "satisfactory', if 110 or lower – "poor". Using the IF function with SUM

In a similar fashion, you can embed the AVERAGE function in the logical test of IF and return different labels based on the average score:

=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))

Assuming the total score is in column D, you can identify the highest and lowest values with the help of the MAX and MIN functions:

=IF(D2=MAX($D$2:$D$10), "Best result", "")

=IF(D2=MAX($D$2:$D$10), "Best result", "")

To have both labels in one column, nest the above functions one into another:

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

Likewise, you can use IF together with your custom functions. For example, you can combine it with GetCellColor or GetCellFontColor to return different results based on a cell color.

In addition, Excel provides a number of functions to calculate data based on conditions. For detailed formula examples, please check out the following tutorials:

  • COUNTIF - count cells that meet a condition
  • COUNTIFS - count cells with multiple criteria
  • SUMIF - conditionally sum cells
  • SUMIFS - sum cells with multiple criteria

Example 3. IF with ISNUMBER, ISTEXT and ISBLANK

To identify text, numbers and blank cells, Microsoft Excel provides special functions such as ISTEXT, ISNUMBER and ISBLANK. By placing them in the logical tests of three nested IF statements, you can identify all different data types in one go:

=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", ""))) IF with ISNUMBER, ISTEXT and ISBLANK

Example 4. IF and CONCATENATE

To output the result of IF and some text into one cell, use the CONCATENATE or CONCAT (in Excel 2016 - 365) and IF functions together. For example:

=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

=CONCAT("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

Looking at the screenshot below, you'll hardly need any explanation of what the formula does: Using IF and CONCATENATE

IF ISERROR / ISNA formula in Excel

The modern versions of Excel have special functions to trap errors and replace them with another calculation or predefined value - IFERROR (in Excel 2007 and later) and IFNA (in Excel 2013 and later). 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.

For example, to replace the "divide by zero" error (#DIV/0!) with your custom text, you can use the following formula:

=IF(ISERROR(A2/B2), "N/A", A2/B2) Using IF together with ISERROR

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!

Practice workbook for download

Excel IF multiple criteria - examples (.xlsx file)

Table of contents

4494 comments

  1. I need a fomumala to calculate SLA "met" or "not met"
    Conditions -

    Priority <10 mins for 1, <15 mins for 2 and <60 mins for 3 & 4

    time values is in one column and priority value in another.

  2. I was wondering if anyone could help...
    I'd like to have a formula that for example:
    -If cells A1:Z1 ALL have numeric values between 121 and 124 the result should read "Hold Time"
    -If not, the result should be blank/""

    I think it's possible with IF and many AND functions however I thought there might be a quicker way to select a whole range of cells and check for these criteria of >121 & <124?

    Any help would be great, Thanks

  3. I was wondering if anyone could help...
    I'd like to have a formula that for example:
    -If cells A1:Z1 ALL have numeric values >121 but <124 the result should read "Hold Time"
    -If not, the result should be blank/""

    I think it's possible with IF and many AND functions however I thought there might be a quicker way to select a whole range of cells and check for these criteria?

    Any help would be great, Thanks

  4. I need to make slots of different overdue days for a file of amounts. e.g the slots are "1-30", "31-60", "61-90" and so on.
    I have the overdue number of days in column "V2" i.e 278 days. The amount is in column "G2" i.e 900.00.
    How do I use the IF/AND function?
    I tried =IF((AND(V2>=0,V2<=30)),"=G2","0")but this is not quite working.

  5. I want to copy same value to the another cell with using formula, not by copy or paste. like if I write in cell A1 "125" then with using of formula I want the value of 125 automatically written in cell c1 which I choose of my destination cell.

  6. Can anyone help? I'm struggling and need a formula to do the following:

    If E5 = "Apples" and F5 = "Oranges", I need another cell to display text saying "Apples and Oranges"

    Then, if the above is true, I need cell I6 to display "1" if D5 shows "One", J6 to display "2" if D5 shows "Two" and K6 to display "3" if D5 shows "Three".

    • Try this to combine
      =CONCATENATE(G3, H3)

  7. Thanks so useful

  8. Party Amount CHQ Date Cleared on Master CHQ Date
    A 100 08-08-17 08-08-17 31-07-17
    B 200 09-08-17 31-07-17
    C 300 05-09-17 05-09-17
    D 500 05-08-17 05-08-17 08-08-17

    i want CHQ Date to be "today" if the master date is lesser than today and if add cleared on that date to be taken as CHQ Date.
    I am using two different If command to do that. How we can use a single if command.Please Help

  9. If I have specification of Min = 34.1 and Max = 35.1. And calculated value should falls in between these two number of Min and Max. Once they are falls in between of min and max, it should display as "OK" and 2nd condition is if the calculated value is not falling in between of min and max the result should display as "NG".

    How is the formula should be!

    Thanks in advance for your reply.

  10. Hi
    I am trying to include multiple if functions to then produce a specific set of letters. example below

    Column A = either has ubk or ugl stated
    Column B = debit figure or credit figure (numbers + or -)
    In column C I am basically trying to get excel to look as column A and if it states ubk AND if its a debit in column B I need it to state br column C, if its a debit then bp AND also if it states ugl in column A and a debit in column C then column C needs to state spo, if its a credit in column C.

    See below column A and B are the data I have, column C is currently input manually but wondered if I could use a formula that looked at both columns to populate.

    Column A Column B Column C (desired)
    ubk -100 br
    ubk +100 bp
    ugl -100 spo
    ugl +100 sr

    Thanks
    Amy

  11. Hi there. Need help please. What should be the formula for this:

    Flights below 1 Hour: $8,000 per hour
    Flights between 1 and 3 Hours: $7,000
    Flights over 3 Hours: $6,700 per hour

    Thanks in advance.

    • Hello, Zarina.
      Thank you for contacting us.

      Let's suppose that the flight duration is in A1. You put the following formula into B1 in order to get the result:

      =IF(A1<1, "$8.000 per hour", IF(A1<3, "$7.000 per hour", IF(A1>=3, "$6.700 per hour")))

      I hope this helps. Please let me know if you have any other questions. I am happy to help!

  12. Kindly solve my problem.

    I need to put S, A, B, C, D in the output cell. kindly send me the formulla.

    S(Excellent) = 95 and over

    A(Good) = 90 to 94

    B(Normal) = 80 to 89

    C(Bad) = 70 to 79

    D(Worst) = 69 or less

    kindly send me ASAP

    Yaser Zeb

    • Hello. Thank you for your question.

      Let's suppose that the number is in A1. You put the following formula into B1 in order to get the result:

      =IF(A1 >= 95, "S", IF(A1 >= 90, "A", IF(A1 >= 80, "B", IF(A1 >= 70, "C", IF(A1 <= 69, "D")))))

      I hope this helps. Please let me know if you have any other questions or difficulties.

  13. Hi,

    I am trying to come up with a formula that will do this:

    If the text in C3 can be found in a range if cells ( =Sheet1!D2:D12 ) then it will say "PE" in the current cell and "2.2" in the cell to the right of current cell.

    Any help would be greatly appreciated!

    • Hi, Pearl. Thank you for your question.

      Please try to apply the formula below to check the range for the value in C3:
      =IFERROR(IF(MATCH(C3, D2:D12,0), "PE"), " ")

      Let's suppose that this formula is in A1. Then place the following formula in A2:
      =IF(A15="PE", 2.2, " ")

      If the text from C3 can't be found in the range D2:D12, the cells with the formulas will stay empty.

  14. Hi all, can you help me with this?

    I need a formula that results to a single letter provided these criteria:
    x>-15 and y-15 and y=-10 to 10 : b
    x>-15 and y>-10 : c
    x=-15 to 15 and y-10 : f
    x<15 and y<10 : g
    x<15 and y=-10 to 10 : h
    x-10 : i

    Thanks for those who would help

  15. "What I need help on is trying to figure out the ""IF"" formula with the inclosed ""IFERROR"".
    1. In Column A, there will be three choices, Apple, Orange and Banana. These could be random.
    2. I am looking for a formula that will look at Column A and once it sees ""Apple"", will then look to Column C and D.
    3. Based on what it finds in those columns, it will go to the table and find the correct number to place into column B "

    Table1 Table2 Table3
    Col A Col B Col C Col D Opt 1 Opt 2 Opt 3 Opt 1 Opt 2 Opt 3 Opt 1 Opt 2 Opt 3
    Apple Opt 1 Opt 1 Opt 1 10 11 12 Opt 1 10 11 12 Opt 1 10 11 12
    Orange Opt 1 Opt 2 Opt 2 11 12 10 Opt 2 11 12 10 Opt 2 11 12 10
    Banana Opt 1 Opt 3 Opt 3 12 10 11 Opt 3 12 10 11 Opt 3 12 10 11
    Opt 2 Opt 1
    Opt 2 Opt 2
    Opt 2 Opt 3
    Opt 3 Opt 1
    Opt 3 Opt 2
    Opt 3 Opt 3
    =IF(E4="Apple",IFERROR(INDEX(Table1numbers,MATCH(B4,Table1A,0),MATCH(C4,Table1B,0)), "No Match")," "),IF(and(E4="Orange",IFERROR(INDEX(Table2numbers,MATCH(B4,Table2A,0),MATCH(C4,Table2B,0)), "No Match")," "),IF(and(E4="Banana",IFERROR(INDEX(Table3Numbers,MATCH(B4,Table3A,0),MATCH(C4,Table3B,0)), "No Match")," ))

    "Ranges are:
    Table1Numbers: K4:M6
    Table1A: J4:J6
    Table1B: K3:M3
    Table 2 & 3 are similar"

  16. IF B1= VAT then D1=C1*15% OR iF B1= NON-VAT then D1=Nill
    Please some one help me to organize the function.

    • Thanks a lot

      • Hello. Thank you for contacting us.

        Please try the following formula to solve your task:
        =IF(B1="VAT", C1*0.15, IF(B1="NON-VAT", "Nill"))

  17. I'm looking at the balance for depreciation expenses on thousands of line items of assets but I need to get it to zero so it doesn't go over the net book value amount.
    Example would be an asset is valued at $71 and is depreciated at $7 per month. On the 11th month I would need only $1 to be an expense since that would be a total of $71 ($7 x 10 months + $1 on the final month to get to $71 total). After the 11th month I would need to have a zero in the cells for the balance of the months thru 60 months. How do I get it to show $7 per month in the first 10 columns, then $1 in the 11th column, and then zero dollars for the remaining 49 columns? I'm trying to do one formula for all the rows of equipment which all have different values and number of months to get to a zero balance. Can anyone help me on this one? Thanks much!!

  18. Ability Ability 1 Ability 2 Summary of abilities applicable
    Information 1.1. 1.2 1.3 1.4 1.5 Higest score Obtained l %age 1.1. 1.2 1.3 1.4 Higest score Obtained l %age Ability 1 Ability 2
    Score (from 0 to 4) in case this ability is not required fill Nill 4 0 1 4 4 20 13 65% 4 0 1 4 16 9 45% 65% 45%
    Total (No. of abilities * highest score)
    in above example how i put the Not applicable or NA for the ability not applicable on the person. if any ability not appicable need to fill NA in 1.1, 1.2, 1.3 and all and it show in %age as NA but at the same time if i fill 0 to 4 numercal vale it pick up that vul;e and reflect in the % age .... useing one function or formula.

  19. In case my question is not clear, how to give range as a condition for example 3-12, as a condition into the condition.

  20. My formula is not working, please let me know what is wrong in this?

  21. IF(OR(AND(C13="Sole Source",D13>=0),AND(C13="Single Source",D13>12),AND(C13="Dual Source",D13>12),AND(C13="Multi-Source",D13>12)),$A$2,IF(OR(AND(C13="Single Source",D133,D133,D13<12)),$A$4,IF(OR(AND(C13="Dual Source",D13<=2),AND(C13="Multi-Source",D13=1),AND(C13="Multi-Source",D13=2)),$A$6,IF(AND(C13="Multi-Source",D13=0),$A$8,"Not Applicable"))))

  22. 0 12
    A 3 3 3 3
    B 2 2 2 3
    C 1 1 2 3
    D 0 1 2 3

    I have two different values as given above a,b,c,d and 0,12. How can I apply if(and formula for this?

  23. Hi,

    I need help with my formula. Basically M4 has 2 options (1 or 2) and I4 is the no. of service. so my argument is like this:

    if M4 = 1 and I4 is below 5 years of service, then choose 13%
    if M4 = 1 and I4 is more than 5 years of service, then choose 18%
    if M4 = 2, choose 13%

    i try the below formula but it doesn't work.

    if(M4=2,"13%", if(and(M4=1,I4=>5 years),"18%" & if(and(M4=1,I4=<5 years),"13%")))

    Please help

    thank you

    • Hi

      Thank you for your question.

      Please try to apply the formula below:

      =IF(M4=2, "13%", IF(AND(M4=1, I4<5), "13%", IF(AND(M4=1, I4>=5), "18%")))

      I hope it helps. Please let me know if you have any other questions or difficulties.

  24. if the date in AB is 61-90 days older than today then value of AH in AJ, if date greater than 91 days than value of AH in AK. if there is no date in AB than NIL in AJ and AK

  25. =IF(AND(D2<=40,E2=50,E2>=500000),D2*50%,"Discount Given")

    hi,
    I have create discount sheet but i don't why error given this sheet
    please help me

  26. in vlookup formula i am getting an error, i want to use another formula in addition to vlookupiferror i.e if a particular col has a particular word for Example Disney or say Akari or say Baggage, then result will be delhi if Disney, if it is Akari then Maharashtra, if Baggage then delhi

    • Hi, Vinit,

      if you want to use IF, then you could try this formula (assuming that the column with the words is A):
      =IF(OR(COUNTIF(A:A,"Disney")>=1,COUNTIF(A:A,"Baggage")>=1),"delhi",IF(COUNTIF(A:A,"Akari")>=1,"Maharashtra",""))

      But keep in mind that it will stop searching for the words in the column as soon as one of the conditions is met.

  27. Can any one pls help me how can i use less than and greater than in once o=if logical cell.

    ActivationCT ActivationAT Total Act Delever INCENTIVE
    0 21 21 0 50 50 Not Eligible
    0 257 257 0 326 326 Not Eligible
    0 23 23 0 25 25 Not Eligible
    0 0 0 0 100 100 Not Eligible
    0 48 48 0 50 50 Not Eligible

  28. I have a formula like this: =IF(AND(E18="x",H18=1),(C18*D18)+C18,0)
    H18 also has its own formula: =IFS(Sheet1!$C$9=1,"1",Sheet1!$D$9=1,"2",Sheet1!$E$9=1,"3",TRUE,"")
    but the first formula just returns a "0" even if the result of the H18 formula is either 1, 2 or 3 and all other conditions are met.
    Any suggestions as to why?
    Thanks

  29. Hi, my condition is
    If the text in the Cell A1 is "High", then the value in Cell B1 should be 25
    If the text in the Cell A1 is "Medium", then the value in Cell B1 should be 50
    If the text in the Cell A1 is "Low", then the value in Cell B1 should be 75

    Not able to put down the formula for this.

    Thanks

  30. hi! i have a problem regarding formula for Time in and time-out display of late and early. same are Morning Overtime and night Overtime.
    E2(Time-in) = 8:30AM
    H2(Time-out)= 17:30PM
    I need to get the R2(Early-IN)and E2(Late)
    so that i can compute for the Overtime for Morning and Overtime Night in 1 person shift. Thank in advance.

    • Hello, Joevan,

      I'm afraid there's not enough data in your condition to help you with a formula.
      Are these time stamps that indicate when the working day starts and ends or they indicate when the employee comes to work? You need both of them, you see. Please, give us more details so we could help.
      Thanks

  31. Hii, I have attached a excel file with this mail as an example. In that mail I have desktop(PC) data with their specifications (like ram gb, hdd etc.). So I have to calculate total amount after deductions. For eg. if total price of one desktop is Rs 2100 & the ram is missing(Means No is written if it loses) from it , then we will reduce Rs 400 from Rs 2100. Same case if HDD is missing then we will reduce Rs 500 from it & it ges on. After all the deduction we will get final price.

    SN LOCATION RAM HDD M.B. DISPLAY Final Price
    1 Chennai No 320GB YES NO
    2 Chennai 4GB No YES NO
    3 Chennai 4GB 320GB YES OK
    4 Chennai 4GB 320GB YES OK
    5 Chennai NO NO YES NO
    6 Chennai NO 320GB YES NO
    7 Chennai NO NO YES NO
    8 Chennai 2GB NO YES NO
    9 Chennai 4GB 320GB YES OK
    10 Chennai NO NO YES NO
    11 Chennai 2GB 320GB YES OK
    12 Chennai 4GB 320GB YES OK
    13 Chennai NO NO YES NO
    14 Chennai 4GB NO YES NO
    15 Chennai 4GB 320GB YES OK
    16 Chennai 4GB 320GB YES NO

    Note : Total Price 2100
    If Ram is missing then we will deduct 400
    If HDD is missing then we will deduct 500
    If display is missing then we will deduct 800
    If M.b is missing then we will deduct 400

  32. I want to display week-wise report.
    Day(1 to 7)= Week 1
    Day(8 to 14)= Week 2
    Day(15 to 21)= Week 3
    Day(22 to 31)= Week 4

    Please suggest an IF formula, so that when I enter any of the above number, it should display the Week.

    • =If(day(1to7),"week 1",if(day(8to14),"week 2")) add the same for next weeks in the same formula

  33. Hi I need a formula for this multiple IF equation. =IF(A1,A2="Yes","2",1) using multiple cells doesn't work? Yes will only be in one of the cells when typed.

  34. I guess its same as mine-

    IF(OR(B3="BCN",B3="AMS",B3="LGW",B3="LTN"),20,IF(OR(B3="TLV",B3="TFS",B3="ALC",B3="AGP"),45,""))

  35. Hi all,

    I hope someone can help me with thw following, as I'd like to have this:

    if B3 is BCN or AMS or LGW or LTN then 20
    if B3 is TLV or TFS or ALC or AGP then 45

    Thanks!

    • Hi, Yanick,

      try this formula:
      =IF(OR(B3="BCN",B3="AMS",B3="LGW",B3="LTN"),20,IF(OR(B3="TLV",B3="TFS",B3="ALC",B3="AGP"),45,""))

    • Use vlookup

      Put this data in cells away from your existing data. In this solution it is in cells L15:M22

      bcn 20
      ams 20
      lgw 20
      ltn 20
      tlv 45
      tfs 45
      alc 45
      agp 45

      Then use this simple formula:
      =VLOOKUP(b3,L15:M22,2,FALSE)

  36. Score 70 80 100
    Target 100 100 100
    Ach% 70% 80% 100%
    Point IF(B3>=100%,"3",IF(B3>=80%,"2",IF(B3>=70%,"1",""))) IF(C3>=100%,"3",IF(C3>=80%,"2",IF(C3>=70%,"1",""))) IF(D3>=100%,"3",IF(D3>=80%,"2",IF(D3>=70%,"1",""))) sum(B4:D4)
    Unable to get sum

  37. This post was super helpful, thanks!!!

  38. Good afternoon: Thank you for all the helpful instructions and guidance on this blog!

    My first attempt at an "IF/AND" statement returns an accurate value:
    =IF((AND(D2>25,D2<=250)),199+((D2-25)*1.95))
    Where value keyed to Cell D2 is 250, resulting value is $637.75.

    Works great!

    Now I need to nest multiple IF/AND statements with different multipliers applied to the result based on specific ranges:

    If Q 25 and 250 and 500 and 1000 and 2500 and 5000 $199+{(Q-25)*.50}

    I can not get this to work!

    =IF(d2=0,"$0.00") & IF((AND(D2>0, D225, D2250, D2500, D21000, D22500, D2=5001),199+((d2-25)*.50, "")

    Any help is appreciated!

    • That did not post correctly! My specific ranges are:

      =IF(d2=0,"$0.00") & if((AND(D2>0, D225, D2250, D2500, D21000, D22500, D2=5001),199+((d2-25)*.50, "")

  39. Hello all,

    I am trying to figure out a function to group states into regions. I found out how to group individual states into regions, however I need a formula that will group multiple states together. For example, Texas is in region 6, however I need to group all of the states that are in region 6 together. I am using the current formula:

    =IF(Cell of state="StateAbbreviation","Region", IF(Cell of state="stateabb2","region 2"))

    Any help would be wonderful! Thank you!

    • Do NOT use IF for this.

      1) Create a table with the state (or abbreviation) in the first column and the region in the 2nd column. For this example, let's say this information is in this range. f2:g51

      Ca West
      Ia Midwest
      TX South
      Fl Atlantic
      Co Mountain
      ...etc

      wherever you need the region use this formula:
      =VLOOKUP(A6, f2:g51,2)

      A6 in the above example is the state

  40. I want just to thank you for your valuable efforts. many thanks

  41. I am trying to write a formula in Excel to work out Stableford points from Stroke scores in golf.
    I tried writing one formula, but it became too complicated, so I separated it into two, one for handicaps 1-18 and the other for 19-36. Both these formulae work individually, but I would dearly like them to work as one. Can you help me to join them together.

    =IF(AND(C4<19,C4<=E3),MAX(0,B4+3-E4),MAX(0,B4+2-E4))
    =IF(D4<=E3,MAX(0,B4+4-E4),MAX(0,B4+3-E4))

    PACIFIC HARBOUR GOLF AND COUNTRY CLUB (Tees Gold)
    Hole Par Index Index Stroke Score Pts 0-18 Pts 19-36
    0-18 19-36 12 DAILY HANDICAP
    1 5 18 25 6 1 2
    2 4 10 28 6 1
    3 5 12 30 6 2
    4 3 16 34 5 0
    5 4 4 22 5 2
    6 4 8 26 6 1
    7 3 14 32 3 2
    8 4 6 24 6 1
    9 4 2 20 8 0
    10 4 7 25 4 3
    11 5 9 27 6 2
    12 4 11 29 6 1
    13 3 17 35 5 0
    14 5 13 31 4 3
    15 4 3 21 6 1
    16 4 15 33 7 0
    17 3 5 23 5 1
    18 4 1 19 5 2
    111 23

    • Not sure I follow everything but I think this works a lot better.

      Create a table like this:
      To par Stableford Score
      -4 6
      -3 5
      -2 4
      -1 3
      0 2
      1 1
      2 0

      Create a named range of the numbers in that table. Call it "scoring"

      Here's a table
      A B C D E F
      Hole Par 1-18 Stableford Score 19-36 Stableford Score
      1 5 6 1 2 5
      2 4 6 0 6 0
      3 5 6 1 6 1

      Column C & E contain the players stroke count on that hole.

      Cell D6: =VLOOKUP(C6-B6,scoring,2)
      Cell F6: =VLOOKUP(E6-B6,scoring,2)

      Copy D6 & F6 down the column to the next 17 holes.
      Sum column D & F to get the 2 round Stableford score.

      Hope this helps.

  42. I have tariff of equipment usage of minimum usage of 8 hours, so if any equipment is used 81 hrs, it should charge for 11 slabs. Is there any formula for the same.

    Thanks,
    Mitesh

  43. Dear Sir / Madam,

    I have pricing that $10 is charged for every 8 hours slab for crane. Now I want to calculate for any odd hours usage, and it should be charged as per the 8 hours slab.

    Can you help me out.

  44. Dear Svetlana from Ablebits,

    I would deeply appreciate your assistance with the following question regarding a advanced Excel IF function:

    1) I’d like to write a function which IF cell B6 is equal text “SP"

    2) AND, cell D28 has a value greater than 0 (zero)

    3) If all the above are true THEN the tab color changes to color the BLUE or just print which Worksheets are true to this formula, so I can identify.

    But this has to occur within more than 800 worksheets on the same workbook.

    Is that possible to do on a Excel for Mac?

  45. Hello, I'm trying to evaluate a series of values, to determine if the value in 2 or more cells is greater than 0. Any help you could provide would be awesome.

    Thank you!

    • =if(COUNTIF(D4:D20,">0")>=2,"Yes...2 or more cells is greater than 0","Nope")

      D4:D20 represents your "series of values"

  46. Hi Satish,

    Try This formula:-
    =IF(COUNTIF($A1:$C1,"No")>=2,"Not Eligible","Eligible")

  47. I have data in which in three columns there is ..

    yes yes no, two yes means eligible other then not eligible..

    yes no yes = Eligible
    yes no no = Not Eligible
    no yes yes = Eligible

    Please suggest the formula .....

    YES YES NO Eligible
    YES NO YES Eligible
    NO YES YES Eligible
    YES YES YES Eligible
    NO NO NO Not Eligible
    NO YES NO Not Eligible

    • =IF(COUNTIF(a10:c10,"Yes")>1,"Eligible","Not Eligible")

      Note: countif is NOT case sensitive

  48. how to calculate basic salary according to the following criteria
    1. Rs 18000 for Manager
    2. Rs 15000 for Account
    3. Rs 10000 for Receptionist

  49. Hey, I'm trying to write an IF formula to show how many people are required to perform a task. For example say people work 7 hours a day.
    If 0-7 hours = 1 person
    If 7-14 hours = 2 people
    If 14-21 hours = 3 people
    If 21-24 hours = 4 people
    The formula I wrote appears to stop after the first logic argument and not apply the next part of the 'IF' correctly. Any help much appreciated.

    • Hello, Jonathan,

      it's hard to tell why your formula doesn't work properly since you haven't included it :) But I can offer you to try the following one (assuming that the number of hours is in A1):
      =IF(AND(A1>0,A1<=7),"1 person",IF(AND(A1>=8,A1<=14),"2 people",IF(AND(A1>=15,A1<=21),"3 people",IF(AND(A1>=22,A1<=24),"4 people",0))))

      Note that it will return 0 if the number of working hours is also equals to 0.

      Hope this is what you need!

      • Thanks so much for getting back.
        My original was below (assuming U3 was the cell value in the below.
        =IF(U3<7,"1 person",IF(U3<14,"2 people",IF(U3<21,"3 people",IF(U3<24,"4 people","5 people"))))
        I tried using your formula but still no joy.
        For example if cell U3 is say 13:30 (13 hours 30 mins) of work and a worker worked 7 hour shifts then you would need 2 people to cover 13 hours and 30 mins of work but this doesn't seam to work and stops after the first logic test.
        I have my reference cells (e.g. U3) formatted as HH:MM:SS does this affect it?
        Thanks again

        • =IF(AND(C3>=0,C37,C314,C321,C3<=28),4,IF(C3="",0,5)))))))

          cell reference is C3

          • =IF(AND(C3>=0,C37,C314,C321,C3<=28),4,IF(C3="",0,5)))))))

    • How about a NO LOGIC formula. I think this is a much easier formula and it scales to as many hours you need without changing the simple formula.

      B1 contains the number of hours a project requires.
      hoursPerWorkerPerDay contains 7 (in your example)

      =INT(0.999+(B1/hoursPerWorkerPerDay))

      Or more simply

      =INT(0.999+(B1/7))

      Why add 0.999 to each value before dividing by 7? If a job will take 6 hours, 6/7 = 0.857... Effectively adding 1 to this, returns 1.857... INT returns the integer value or 1. This meets your spec.

      Why not just add 1? Try it! Say a job takes 7 hours. =INT(1+7/7) returns 2 whereas 0.999+7/7 returns 1.999. Taking INT(1.999) returns 1. This meets your spec.

      Nested logic in If statements get very complicated, very quickly. They are hard to read and troubleshoot. Of course sometimes this may be necessary but I always look for another way.

  50. I need a formula for below conditions:
    =if(749>A1>500 then B1*5%, (999>A1>749 then B1*7.5%, (A1>1000 then B1*10%)))

    please revert me if it is possible.
    Thanks

    • I'VE NEVER GOT A REPLY FOR THIS TYPE OF SITUATION...
      SO IT'S OKKKKKKK..

    • Try this

      =IF(AND(749>A1,A1>500),B1*5/100,IF(AND(999>A1,A1>749),B1*7.5/100,B1*10/100))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)