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

4530 comments

  1. Sorry, made an error in the formulas I have tried.
    I meant:
    =IF(L2 ="Deletion"; "0"; "=F2-E2")

    • Hi Marcia,

      You were almost there :)

      Of course, you can use other functions and mathematical operations in the value_if_false and value_if_true arguments. You just don't want either = or "" there. When you enclose something in double quotes, Excel treats it as a text string rather than a cell reference of arithmetic operation, so it returns exactly what you typed.

      Once you remove those two, the formula works just fine:

      =IF(L2 ="Deletion"; 0; F2-E2)

      If you'd rather have an empty cell instead of 0 when the condition is met, then you put it like this:

      =IF(L2 ="Deletion"; ""; F2-E2)

      Also, there's a little mismatch between your description "display the sum of two other cells" and the formula :) If you really wanted the sum, then it should be:

      =IF(L2 ="Deletion"; 0; F2+E2)

      Hope this helps.

  2. =IF(logical value; value_if_true; value_if_false

    Is it possible to have a formula instead of a value for "value_if_false"?

    I want my cell (G2) to display the sum of two other cells (F2 and E2) unless the value of another cell (L2) corresponds to a specific text.

    I have tried:
    =IF(L2; "Deletion"; =F2-E2)

    But this does not work, and if I try:
    =IF(L2; "Deletion"; "=F2-E2")
    the cell displays =F2-E2 rather than the resulting number.

    Any ideas?

    Thank you

  3. Is there a way to test if a formula or function has been entered into a cell.

    For example when I want my pupils to enter a function into a cell. I then want to indicate if the formula is correct but only if they enter the function not if they enter the answer.

    At the moment entering the answer with out the function will still trigger the IF function.

  4. Very nice product

  5. Hi I'm new to excel and I'm not understanding the IF and OR function.
    the formula that I'm not understanding Is..... There is cell D7, E7, F7 above D7 is Category, above E7 is Pay Rate, and above F7 Evaluation, under Category is EMP, under Pay Rate is 1, and under Evaluation is 2.
    It says to create an IF formula in the selected cell that uses an OR function to return a value of .04 if cell D7 contains "EMP" or if cell F7 is 2 or less, and otherwise returns a value of .02.
    Can you please help me. Thank you.

  6. I'm sorry. My entire formula did not copy over. Here is entire IF formula:

    =IF(A2<1,"0",IF(A2=501,"=A2")))

    What I am trying to do is say if the A2 cell amount is larger than 501, then the actual number from cell A2 be shown. How do I do that? When I put this in, the answer I get is, of course, A2 and not the actual number. Please help!

    • Hi Lorraine,

      Your formula is almost correct, you just don't need to enclose cell references and numbers in double quotes:

      =IF(A2<1,0,IF(A2=501,A2))

  7. I am trying to write out the following IF formula.

    =IF(A2<1,"0",IF(A2=501,"=A2")))

    What I am trying to do is say if the A2 cell amount is larger than 501, then the actual number from cell A2 be shown. How do I do that?

  8. SIR

    I WANT TO EXTRACT DATA FROM PDF FILE TO AN EXCEL WORKSHEET AUTOMATICALLY WHAT IS FUNCTION

  9. Dear Svetlana
    I am very happy that, I really was helped by formula.

    Thank you

  10. My formula checks if the description of a cell contains certain words, to sort payments that have been made by bank / cash / others.

    IF(ISNUMBER(FIND("BACS",E2)),"Bank","")&IF(ISNUMBER(FIND("DD",E2)),"Bank","")&IF(ISNUMBER(FIND("CHQ",UPPER(E2))),"Bank","")&IF(ISNUMBER(FIND("CASH",UPPER(E2))),"Petty Cash","")

    So now I have a column with "Bank", "Cash" and "" (empty) for "Others".

    I was wondering whether there is an easy formula that I could add to my current formula, so instead of "" (empty) would show "Others".

    I am thinking something with the same structure as IFERROR, but with the value of the cell (for example: IFVALUE(formula="","Others")

    Any ideas?

    Thanks,

    Eva

  11. Sales ($ millions)
    Quarter 1 500
    Quarter 2 350
    Quarter 3 495
    Quarter 4 620

    2. Write an IF statement in Cell C9 that Quarter includes a nested MAX function and that returns the text "Exceeded $600M sales in one quarter" if any quarter meets that criteria or "Quota not met" if not.

    Any one who can help me to solve this problem ? please help me

  12. Wow, thanks very much for this tutorial on these Excel functions, this was very helpful unto me. Jesus Christ Bless you! :)

  13. Same sheet cell reference

  14. You have to Take the cell reference to Logical Test instated of Sheet Name "50 Cust Input"

  15. =IF(('50 CUST INPUT'!E:E="Master Cable Network"),1222352,IF(('50 CUST INPUT'!E:E="YASHASH CABLE NETWORK"),1222530,elseif(('50 CUST INPUT'!E:E="OUTPACE OPTIFIBRE NETWORKS PVT LTD"),1229944,else IF(('50 CUST INPUT'!E:E="FIGROVIN COMMUNICATION SERVICES PVT LTD"),1222351,elseif(('50 CUST INPUT'!E:E="TCL"),1005296,elseif(('50 CUST INPUT'!E:E="YOU BROAD BAND"),1215633, elseif(('50 CUST INPUT'!E:E="TATA TELE SERVICES LIMITED" ),60005)))))
    pLEASE EVALUATE

    • the formula dowesnt work at all please help

    • =IF('50 CUST INPUT'!E:E="Master Cable Network",1222352,IF('50 CUST INPUT'!E:E="YASHASH CABLE NETWORK",1222530,IF('50 CUST INPUT'!E:E="OUTPACE OPTIFIBRE NETWORKS PVT LTD",1229944,IF('50 CUST INPUT'!E:E="FIGROVIN COMMUNICATION SERVICES PVT LTD",1222351,IF('50 CUST INPUT'!E:E="TCL",1005296,IF('50 CUST INPUT'!E:E="YOU BROAD BAND",1215633, IF('50 CUST INPUT'!E:E="TATA TELE SERVICES LIMITED",60005,"")))))))

      • Hello could you explain why the following formula shows
        :
        =IF(AND($D51>$S$2,$D51<16),"",F43)

        =IF(OR(C144=1,C144=0),0,SQRT(C144/(C144-1)))

        =IF(C230="",0+D229,C230+D229)

        =IF(D229<D230,D230,"")

        =IF(AND(C$409=$S$1-1,$B390<$S$1),C69,IF(C356="","",C356*SQRT(C111)+C$86))

        =IF(C$388$S$1-1,"",SUM(D431:D445)/(D$21-1)))

        =IF(C468=$S$1-1,C469,IF(C$6="","",SUM(C452:C466)/(C$21-1)))

        =IF(D25=1,C68,IF(OR($B473>$S$1,C$472>$S$1-1),"",C$406+$R$351*SQRT(C$450/C$470)*(C491-C$427)))

        =IF(C26=1,C6,IF(OR($B510>$S$1,C$508>$S$1),"",NORMINV(RAND(),B474*B510,SQRT(B$449*SQRT(ABS(B510*S510))))))

  16. Hi ! Will you please help me how can i access the name from one sheet to another sheet . the main thing is when i call the data from (sheet1!A1) after that 14 row should be blank in sheet 2. as well as on the row 15 of sheet2 , should store ( sheet1! A2) data. I have created such type of format on Sheet2

    NAME :- Rozer 1 Roll-no 801

    14 time blank line after that

    NAME :- Sammul Roll-no 802

    }}}}

    All information are accessing from sheet1 . i want if i enter the data in sheet one, then sheet 2 automatically create such type of forms.I hope you will suggest me the best formula.The main problem arising in ( NAME & Roll-No ) column.
    Thank you !

  17. my last comment the formula should be K9 less than or equal to I35 Accept and K9 greater than or equal to I35 reject

    It did not show the formula here is a try again

    =IF(AND(K9=I35),"Accept","Reject"))

  18. Hello could you explain why the following formula shows "reject"
    =IF(AND(K9=I35),"Accept","Reject")

    The formula in K9 is "=E9-J9" and totals -1.7
    I35 has no formula and has a value of 5.

  19. Hi. I am making a simple formula for tests results. I have already a list for the percentage of the raw scores. All I wanna do is when I input the raw score (1st column) its equivalent percentage will automatically show in the next column.

  20. I am trying to insert a formula that will search column B for a specific text and then enter the data from that same row next column that it found from specific text. For instance Column B2:B44 has a list of products in stock, Column C has a list of quantity of items needed to be made of items in column B per Month to meet sales reports these totals are changed monthly per multiple locations, I am trying to create a formula that will be universal for each location, by copy a monthly email attachment that has these changes. Problem with every formula I have came up with is that there is no set format in the email sent monthly Column B2 this month may be a certain product but next it may be a different product. So I need something that constantly analyzing this.

  21. IFERROR(IF(ROUND((($P$8-$H14)/$H14)+1,4)>1.5,1.5,IF(ROUND((($P$8-$H14)/$H14)+1,4)<0,0,ROUND((($P$8-$H14)/$H14)+1,4))),"-")

    Please explain what this if error means. please include an example as well.

  22. Hi

    I have a question of calculating percentile.

    Suppose there are two columns A and B. Column A is "ID" and column B is values. There are many values in column B for a certain "ID". What I want to do is to calculate 90 percentile for each ID. How should I do this? Thanks.

    Max

  23. Hi I am unable to figure this out.

    I need a formula that says: If any of the cells D2:D100 contain the term "SEC", then add the number value from it's corresponding G cell.

    For example, if D2 and D5 both contain "SEC", then I want to combine the numbers found in G2 and G5.

    Any idea how to create this formula?

    Thanks!

  24. Please i need help
    I am working in a store where we sell something like maggi, rice, beans etc.
    I want to use excel to record the data and calculate how much i sold par day.
    column A: date, Column B: goods, Column C: price
    Column D: Sales
    I need formula to do this

  25. I need your help on how to manage sales account, formula to use for sale account for stock

  26. I have a wonderful "IF" statement that looks for a single word in sentence in a cell.
    SAMPLE: =IF(ISNUMBER(SEARCH("frozen",$U2)),"X","")
    However, I would like to search for another word at the same time, i.e. "freeze".
    In other words, If the words "frozen" or "freeze" appear, then put an "X" in the column/cell that I am working from.
    I cannot seem to make this work. Can someone help?
    Seems like it should be so easy.
    Thanks in advance.

  27. Need Your help.

    My Result is 120 and target is 100 so achievement is 120% in the fourth column i need show % and need to put condition that if achievement is between 101% - 105% than it should show 1% in the fourth column and if achievement is between 106% - 110% than in same column it should show 2% and if achievement is between 111% - 115% than in same column it should show 3% or if achievement is more than 116% than in same column should show 4% (Basically it should show maximum % applicable)

  28. Hi I would like to get help with nested if
    I would like to calculate performance of employee tasks based on 2 tasks types.conditions.
    1. if task type is "a" then if task finsih in 1 day the grade is "100" if 2 days then the grade "80" and so forth.
    ELSE if task type is = "b" then if 2 days then is "100" and if 3 then "80" and so forth.

    the first half of the formula works, but not the second.

    =IF(A1="task a",IF(B2<0,"0",IF(B2<=2,"100",IF(B2<=4,"80",IF(B26,"50",IF(A1="task b",IF(B2<0,"0",IF(B2<=3,"100",IF(B2<=5,"80",IF(B27,"50"))))))))))))

  29. I have an issue with percentages. where the total passed was 14 and the total failed was 7 but the total in whole was 21... 7+14 = 21. i need a formula so the actual percentage shows. i get 50% when i use the example C10/B10

  30. Hello! I have an advanced formula I would like to input, but I am not sure how to write it. I am trying to combine a math formula and conditional logic formula in the same cell.

    Original Cell Contents: =(I11+J11+K11)-L11

    So, basically, I want the cell contents to be calculated with the above formula, but if the resulting amount is $85.00, then I want the cell to remain blank, and if the amount is not $85.00, then I want it to display the calculated amount.

    Can this be done?

    • Well, it would seem a friend was able to answer this for me! So, I will post the solution in case anyone else has this problem:

      =IF((I11+J11+K11)-L11=85,"",(I11+J11+K11)-L11)

  31. I am trying to work out the difference between dates in weeks. There is a start date (H17) and three possible end dates (O17),(U17) and (V17). If there is a date in O17 I want to work out the difference between O17 and H17, if there is no date in O17 I want to try U17 and work out the difference between U17 and H17, if no date in O17 or U17 I want to work out the difference between V17 and H17 in weeks. I can work it out using 2 cells using the following formula but struggling for 3

    =IF(NOT(ISBLANK(O17)),(O17-H17)/7,IF(ISBLANK(O17),(U17-H17)/7))

  32. Hi, I need formula for below condition.

    IF R1>S1 AND Q1>K1 THEN "BUY" & S1>R1 AND K1>Q1 THEN "SELL"

    Is it possible to apply both the condition in one cell using 'IF AND OR ELSE' or anything else you can think of ?

  33. need a formula for the following conditions:

    if cell value is greater than $160.00, but above 45% equals 6
    if cell value is between $155.00 - 159.99, and between 40% - 44.99% equals 5
    if cell value is between $150.00 - 154.99, and between 35% - 39.99% equals 4
    if cell value is less than $150.00, and below 35% equals 0

  34. Hi there,

    I need to say IF S1 = BIKE then VLOOKUP from 1 stock report but if S1 = blank then VLOOKUP from another stock report is this possible?

    Thanks,

    • Try This:

      IF(D20="BIKE",VLOOKUP(D20,'Report 1'!B2:E16,3,FALSE),VLOOKUP(D20,'Report 2'!C4:G17,2,FALSE))

      You have to adjust the table range, and column index as per your report structure.

  35. THERE ARE TWO CLIENTS, E.G. ABB AND DVC. IF THERE ABOVE TWO CLIENTS, THERE WILL NOT TO BE MULTIPLIED THEIR RESPECTIVE AMOUNT WITH %.

    I HAVE WRITTEN ONE FORMULA :

    =IF (COLUMN NUMBER="ABB","0",(COLUMN AMOUNT*12.36%),IF(COLUMN NUMBER="DVC", "0", (COLUMN AMOUNT*12.36%))

    AS A RESULT NO VALUE IS GIVING.

    WRITE THE FORMULA.

  36. Hello I’m working on a formula what I’m trying to do is:
    Example 1
    Billed amount: $10,000 (B2)
    Paid amount A: $200 (C2)
    Paid amount B: $200 (D2)
    Write Off: $100 (E2)
    Balance: $9,500 (F2)
    Allowed: $8,000 (G2)
    Total Paid: $400 (H2)
    Amount to Satisfy/Use: $8,000 (I2)
    Example 2
    Billed amount: $5,500 (B2)
    Paid amount A: $200 (C2)
    Paid amount B: $200 (D2)
    Write Off: $3,500 (E2)
    Balance: $1,500 (F2)
    Allowed: $5,200 (G2)
    Total Paid: $400 (H2)
    Amount to Satisfy/Use: $1,900 (I2)
    Example 3
    Billed amount: $5,500 (B2)
    Paid amount A: $100 (C2)
    Paid amount B: $ (D2)
    Write Off: $ (E2)
    Balance: $5,400 (F2)
    Allowed: $5,200 (G2)
    Total Paid: $100 (H2)
    Amount to Satisfy/Use: $5,200 (I2)

    I want it to use Allowed amount if that is less than balance (ex 1).However if amount paid and balance are less than allowed amount I want it to use the balance + total paid (ex 2). Sometimes there will not be any paid amount or write off amount, and in that case I would want it to use allowed amount (ex 3).This is what I currently have and its close but not quite giving all the correct figures as it changes.
    =IF(F9<=G9,SUM(F9+H9),(G9+H9))
    Thanks

  37. I am trying to calculate a rate based on destinations. My formula should read as follows:

    If EO1="cash" and J1=BRD and U1=BRD, then take EK*$1, if not, then EO1="cash" and J1=BRD and U1=BAX, then take EK*2, if not then EO1="cash" and J1=BAX and U1=BRD, then take EK*2, if not then EO1="cash" and J1=BAX and U1=BAX, then take EK*2, if false "")

    Something like that but it doesn't work. I am trying to charge customers for different routes because our rate is based on whether is was in the city or out of town, also I want it to only take into consideration the cash riders because vouchered riders are different. Please help.

  38. I am currently trying to create a tax spread sheet that can work out the total tax based on 1 input which is the monthly salary. South African tax is progressive and is based on tables per bracket.

    I have tried to create a nested if formula to assist in kind of automating the process for myself.

    …=if(49468*12272700,31425,(49468*12-174550)*.25))
    This is the formula in a nutshell, however the answer it produces is R 104 767.31
    Which is incorrect it should return R 31 425...

    What am I missing?

    • O sorry the formula actually is some characters dont show

      …=if(49468*12 smaller than 174550,0,if (49468*12 bigger than 272700,31425,(49468*12-174550)*.25))

      • …=if(49468*12272700,31425,(49468*12-174550)*.25))

  39. Svetlana, Thank you for an awesome website! I've expanded my knowledge of excel greatly with your help. I do have a problem I'm trying to workout and hope you can provide a solution. I may be trying to make my issue more difficult than it needs to be.

    I have a table that has Unique IDs for job billets that are all in column A "Unique ID". There are date columns for "checked-in" and "checked-out" as well as various other information pertaining to that billet. I get an update for this table on a weekly basis. Each week there may be Unique IDs not listed if that billet is vacant (the report only shows filled billets), or it will have a row with that Unique ID with a future date if somebody has been identified to be hired. On a 2nd tab I have a list of all the Unique IDs that I use as the baseline in column A. I need to check each unique ID against the weekly report to see if it's there (current) AND check the "check-in date", if the check-in date is in the future it should report as vacant, if it's in the past it should report as current. If the UID does not show up on the weekly report it should report as vacant.

    I currently am putting the weekly report on their own tabs, and I successfully created an "=iferror(index)" function to check for the UIDs on each tab. My report tab has all possible UIDs in column A, then I place the formula for checking each weekly report in columns B, C etc. I have the index place either the individual UID or "vacant" in the cell. But getting that lookup based on the check-in date has been stumping me. Am I making it harder than it needs to be? Can you help??

    • I tried working on this again tonight. I came up with =IFERROR(IF("02/09/15">(INDEX('20150209'!$A$1:$S$958, MATCH(A2, '20150209'!$A$1:$A$958,0), MATCH("Confirmed Check-in Date",'20150209'!$A$1:$L$1,0))), "NOT HERE", "Current"), "Vacant"), but no matter what date I put in the "check-in Date" it always returns true and comes up with "not here".

      I started with the IFERROR statement and had that working, it returned the correct info. I then just tried adding the IF, but it seems to not actually run the logical test for the IF statement. I feel like I'm close but missing something obvious.

      • Hello Ron,

        It is an interesting task, Ron. However, it is difficult to recommend you something without having an example.
        Please send a small sample table with your source data to support@ablebits.com and specify what status you want to get for them. We’ll try to help.

  40. Hi Svetlana,

    I've used your guidelines and they work great. My formula currently looks like this:

    =G5*((IF(E2=1,(1-(Sheet1!$G$2)),(IF(E2=2,(1-(Sheet1!$G$3)),etc. etc.

    G = Retail Price
    E = Discount code
    Sheet 1 column G = Discount %

    In writing, I am trying to get my 'cost price'. Column E has discount codes ranging from 1-12 and column G has retail price. The discount percentages I receive based on the codes are in Sheet 1, column G. The formula works...great. BUT it has made the workbook SO slow to load up (127,000 rows). The problem I have is I am about to do the same thing in a new workbook for a different supplier - but this supplier has 64 different discount codes. If it's struggling to work with 12, I'm guessing it won't cope with 64.

    Is there a better formula I could be using (LOOKUP for example)? The problem I have with LOOKUP is it seems the result can't be a formula, it has to be a value (unless I'm doing something wrong)?

    Thanks very much in advance for your help!

    • hi ive been trying to figure out how to do this problem:

      If(h6="sweetcorn soup",75,if(h6="lamb skewers",120,if(h6="onion soup",65,if(h6="vegetable chowder",100,if(h6="chicken & leek soup",150,"")))))

      But cant seem to get it. What could I possibly be doing wrong?

  41. very helpful.thank you. i reached the if and max/min function and stopped for today. i don't want to confuse my self with the rest.

  42. HI, I am trying to concoct a formula for a spreadsheet.

    If 1 person in the household and income is below $1342 then multiply cell b1 by 50%. If 2 people in the household and income is below $1809 then multiply cell b1 by 50%. If 3 people are in the household and income is below $2276 then multiply cell b1 50%. It is taken into consideration both income and household members. Thanks for any help.

  43. i have written this formula it doesn't work and the statement shows that "you haven too many arguments"
    please solve the formula.

    =IF(AND(Sheet1!E11="Applicable",Sheet1!F12='IT STATEMENT'!B4:B15),ROUND(100*(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))-(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))-Sheet1!E12+1))/(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))),0)+ROUND(120*(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))- Sheet1!E12+1)/(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))),0),IF(AND(Sheet1!E11="Applicable",'IT STATEMENT'!C418030),ROUND(130*(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))-DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0)-Sheet1!E12+1))/(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))),0)+ROUND(140*(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))-Sheet1!E12+1)/(DAY(DATE(YEAR(Sheet1!E12),MONTH(Sheet1!F12)+1,0))),0),IF(AND(Sheet1!E11="Applicable",'IT STATEMENT'!C4<=8200),100,IF(AND(Sheet1!E11="Applicable",'IT STATEMENT'!C4<=13070),120,IF(AND(Sheet1!E11="Applicable",'IT STATEMENT'!C4<=18030),130,IF(AND(Sheet1!E11="Not Applicable",Sheet1!E13="No"),0,elseIF(and(Sheet1!e11="Not Applicable",Sheet1!e13="YES",'IT STATEMENT'!C4<=8200),round(100*(DAY(DATE(YEAR(Sheet1!E9),MONTH(Sheet1!F9)+1,0))-Sheet1!E9+1)/(DAY(DATE(YEAR(Sheet1!E9),MONTH(Sheet1!F9)+1,0))),0),if(and(Sheet1!e11="Not Applicable",Sheet1!e13="YES", 'IT STATEMENT'!C4<=13070),round(120*(DAY(DATE(YEAR(Sheet1!E9),MONTH(Sheet1!F9)+1,0))- Sheet1!E9+1)/(DAY(DATE(YEAR(Sheet1!E9),MONTH(Sheet1!F9)+1,0))),0),if(and(Sheet1!e11="Not Applicable",Sheet1!e13="YES",'IT STATEMENT'!C4<=18030),round(130*(DAY(DATE(YEAR(Sheet1!E9),MONTH(Sheet1!F9)+1,0))-Sheet1!E9+1)/(DAY(DATE(YEAR(Sheet1!E9),MONTH(Sheet1!F9)+1,0))),0),140))))))))))))

    • Hi Murthy,

      A syntactic parser shows 3 extra closing parentheses at the end of the formula. Remove them and try to debug further : )

  44. i want to have an if function if the range a1:a10 has text in it i want the cell a12 to display
    "thanks" if not all has text example cell a3 has no text nothing will be displayed on cell a12 ""

    • Hi lae,

      Put this formula at A12 cell

      =IF(AND(ISTEXT(A1:A10),COUNTBLANK(A1:A10)=0),"Thanks","")

      • THANK you, this work for me, it just that i use the "thanks" on the value of false..
        i'm still new in using multiple functions at excel

        • ohhhhh... but when i try to remove text from a cell still the word thanks appears

          • Hi Iae,

            If you want to check for blank cells, try this formula:
            =IF(COUNTA(A1:A10)=10,"thanks","")

            If you want all values in cells A1:A10 to be text values, then go with the following one (it will display nothing if there's at least one empty cell, or number, or date):
            =IF(COUNTA(A1:A10)-COUNT(A1:A10)=10,"thanks","")

  45. I am trying to do the following with my data:

    If the Birthdate in the column falls between Aug 30, 2010 and Feb 28, 2012 is Year 2, if date falls prior to Aug 30, 2010, Too Old

    If the birthdate falls between Mar 1, 2012 and Sept 30, 2012, Year 1, if date falls after Sept 30, Too young

    • Hi Sandra,

      Considering that your date entry is in A1:

      =IF(AND(A1>="8/30/2010"+0,A1<="2/1/2012"+0),2,IF(A1="1/1/2012"+0,A1"9/30/2012"+0,"Too Young"))))

      Remember in Excel the dates are entered as mm/dd/yyyy

      • Just a correction

        =IF(AND(A1>=DATEVALUE("8/30/2010"), A1<=DATEVALUE("2/1/2012")), 2, IF(A1=DATEVALUE("1/1/2012"), A1DATEVALUE("9/30/2012"),"Too Young"))))

    • Hi Sandra,

      You can try the following formula with a few nested IF functions, where A2 is the first cell with data in the Date column:

      =IF(A2

      • Dear All,

        Please help me for using conditional as below

        if c12= Activated result 1, if c12= Paid Off result 2, if c12 = Repossessed result 3, if c12 = Terminated result 4

        thank in advance for help me

  46. This should be done via Cell Formatting rather than a formula.

    Select the cell where the formula outputs the result and press Ctrl+1 to make the Format Cells dialog appear. On the Number tabs, choose either Number or Currency under Category, and set "Decimal places" to 2.

    • unfortunately ms. svetlana it still does not recognize the zero, not sure why. I wish I could provide you with a screen shot. any other thoughts?

      • I believe the quickest way would be for me to have a look at your data. If you can send me your sample workbook at support@ablebits.com, we will try to figure it out.

        • Apologies, the solution may lay in the original formating.

          OKay, I believe that you may be displaying a single decimal place rather than two. There are a few solutions, but I believe that this may be the easiest - highlight the column & format cells & ensure that you are displaying two decimal places.

          Unfortunately I've just had someone - who holds a couple of degrees and is considered a genius - replicate this problem ... it happens to the best of us

          Arthur

  47. How can I get the zero to show on the spread sheet ($1.40) it shows 1.4

    &IF(AND(C25>=100000,C25<=200000),1.40,"")

    • change the number fields to dollars using the format drop down >numbers

  48. I have written the following formula that is working fine.
    (IF(ISNUMBER(FIND("Opening", L54)), "OPENING", IF(ISNUMBER(FIND("BIA", L54)), "BIA", IF(ISNUMBER(FIND("AICF", L54)), "AICF", IF(ISNUMBER(FIND("LASKIN", L54)), "LSKN", IF(ISNUMBER(FIND("OTHER", L54)), "OTHER", IF(ISNUMBER(FIND("PELL", L54)), "PELL", IF(ISNUMBER(FIND("SDEA", L54)), "SDEA", IF(ISNUMBER(FIND("SEOG", L54)) , "SEOG", IF(ISNUMBER(FIND("TRIBAL", L54)), "TRIBAL", IF(ISNUMBER(FIND("VA", L54)), "VA", IF(ISNUMBER(FIND("Cafe", L54)), "CAFE", IF(ISNUMBER(FIND("AIHEC", L54)), "AIHEC", IF(ISNUMBER(FIND("MEAL", L54)), "MEAL", IF(ISNUMBER(FIND("SERF", L54)), "SERF", IF(ISNUMBER(FIND("Rental", L54)), "RENTAL", IF(ISNUMBER(FIND("IHS", L54)), "IHS", IF(ISNUMBER(FIND("Donation", L54)), "DONATION", IF(ISNUMBER(FIND("refund", L54)), "REFUND", IF(ISNUMBER(FIND("Dacotah Bank", L54)), "DACOTAH BANK", IF(ISNUMBER(FIND("meal", L54)), "MEAL", IF(ISNUMBER(FIND("UST-", L54)), "UST", IF(ISNUMBER(FIND("GED", L54)), "GED TESTING", IF(ISNUMBER(FIND("Summer Reading", L54)), "LIBRARY", IF(ISNUMBER(FIND("Vehicle", L54)), "VEHICLE", IF(ISNUMBER(FIND("BOT", L54)), "BOT", IF(ISNUMBER(FIND("Employee", L54)), "EMPLOYEE", IF(ISNUMBER(FIND("PR DEDUCT", L54)), "EMPLOYEE", IF(ISNUMBER(FIND("CAFE", L54)), "CAFE", 0)))))))))))))))))))))))))))))

    BUT... .now I would like to add that will add IF(ISNUMBER(FIND("Voucher",L54)) move to column P and perform the formula above on column P.

    I have both formulas in a separate worksheet because I had read somewhere that we could do it that way but don't know the formula doing that way.

    How do I add this last step and is there and easier way to do this on a separate spreadsheet using a table format?

    • IF A2's value = O and B2's value = Y then take date from C2 and IF A2's value = O and B2's value = N then take date from D2 ELSE TAKE DATE 0

      • Hi AJAY,

        You can use the following formula:

        =IF(AND(A2="o",B2="y"),C2, IF(AND(A2="o",B2="n"), D2, 0))

        • IF A2's value = O then take date from B2 And IF A2's value = D then take date from C2 And IF A2's value = K then take date from D2

        • i am a dealer..

          i sell cars on installments
          i need a formula in excel which automatically due the amount after due date.

          3 years installment
          first installment: 49000
          2nd installment: 50000
          3rd installment: 50000
          every month installment: 8000
          33 months installment plan
          half year installment: 45000
          last installment 66000

  49. I managed to write a nested IF formula for a cell to increase a date based on multiple conditions:
    IF I2's value = PUB then take date from D2 and add 12mths
    IF I2's value = PHD and H2's value = PTR then take date from D2 and add 72mths
    IF false then take date from D2 and add 36mths

    =IF(I2="PUB",EDATE(D2,12),IF(AND(I2="PHD",H2="PTR"),EDATE(D2,72),EDATE(D2,36)))

    however now I have to add another criteria which seems to offer too many arguments...
    I need to say all of the above with the addition of:
    IF there is also a "Y" in N2, then an additional 12mths needs adding to this date

    is this possible to do in a nested IF?

  50. Hi I have one doubt. I need formula for the below condition.

    If the Cell value is in between 1-5 then "1-5 days".

    If the Cell value is in between 6-10 then "6-10 days".

    If the Cell value is in between 11-20 then "11-20 days".

    If the Cell value is in between 21-30 then "21-30 days".

    If the Cell value is greater than 30 than "greater than 30"

    I need condition formula for the above condition's.

    • Hello Sheethal,

      Assuming that your values are in A1, you can use either a long but easy-to-understand IF / CONCATENATE formula:

      =IF(AND(A1>=1, A1<=5), "1-5 days", "") & IF(AND(A1>=6, A1<=10), "6-10 days", "") & IF(AND(A1>=11, A1<=20), "11-20 days", "") & IF(AND(A1>=21, A1<=30), "21-30 days ", "") & IF(AND(A1>=31), "greater than 30", "")

      Or a shorter but more complex one:

      =IF(A1>=30,"greater than 30", CHOOSE(INT((A1+4)/5), "1-5", "6-10", "11-20", "11-20", "21-30", "21-30")&" days")

      • THANKS

        • Since Excel stops evaluating after the first if condition is true, you could use this nested if function also (Assuming no false data is inserted in A1, in which case you would need to insert something for testing proper value):
          =IF(A1<6,"1-5 days",IF(A1<11,"6-10 days",IF(A1<21,"11-20 days", IF(A1<31,"21-30 days","greater than 30"))))

      • Thank you!

      • if Sheet2 A2 is = Sheet1 A2 and Sheet2 B2 is = Sheet1 B2

        then i want C2 of sheet2 is = C2 of Sheet1

      • Hi Svetlana -

        I can't seem to get your formula to work. My cell values begin in A2 so that is all I am changing, but no matter what, my end result keeps showing "greater than 30" even though the cell value I am referencing is a 6.

          • what is the expected result of following formula?
            =IF(2<3,IF(4<3,1+1,2+2),3+3)

            • Ans is 4.

              Instead of asking this here you would have tried in excel itself dude.

              Explanation :
              Logical Test : If(2<3 - {Answer is "TRUE"}
              Value if true : IF(4<3,1+1,2+2)
              Value if False : 3+3

              As this is a nested if it has another condition inside. See value if true in above condition
              Value if true : IF(4<3,1+1,2+2)→ 4<3 = Ans false. so 4.
              Hence proved

      • THANK YOU!!!!!

        :)

      • Assume Numbers are mentioned in Column N13.

        =IF(N13=0,"0 Days",IF(AND(N13>=1,N13=6,N13=11,N13=21,N1330,"Greater than 30",0))))))

      • Exactly what I needed! Thank you!!!

    • Hello,

      I'm looking for some advice on using the if function in regards to placing a price within a price band. For example I have a price of 179 and price bands of 0-169 / 170-199. . . increasing at the same rate all the way up to 980-1000+. I have had one success of using this formula:=IF(C4>'Price Bands'!$B$3:$B$31,IF('Total Market AVG'!C4<='Price Bands'!$C$3:$C$31,'Price Bands'!$D$3:$D$31,0)) to obtain one correct result. However when i tried to copy it down for the rest of the prices, it did not work.

      Any advice on this would be great!
      Thanks

    • Please help on this wrong formula:

      =IF('TOTAL WEEK 1'!$D$4:$R$4>60;'TOTAL WEEK 1'!$D$4:$R$4-60;0)
      My problem is that excel only see the first row differ and not the rest of the rows if there is n value above 60.

      Thank you in advance

    • Assume Numbers are mentioned in Column N13.

      =IF(N13=0,"0 Days",IF(AND(N13>=1,N13=6,N13=11,N13=21,N1330,"Greater than 30",0))))))

    • Hi Sheethal,

      =IF(H11=0,"0 Days",IF(AND(H11>=1,H11=6,H11=11,H11=21,H1130,"Greater than 30",0))))))

    • I need formula for the following condition.

      If, A>B then A-C-D-E.
      But if, A<B then A+B+C+D+E.

      How to combined them using IF formula? any body can help do the formula? I'l appreciate.
      Thanks guys..

      • =if(A>B,A-C-D-E,A+B+C+D+E)

    • Thanks, it was super helpful

    • I want to do 5 possible answer for one cell. I do not know if it is possible.

      C/W = B2<340, D2340, D2<160
      4500= B2<450, D2450, D2220

      I have been trying:

      IF(B2<340:D2340:D2<160,"Verify A/C belly contour",IF(B2<450:D2450:D2220,"6400",)))))

      =IF(AND(B2<340,D2340,D2<160), "Verify A/C belly contour", "") & IF(AND(B2<450,D2450,D2220), "6400", "")

      But I cannot manage it to work. Could someone help me?

      Thank you!

    • Dear Maam,

      I have a value in cell(kw of motor) for that value we have different tables according to No. of Poles for that i have written following formula...

      = vlookup (p7,if (p3=2,a1 to d4,3)
      if (p3=4,g2 to j4,3)
      if (p3=6,g2 to j4,3)
      but didn't work.

    • i am a dealer..

      i sell cars on installments
      i need a formula in excel which automatically due the amount after due date.

      3 years installment
      first installment: 49000
      2nd installment: 50000
      3rd installment: 50000
      every month installment: 8000
      33 months installment plan
      half year installment: 45000
      last installment 66000

    • is this formula is correct?
      =IF((AND(ISTEXT(C6)="A",istext (D6)="YES")),"OK", "NOT OK")

    • Hi Sheethal C,

      your case is best candidate for vlookup function. It would be a better choice here than IF function. It makes the calculation much simpler and therefore less buggy.

      Paste the table with two columns for example in A1:B5 :

      1 1-5 days
      6 6-10 days
      11 11-20 days
      21 21-30 days
      30 greater than 30

      The following formula will give you the needed result:

      =VLOOKUP(D1;$A$1:$B$5;2;1)

      You just need to write any number in D1.

      • Sorry for a typing error, I sow it later. Please replace the "30" in the last cell of first column with "31".

    • Hi,

      I am trying to evaluate 2 conditions in adjacent cells to allow a status in the 3rd cell.

      Cell A1 value is either "Yes" or "No"
      Cell A2 Value is either "Good", "SomeIssues", "Poor" or "None"

      Based on these values I want Cell A3 to return the following:
      Yes+Good=Preferred,
      Yes+SomeIssues=Secondary,
      Yes+Poor=DONOTUSE,
      Yes+None=Monitor
      No=Evidence needed (regardless of the value of A2)

      Thanks,
      John

      I tried adapting some of the above but had an error "too many arguments"

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