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

4491 comments

  1. I am very new to this so I want to apologize in advance for the hopefully simplistic question... I have opened a new business and am trying to track expenses so I can get them to my accountant in a more timely manner... in column A I have the date; column B the invoice number; column C is a dollar amount; column D is a drop down box with type of expense (i.e. resale, shop supply, tool, other) What I am trying to do is create a formula that allows me to compare the drop down in column D with the cell next to it as a dollar amount to track the total expenses. So in logical terms (not formula because that's what I'm needing help with) if anything in column D = equals shop supply take the neighboring cell from column C and add it together then render it as a sum total this specific cell named shop supply... and so on and so forth for the remaining types in the drop down box... I truly hope that made sense an appreciate any assistance given!!

  2. Could really use some help.

    Basically I have a deadline date in H3.

    I need the formula in O3 to check whether the deadline (H3) has passed today's date. If it has, it then needs to check whether the work has been completed on time (N3), and dependent on the result, display Late or Not Late.

    Additionally, I need it to display Late or not Late if a date has not been entered in N3.

    Hope someone can help.

    Thanks.

  3. Could really use some help.

    Basically I have a deadline date in H3.

    I need the formula in O3 to check whether the deadline (H3) has passed today's date. If it has, it then needs to check whether the work has been completed on time (N3), and dependent on the result, display Late or Not Late.

    Additionally, I need it to display Late or not Late if a date has not been entered in N3.

    Hope someone can help.

    Thanks.

  4. I need a formula to get the values

    Cell C1 and C2 have four Different values:-
    "Yes" "Yes"
    "No" "No"
    "Yes" "No"
    "No" "Yes"

    I need the result for all the four conditions as:-

    If C1 & C2="Yes" result is 1
    If C1 & C2= "No" result is 2
    If C1="Yes" & C2= "No" result is 3
    If C1="No" & C2="Yes" result is 4

    Kindly help me on this.

    • Hello, Akash,

      Please try the following formula to get the result you need:
      =IF((AND(C1="Yes",C2="Yes")),1,IF((AND(C1="No",C2="No")),2,IF((AND(C1="Yes",C2="No")),3, IF((AND(C1="No",C2="Yes")),4,""))))

      Hope it will work for you.

  5. I need a formula that will pull that sales tax from a table based on the county only when that state is entered.
    So, if the state is KS and the county is Marshall, I need it to look up that tax rate. Some of my states don't require tax but may have the same county name as those that do. For example, the state is IN and the county is Marshall. I do not need the tax pulled for this state. Is this possible?
    I have successfully gotten the tax rate looked up based off the counties but not both the state and county.

  6. I need a formula where if i select YES the score should be 2 and if select NO the score should be 0 and if i select NA the score should be 2

    • Hello, Jebin,

      If we understand your task correctly, the following formula should work for you:

      =IF(A1="YES", 2, IF(A1="NO", 0, IF(A1="NA", 2)))

  7. Please enter the excel formula if you need the cell to show “0” if 3 plus 4 equals and “1” 3 plus 4 is either greater than or less than 7”. Please exclude the equal(=) sign in your response.

  8. I want to use below formula in same columan, can I?

    =(50000/15000*20000-2%)

  9. I want if A1=Payment, add B1 to C1 and return value in D1.And If A1=Sales, Subtract B1 from C1 and return the value in D1. How I can use the nested IF Function/formula?

    Thank you!

    - Kamran

  10. Hi,

    I need help to design a whatif statement (I think). The conditions are;
    Condition 1
    0-26
    27-39
    40-52

    Condition 2, applies to each of the above condition;
    Pension 1
    Pension 2

    Condition 3, applies to each of the above condition;
    Type1
    Type2
    Type3
    Type4
    Type5
    Type6

    Each of the above would generate a different result.

    Can this be done in one formula?

  11. you need the cell to show "0" if 3 plus 4 equals and "1" 3 plus 4 is either greater than or less than 7".

    Please help

  12. I have A2=28, B2=45 to get a smaller value i applied the formula =IF(A2>B2,A2-B2,A2). So i got an answer has 28.

    My question is if i change a value in A2=50 now i should get a value has B2 in C2 cell.

    So i have entered has =IF(A2>B2,A2-B2,A2)*OR(IF(A2<B2,B2,B2))

    Still it is not working, can someone assist me how to put a formula with a proper condition?

  13. Hi

    i need help on my IF function

    Column A = 2017
    Column B = DEL

    i have this to make a formula that if column A and B is meet the condition is equal to 1, if A is not meet and B is meet is equal to 0...

    i have this formula but still zero , A and B are meet the condition

    =IF(AND(A1="2018",A2="2 MO OD"),1,"")

    thanks

  14. hi

    i have a problem on this

    column a is "2018" and column b is "2 MO OD" C equals to 1, if ever a or b is not meet C equals to 0

    I HAVE THIS FORMULA COLUMN A AND B HAVE MEET THE VALUES BUT STILL AT COLUMN C IS ZERO

    =IF(AND(BE135="2018",BG135="2 MO OD"),1,"")

  15. HELP. I wish to know the correct formula with the scenario below:

    Every 1x more than 1 = 5 points (incremental) or every 0.5x lesser than 1 = 1 point (decremental)

  16. I have A2=28, B2=45 to get a smaller value i applied the formula =IF(A2>B2,A2-B2,A2). So i got an answer has 28.

    My question is if i change a value in A2=50 now i should get a value has B2 in C2 cell.

    So i have entered has =IF(A2>B2,A2-B2,A2)*OR(IF(A2<B2,B2,B2))

    Still it is not working, can someone assist me how to put a formula with a proper condition?

  17. hi....
    I need a formula for count below criteria

    Column A contain with 1-20 numbers
    Column B contain with "text" or empty cells

    I want to count number of cells which contain "text" with 2s( which in column A)

    As example if column A contain 5 2s, and column B contain only 3 "text";
    Then answer should be 3

    Please help

  18. Hi friends,
    I tried this formula but it does not work. Can anybody tell me where is the problem ? How can I correct?

    =IF(G2>=60,"A",IF(G2>=50,"B",IF(G2>50,"C")))

    • Ismayil:
      I think you just need to change the ">" in the last IF to "<".

  19. How to apply IF condition if the days fall in this category

    >45 Days
    31-45 Days
    16-30 Days
    0-15 days

    • Rajesh:
      Where the data is in A2
      =IF(A2=<15,"Something",IF(A2=<30,"Something",IF(A2,45,"Something","Something Else"))))

  20. how to put if value is 100000 than minimum 1000 & is value is more than 100000 than value is should be 1%

  21. How do I write a formula reading from another tab
    tab 2 is to add all the cups in tab 1, so it should be somthing like
    sum(tab1 B2: B47 = CUP and/if I2:I47 is greater than 0

    Column B is the consumables and Column I is the qty used
    Column B could have CUP down several times.

  22. =IF(A2 isWEEKDAY(7),"20",(=COUNT(B2:K2))) I'm pretty sure i messed this up i need it to check if the date is the 7 day of the week else count the rows from b to k

  23. If Sales is greater than or equal to Sales Plateau for Bonus Assumption, 100% of Bonus Assumption will be given; If Sales is greater than or equal to 80% of Sales Plateau for Bonus Assumption, 60% of Bonus Assumption will be given. If Sales is greater than or equal to 60% of Sales Plateau for Bonus Assumption, 50% of Bonus Assumption will be given. Otherwise, no bonus will be given.
    PLEASE someone help me

  24. I have a dataset in which I need to index column J1:J30000 IF column BF1:30000 is greater than 94% and if it is less than 101%. I can already get =IFERROR(INDEX('GM400'!$J$1:$J$30000, SMALL(IF('GM400'!$BE$1:$BE$30000<$L$1,ROW('GM400'!$J$1:$J$30000)), ROW(1:1))), "") to work to locate projects over budget but I'd like to show those projects that are within 5%, 10% and 20% of their respective budgets.

  25. Hi I was trying to work on IF, but some error while output.

    CAT COLOUMN = SC
    ANNUAL INCOME = 280000
    MARKS CARD FEE =

    CONDITION 1) If CAT=SC/ST WITH ANNUAL INCOME IS <= 2.5LAKH THEN MARKS CARD = 0
    CONDITION 2) If CAT=SC/ST WITH ANNUAL INCOME IS ABOVE 2.5LAKH THEN MARKS CARD = 290
    CONDITION 3) IF EXCEPT SC/ST CATEGORIES MUST BE MARKS CARD FEE SHOULD BE TAKEN 290.

    kindly help on this formulaes.....

  26. I am working on a formula in excel. My requirement is adding more than two scenarios in one cell.

    Grade CTC Basic Salary(35%of CTC)
    EA 1000000 350000

  27. I wanted to know if there is a formula for a table that I am trying to make.

    I need that if for example A26:A206="1" then the average number from F26:F206 will be in the cell that I am pointing it to, and also for a different cell if A26:A206="go" the count it. If I am explaining myself correctly.

  28. =IF(B8<1, "12-(2.4*B8)",IF(AND(1<=B8,B8<1.2), "=57.6-(48*B8)"))

    when i apply this, It does not calculate the value of =12-(2.4*B8). It shows the formula as text.

  29. Hi I was trying to work on IF, but some error while output.

    COL A = APPLE
    COL B = G1
    COL C = 500
    COL D = 2

    If B,C,D IS NOT EMPTY THAN CONCATENATE A_B_C_D
    AND
    if D IS EMPTY THAN CONCATENATE A_B_C
    ELSE A
    =(IF((B2&C2&D2)"",A2&"_"&B2&"_"&C2&"_"&D2,(AND(IF(D2"",A2&"_"&B2&"_"&C2,A2)))))

  30. Enter the data in the next slide into a worksheet and use the information below to complete the blanks by applying appropriate spreadsheet functions and formulae;

    Scale Basic (use IF statement) M3 550,000 M4 425,000 M5 275,000 M6 150,000 M7 120,000
    Allowances = 150% of the Basic Pay.
    Gross Pay = Basic + Allowances
    PAYE is computed as follows (use IF statement) 0 – 130,000 No tax 130,000 – 235,000 10% of what is in excess of 130,000 235,000 – 410,000 20% of what is in excess of 235,000 + 10,500 410,000 above 30% of what is in excess of 410,000 + 45,500
    NSSF = 5% of Basic Pay
    Deductions = PAYE + NSSF
    Net Pay = Gross Pay – Deductions

  31. I'm trying to create a formula for the following criteria:

    a) If A3 is zero, input zero
    b) If B3 is greater than $1,000,000, input zero
    c) If B3 is between zero and $1,000,000, input the lesser of A3, and $1,000,000 minus B3

  32. Hi,

    I am trying to calculate stamp duty.

    So if Value is £300,000 or less stamp duty would be 0.

    But if the value was £325,000 the stamp duty would be 5% of the extra £25,000

    How do i put this into excel? So that i can just input house cost, and it will work out the stamp duty for me.

    Thanks
    Kirsti

  33. 1.) Column F has multiple status such as ASAP, Critical, High, IT Major, Low Sev – High Volume, Normal, VIP (Executive)
    2.) G = Submit Date
    3.) K = Resolve date
    Q1: if status is Closed or Resolved (Col F) & col K is Null, the output should be "Missing Resolved Date"
    Q2: If Status is anything apart Closed or Resolved, the Col K will not have date but will be null and it should return "Ticket is open"
    Q3: If status closed or resolved and there is date in column K, then it should calculate the Days between Col G & Col K.

  34. What I want is a formula that will return a result for 20% of a given number but if the result is less than say 400 then display 400 not 20% of the given value. For example lets say we have values of 3000, 6000, 4000 and 1000. For the first 3 I would want results of 600, 1200, and 800 but for the last one as 20% would be 200 ( less than 400) I would want the result to display 400.

  35. IF(AND(B21="U2",D21 0),0.15,)

  36. i have this formula:

    =IF(AND(B21="U2",D210),0.15,)

    i want to return error message if parameter is not met.

    ex. B21 = "Not U2"
    error message = B21 is not met

    thank you!

  37. i have this formula:
    =IF(AND(B21="U2",D210),0.15,)

    i want to return error message if parameter is not met.

    ex. B21 = "Not U2"
    error message = B21 is not met

    thank you!

  38. Hi,

    Please help me. I've been cracking my head for almost a week now.I need a formula to calculate incentive.
    Above to 30 count automatic rich to next cell
    Under to 30*50 Rs.
    Under to 30-50*75 Rs.
    above to 50*100 Rs.
    please make a formula...

  39. Hi Friends
    I want to count the number of workdays for each month in a table format.
    A B C D
    1 10 P1 01/23/2018
    1 12 P2 01/23/2018
    2 23 P3 01/24/2018
    3 55 P1 01/25/2018
    1 55 P2 02/08/2018
    1 77 P2 02/08/2018
    Column A = the number of workdays for each month
    Column B = number of Products
    Column C = name of Products
    Column D = date

    I want a formula in order to give me the number of workday(s). for example for above mentioned data we have 3days in Jan and 1day in Feb.
    I would be very thankful if you respond me ASAP.

    • Amir:
      Where the dates are in the range M11:M16, in an empty cell I would enter the label "Number of workdays in Jan". Then in a cell directly to the right or left of the labeled cell I would enter this formula:
      =COUNTIFS(M11:M16,">=1/1/2018",M11:M16,"=2/1/2018",M11:M16,"<=2/28/2018")
      You can expand the range to include the addresses you'll need as the year progresses say from M11:M34. Alternatively you can use the absolute reference from the get-go by using the dollar signs like $M$11:$M$34. This would work if you knew ahead of time what the year's range would be.

      • Hi Doug
        but your formula counts every dates which reach the condition. for example if I have two records in one day of Jan, the formula will result in 2 but the correct answer is 1.
        do you get what I mean?

  40. Doug

    We have hundreds of test reports. In the test reports there are as many of four boxes(3x3 cells). Each cell represents an area that was tested for air quality. If the combined value of the box >=0.6 it "fails". I'm using <=0.6 "pass".

    Therefore using a range reduces the time needed to create the formula and would make it easier to find errors

  41. Beth:
    Not sure about the logic of your example, but this might work:
    =IF(AND(J8>0,J8<6),5,"Something Else")
    It says, If the value in J8 is greater than 0 and less than 6 display 5 otherwise display something else.
    I used greater than 0 because I didn't think the value in J8 being equal to 0 and less than 6 made sense for an IF/AND statement.

  42. I am trying to do an IF/AND formula that will return a value, not a word; ex: if a cell =0 and is <6, then return 5.

  43. Hi Doug

    thanks for the reply.

    I'm currently using:

    =IF(AND(U20<0.06,Y20<0.06,Y40<0.06,V60<0.06),"YES","NO")

    This works but I'd rather be able to use the range:

    U20:AA25
    U40:AA45
    V60:Z66

    • Paul:
      It seems as though the formula you're using works.
      Why do you want to use the ranges?

  44. I have three ranges of cells. Each range individually must return a value of <0.06 to be true.

    U20:AA25
    U40:AA45
    V60:Z66

    • Paul:
      OK, so you've got some data in these rows.
      Do you want an IF/Then Statement to check to determine if the value in a range is less than .06.
      Is that what you're saying?
      Or are you asking for an expression that will determine the value of these ranges?
      Are you currently using an expression to produce the value of each range?

  45. I have three conditions but I have not getting the results

    Please help.

    =IF(A5="Dist","((D5<=106,'10.90','7.90'))","((D5<=106,'9.20','6.20'))")

    • Amol:
      I think it would help you to put into plain language what you're trying to do. Something like, "If the value in A5 is "Dist" then display something, otherwise display something else."
      Try that and see if it helps. If you can do that and it's clear to you what you want to happen and you still can't figure out how to do it, post that sentence and we'll try to help you.

  46. Here is my issue:solve for "A3"

    Sheet1, A1 dropdown list has 7 options

    Sheet1, A2 dropdown list has 10 options conditional to Sheet 1,A1. (This I have solved so far)

    Sheet1 A3 references sheet2, A1-A10, down to G1-G10 respectively dependant upon option chosen in cell A1 and A2. HELP!!!!!

  47. Nabi:
    If the employee's service time is already determined and is displayed in C3 then the formula is
    =IF(C3>180,"Confirmed","Probation")
    If the time of service for the employee is not calculated you'll need to calculate the time of service for the employee. The time of service can be determined by =DATEDIF(Start Date Cell Address,TODAY(),"D")
    This will display the number of days from the start date to today.
    Keep in mind the TODAY() function can change which might be what you want or not.

  48. Hi i am looking for formaula for employee conformation like if the employee done with 180 days automatically it should show confirmed if it is less then 180 it is Probation can u please help on it

  49. My issue is that I use this formula =IF(F45="","",IF(F45<=0,"Due","OK")) this mean fuel requirement is due or not. but an other condition that in equipment is down so the i need this option in my formula

    thanks

  50. hi... i tried to use an if function it didnt work. the sceario is that we use 2 different weight scales metric and imperial. i just wanted to excel to calculate and show me every weight in grams rather both. i tried this formula "=IF(L4="Grams", "K4*O4", "K4*O4/2.2046")" and unfortunately it didnt work. can u please help me?

    • Adnan:
      What about your formula didn't work?
      What's in L4?
      What's in K4 and O4?
      The expression K4*O4/2.2046 should probably be written K4*(O4/2.2046) or (K4*O4)/2.2046 depending on what's in O4.

    • Hi Adnan,

      And you should not enclose cell references and arithmetic expressions in quotations marks. For example, this formula calculates just fine:

      =IF(L4="Grams", K4*O4, K4*O4/2.2046)

      • Good catch, Svetlana:
        Not sure why, but I didn't see the double quotes around the expressions inside the parenthesis. That would definitely not work.

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 :)