Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 26. Total comments: 4555

  1. Hello, i want to make a formula that shows me a number of 13 rows based on two criteria. One of them is
    " =IF(M3="monofazat",(R10) " and i want to put that 13 rows in " R10 " place. In the same row i want to make another criteria that sound like this " =IF(M3="trifazat",(T10) "and also shows e another 13 rows. When i write in "M3" this texts ( "monofazat" or "trifazat") i want that rows to appear in the one i write the formula. I want to make some ofertation table, to be more simple to make an offer to a client. It's possible? Thanks, i m waiting for an answer!! Have a nice day!

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(M3="monofazat",A2:A15,"")

      If this is not what you wanted, explain in more detail, write an example of the result.

  2. There will be a given data of a person with their Height, Weight and Mid upper arm circumferance , from that we have to derive whether the child is Severe Accute Malnutrition or Moderate Accute Malnutrition or Normal or Obese. What will be the formula in Excel . Length (cm) "SAM
    < –3""MAM ≥ –3 to +2 to ≤ +3" "Obesity> +3"
    Weight (kg)
    45 0–1.8 0–1.9 1.9 2.0–3.0 > 3.3
    46 0-1.9 2.0–2.1 2.2–3.1 3.2–3.5 > 3.5
    47 0–2.0 2.1–2.2 2.3–3.3 3.4–3.7 > 3.7
    48 0–2.2 2.3–2.4 2.5–3.6 3.7–3.9 > 3.9
    49 0–2.3 2.4–2.5 2.6–3.8 3.9–4.2 > 4.2

    1. Hi!
      The result you want to get me is incomprehensible. But recommendations from this article will be useful for your formula.

  3. I need a formula that can look at value in B column (there will be up to 10 different values in this column)
    and return a different result in the next cell of column C IE: cell b1 = text then c1 = text2

  4. Hi There!
    Looking for some help.

    I have a scoring card and need to calculate points on a ranking scale. What would be the correct formula for the below.

    Score is 20 or less receive top points, if score is higher than 20 subtract point. (-2)

    =IF(D920,$F$34,(D9*F$34)/20-2))
    OR
    =IF((D820,$F$34,(D8*F$34)/20),-2))

    Thanks

    1. Hi!
      Unfortunately, without seeing your data it is impossible to give you an exact formula. Your conditions are not entirely clear to me. But you can do this if you carefully study the instructions in this article above.

      1. Hello,

        We need to check value of two column and IF BOTH ARE POSITIVE "LB",if 1st is negative and 2nd is positive "SB",if both are negatie "LU" else "SC")
        1st 2nd
        -1.62% 169.90% LB
        2.01% 44.29% SB
        -0.78% 15.46% SB
        -0.74% 7.80% SB
        -0.10% 4.16% LB
        1.95% 2.91% SB
        -0.19% -1.32% SB
        0.89% 1.92% LB
        3.11% 1.33% SC
        -1.47% -7.96% SC
        2.25% 3.83% SC
        -0.85% -2.97% SB
        0.41% -3.17% SC
        0.05% 0.42% SC
        -0.07% -3.88% LU
        -0.61% -3.52% SB
        0.49% -2.44% SC
        -0.95% -0.84% LU
        -2.37% -5.69% SC

        Please help me with correct formula.

        1. Hello!
          I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

  5. I want to put the "statements in column 2" when I select "items in column 1" from menu bar in another sheet.

    Column 1 Column 2
    item1 sample for statement1
    item2 sample for statement2

    I tried pivot table, but it provides statement upto some extent, I need to display the whole statement in column 2, no matter how bigger it is. Please help!

  6. I had read all the examples, and maybe I miss it but I can't figure it out a formula:

    If the value of cell A1 is equal or more than 10% the amount will be $500.00 but if the same cell is less than $500.00 it will be 10% less. Can you please help me? I do appreciated

  7. Hi,
    I have a spreadsheet with a Table tab and a Data tab. In the Table I have a persons name and then columns for each different inspections that are completed - I'm trying to work out whether a VLookup or a some sort of IF or SUMIFS variation can be used to search the information in the 'Data' tab. Colum C in Data is the names and D is the type of inspection. The Data is C3 : C20 (names) and D3 : D20 Inspection type (example 'Assurance - Task Inspection') I need the table to have = QTY of John Smith's Task Inspections. Ideally I would like to include in the formula 'name' and 'inspection type' as the report evolves and we include more people or inspections all that would need to be changed is the formula 'name' reference or 'inspection type' reference.
    Note: The data tab will be copied from a system report and pasted into those rows everytime

      1. Thanks Andrew,
        I managed to work out a SUM and COUNTIF combination and trial and error the formulas until it worked out.

  8. Thank you very much guys, this article was very helpful in getting me to complete an urgent caluclation.

    well explained and simple.

    Many thanks

  9. Hello ,

    Could you please help me on the below formula? I'm trying to define the below rule:
    The formula that I have defined is this =IF(K2>=I2,"Achieved",IF(K2<I2,"Not Achieved"))
    but I need to add another condition so if the method of meeting colum2 is Phone Call whatever value it might have not to be calculated as Achieved.

    2 Meeting 1 Not Achieved
    1 Meeting 0 Not Achieved
    6 Meeting 6 Achieved
    6 Meeting 6 Achieved
    6 Meeting 2 Not Achieved
    6 Meeting Not Achieved
    1 Meeting Not Achieved
    0 Phone Call Achieved

    1. Hello!
      If I understood correctly, add the condition to the formula

      =IF(J2="Phone Call","Achieved",IF(K2>=I2,"Achieved",IF(K2<I2,"Not Achieved")))

  10. Hi, i have been trying very hard to display the length of service for some vouchers. Appreciate your help!

    Category > = 2 Years < 2 Years
    Manager 70.00 50.00
    MT & ASM 50.00 40.00
    FT SA 40.00 30.00
    PT SA 30.00 20.00

    and it depends on the category and less than 2 years or more than 2 years.

  11. Hello I need a formula that can display multiple argument
    For example let say the display cell is C1 and what to be displayed in it is in relationship with other two cell let say A1 and B1 such that if A1 is IP and and B1 is 1.0 then C1 is 0.036 but A1 has 9 different possible conditions while B1 has 8 different possible conditions.

    1. Hello!
      This information is not enough to write a formula. However, you will be able to describe all the conditions in the formula if you carefully read the recommendations in the article above.

  12. Hello,
    I'm trying to come up with a formula that will give me the same return as the following, but include more room numbers and more sections of the facility:
    for example this formula in column A returns "North" or blank "=IF(AND(B14>209,B14<227),"North","")"

    What I would like is to have the following:

    If the value in Column B = between 202-208, and 232, Column A = North East
    If the value in Column B = between 210-226, Column A = North
    If the value in Column B = between 234-242, and 258-274, Column A = East
    If the value in Column B = between 244-256, Column A = West
    If the value in Column B = between 280-294, Column A = South

    I don't need it to return blank.

    I thank you ever so much for any help you can offer!

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

  13. If value of C2 (in excel cell) is 210000. Take value greater than 150000 but less than 200000 in C5 (in excel cell) and if value of C2 is equal to 150000 or less than 150000 take as zero.
    Kindly enlighten with formula.

  14. Hello guys
    I have a problem that i cant solve for days. Can't find an answer on web. So here is what i wanna do;
    There are almost 250 pieces of products i have and all have names and codes
    (e.g name:15cm silver furn. leg and code: ob0001)
    I want to enter the name of one products in a random cell and excel will automatically enters the code in the next cell.

    Its all but i cannot make it work. im about to smash my computer (:

    Is there a solution? Please help me!
    thanks in advance

    1. Hello!
      If the product name and code are in a separate table, you can use the VLOOKUP function to search that table. Your search for the name gets the corresponding code. Or vice versa.

      1. Thank you very much.. Im gonna try this function.

  15. I'm looking for a function that will change the end date based on the number of days delayed. However, it's not as simple as merely adding the number of days to the start date. There are two stakeholders involved such that if stakeholder "A" has a delay of 30 days, stakeholder "B" or the result is only impacted by 12 days. Would you please suggest a function for this and tell me how to use it? Thank you.

    1. Hello!
      I am not sure I fully understand what you mean. Explain in more detail, give an example of the source data and the desired result.

  16. Please enlighten the excel formula to calculate following problems:-
    If value of C2 (in cell) is greater than 150000 than takes value greater than 150000 but less than 200000 and if value of C2 is equal to or less than 150000 ignore it.

      1. Thanks for speedy replied.
        My question is that employees are submitting Income tax saving documents such as Rs.100000, Rs.134000, Rs. 200000, Rs.260000, etc. varies from one employee to other. Exemption limit admissible is Rs.150000 only irrespective of saving documents submitted by them under particular section. If employees submitted saving documents for Rs.120000, the whole amount is exempted as it is less than Rs.150000 (maximum permissible exemption) but if employee submitted saving documents for Rs.210000, his exemption is limited to Rs.150000 as per Rules. However, saving documents beyond Rs.150000 subject to a maximum of Rs.50000 can be exempted under another/difference section (beyond 150000 upto 50000). As the employee submitted saving documents of Rs.210000, he can get additional exemption of Rs.50000 in another section and excess amount of Rs.10000 to be ignore.
        My question is that an employee has submitted saving documents amounting to Rs.240000 and maximum amount of Rs.150000 is exempted in particular section and amount exceeding Rs. 150000 is to be exempted in another section subject to the limit of Rs.50000 only and rest amount to be ignore. However, if saving document submitted by employee is less than Rs.150000 the same to be ignore for exemption in another section. (If the value of C2 (say Rs.240000) is greater than 150000 takes value greater than 150000 but less than 200000 (i.e Rs.50000 maximum admissible) in another cell and if value of C2 is less than (say 140000) or equal to 150000 (say 150000) ignore or takes zero).
        Hope you will assist me in solving problems as the formula is to applied for more than 500 employees.

        1. Hello!
          I can help you write the formula if you give an example of the source data, the result, and accurately and briefly describe all the conditions. I don’t have the time or opportunity to try to understand the financial instructions. According to your description, I cannot help you.

          1. Thanks. Let be clear the question:-
            Say Value of C2 in Excel is 240000 but maximum limit in C2 is only 150000.
            Value exceeding 150000 in C2 is to be taken in C5 but to the condition that maximum limit in C5 is 50000 and if value in C2 is less than or equal to 150000, the same to be ignore or taken as zero in C5.
            Formula to be apply in C5 uniformly irrespective of amount of value in C2.
            Hope you are clear of my question and assist me by providing the formula.

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

              =IF(C2-150000>=50000,50000,0)

  17. sir,

    pls help

    IF total of B4=1 then, appear the total of column D7 ( whatever it is.....)

    Thanks,

  18. I'm trying to create a nested IF function that includes an AND condition but get an error that says there are too many arguments. Is there any way to create a nested IF that includes an AND?

    Here are the three conditions/formulas I would like to combine to create a single output in one Status field (the word "blank" is just a placeholder so that I can see the results):

    if F2 is not blank, then return "Done":
    =IF(F2"","Done", "blank")

    if F2 is blank AND E2 is greater than 10 days, then return "2nd call":
    =IF((AND(F2="", TODAY()-E2>10)), "2nd Call", "blank")

    if D2 is blank, then return "1st call":
    =IF(D2="", "1st Call", "blank")

    Here is what I think the full formula should look like:

    =IF(F2"","Done", "blank", =IF((AND(F2="", TODAY()-E2>10)), "2nd Call", "blank", IF(D2="", "1st Call", "blank")))

    Here is a screenshot of sample data:

    https://drive.google.com/file/d/1UtB_MMrfM25MyDX-NFfqw_Zroxopdcj1/view?usp=sharing

    Thank you so much for any assistance you can provide. I have learned so much from your website. I really appreciate the hard work you've put into making these formulas easier to understand.

    1. Hello!
      Perhaps this formula will work for you

      =IF(F2<>"","Done", IF((AND(F2="", TODAY()-E2>10)), "2nd Call", IF(D2="", "1st Call", "blank")))

      1. Ah, I see what I did wrong by including the "blank" output. That does help. Thank you so much for your assistance.

  19. I need help calculating an Average for multiple subjects in one row
    e.g.
    Student Maths Science Geo

    Peter 80 80% 0 0% 78 78%
    John 50 50% 78 78% 0 0%
    Sue 60 60% 0 0% 80 80%

  20. I am trying to add an SumIF statement to my sum.

    If the Sum of A1:A4, is greater than 100, only display 100.

  21. Hello,

    I hope someone can help me with this.
    If cell C is not blank, result should be "Done"
    If the cell is blank and cell B and cell A result should be the following:
    If 15 Day Difference, "Too early"
    If 30 Day Difference, " Needed"
    If 60 Day, "Cancelled"

    Here is the data:
    Cell C:

    Policy Effective

    02/16/2021
    02/11/2021
    02/11/2021
    01/22/2021

    Cell A & B
    Cell A Cell B
    02/18/2021 02/16/2021
    02/17/2021 02/04/2021
    02/12/2021 02/06/2021
    02/12/2021 02/06/2021
    01/22/2021 01/18/2021
    03/01/2021 02/27/2021

    Thank you so much

    1. Hello!
      Please check the formula below, it should work for you:

      =IF(C1<>"","Done",IF(B1-A1<=15,"Too early",IF(B1-A1<=30,"Needed","Cancelled")))

  22. Hi,

    many thanks for the detailed tutorial here. love it!

    i have a1 linked to a drop down list from a separate worksheet.

    i want my m1 to reflect a price based on the item selected from the list in a1 (list of prices also from a table in separate worksheet)

    eg. if a1 is selected as apple, I want this to be reflected in m1. if it is selected as a orange, i want it to be reflected as such in m1.

    how would i do this please?

    many thanks in advance!

  23. Hello,

    I'd like to create a formula for the following, but I don't know how to do it.

    If status in column F is "Draft", then use the File Path in Column I to enter the file name in that folder into Column J called 'File Name'.

    Can this be done with a formula? Or is VB needed?

    Any help is greatly appreciated.

    1. Just to give more information on this. The excel worksheet lists information to track a large list of documents. Each document is kept within its own folder. The worksheet lists a lot of data for those documents including the document's status and its File Path.

      Here is the issue: Even though there is only one document in that folder, the file name of the document changes daily. So instead of updating the file names manually (which is tedious and time consuming), I'd like to use the Status column and the File Path column to automatically update the file names of those documents.

  24. Hi there,
    Im trying to return Status (to return Not Started/ Ongoing/ Done/Delayed) of a Task based on starting date, due date and today. I am stuck on adding condition for "Delayed", when Due date has been postponed.

    Today = L2
    Start Date = E3
    Due Date = F3

    I have tried using the OR formula as well, but I cannot add a 4th condition for delayed:
    =if((or(F3>L2="Delayed",E3&F3="",F3<L2)),"Done","Ongoing")

    Could you kindly assist on this question? Thank you in advance!

    1. Hi Diana,

      To check multiple conditions and output different results depending on which condition is TRUE, use nested IF functions:
      =IF(F3>L2, "Delayed", IF(AND(E3="",F3=""), "Done", IF(F3<L2,"Ongoing", "")))

  25. How to Calculate if there are 5 parties having different price how to know the which of the party has less price showing the party name

  26. Hello, this seems obvious yet I am stuck and can't find the best way to apply it in excel - pls help:

    I have two different IF functions that are working well each on its own:
    - Function1: =IF(D3="A",'Assumptions Sheet'!$O$10,IF(D3="B",'Assumptions Sheet'!$P$10,IF(D3="C",'Assumptions Sheet'!$Q$10,0)))
    - Function2: =IF(D11="A",'Assumptions Sheet'!$O$9,IF(D11="B",'Assumptions Sheet'!$P$9,IF(D11="C",'Assumptions Sheet'!$Q$9,0)))

    Question: Now, I want to combine them within another IF statement - If CELL A3 = "H" then return the result of Function1 ; If CELL A3 = "L" then return the result of Function2 .

    Thank you.

  27. Help pls. I need a formula for:

    EX: I have a number in cell B1 and if the number is 2, then transfer the number in cell A1 to H1,
    This goes on for 45 numbers. Ex: If number in cell B2 equal 5, then transfer number in cell A2 to H5. etc.

    Can anyone help pls?

  28. I'm having a tough time trying to write a formula where 2 conditions must be true, then using the value from a third formula.

    Sheet -1 (google quiz)
    Score Name Student # Class #
    10 Jim 5 6/1
    6 John 5 6/2
    6 Greg 5 6/3
    9 Tom 5 6/4

    So take the above table for example. That would be the sheet that's linked to a google form and it populates as the students take the quiz. No, I have 4 different classes, 1 sheet for each class. I'm having a hard time writing a formula where say, "condition 1 = 6/2", "Condition 2 = 1 (the student number)", and if both of those conditions are met, then the "score" is automatically populated on the roster sheet.

      1. Okay, that helps. However, I'm still having difficulty getting the score as the value.

        So I have multiple sheets that are pulling data from 1 sheet where all my google forms are populating. The google form has a cell "score (1-20)", a cell with the "student numbers (1-40)" and a cell for the "class". However, I have a sheet for each class (6/1 - 6/4), with 40+ students in each. The =AND(logical_expression1, [logical_expression2, ...]) formula highlights those cells, but I can't get it to pull the actual score (from the score cell) when both conditions are met.
        =IF(AND(F1:F100="6/2",E1:E100="1") F1:F150 being the class, E1:E150 being the students number, because each class has students numbered 1-##, there is overlap, therefore I need to have both class and student number to be true, if both of those conditions are met, then I need it to pull the score (1-20) from that specific row.

        1. If you refer to multiple cells at once in your formula (F1:F100 rather than F1), you should also wrap it in the ARRAYFORMULA.

          If you're still not sure how to make it work, please consider creating a sample spreadsheet with the following tabs: (1) a couple of sheets with 10-20 lines from your forms (if there's confidential info, replace it with some data but keep the format), (2) an example of the result you want to get.

          Share this spreadsheet with us: support@apps4gs.com, I'll look into it.

          Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  29. Hello.
    Please I have issue with printing all carry over courses for a particular semester. It always give me the first carryover course and ignore the rest. I used the if nested conditions...
    Please what could be the reason.
    Thank You.

  30. Hi there,

    I have an issue that is along these lines but not precisely what has been discussed thus far...

    I am needing to add values together based on two other conditions being met,
    for example

    If A = 5 and B = 8 add 1 to C
    this was the formula I came up with for the above:

    IF({{a}} = 5 AND {{b}} = 8, VAL('c') + 1, VAL('c'))

    Now my issue is coming in where I need to add multiple criteria to C...
    This formula below does not work but I've used it to explain the thought process

    IF({{a}} = 5 AND {{b}} = 8, VAL('c') + 1, VAL('c')) + IF({{D}} = 5 AND {{E}} = 8, VAL('c') + 1, VAL('c')) + IF({{F}} = 5 AND {{G}} = 8, VAL('c') + 1, VAL('c'))

    Could anyone assist in a correct formula that could work. Based on the above (incorrect) formula I am trying to reach a total of 3 in C.

    Thanks in advance for the brain power.

  31. I'd like to look at 2 cells (J9 and BH9) to see if there is anything in either of them. If something in either cell then show the value in AN9. If nothing is in J9 or BH9, then the IF statement would be skipped.
    I tried several variations of this...
    IF(OR(J9=””,BH9=””),””,AN9)

    1. I figured it out after much trial and error.
      IF(AND(OR(J9=""), OR(BH9="")),"",AN9)
      I'm grateful that I found this website.

  32. I want to use the countif statement with two conditions, how do I do this?

  33. Hi,
    Im trying to set a condition where if it says the word "Conns" the answer will be "600" number and if it says the word "MDO" show "570".
    This is one of the formulas I have try so far:
    =IF((D587=Conns,MDO),"600","570")

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      =IF(D587="Conns",600,IF(D587="MDO",570,""))

      1. I was using "" wrong, but thank you for your help.

  34. Hello,
    Is there a way to use an IF or IFS formula if you have more than 127 conditions?

    1. Hi!
      You see, there is a limit in Excel that defines how deeply the IF function can be nested. For example, Excel 2007 allows 7 levels of nesting only. Starting from Excel 2010 you may nest up to 64 Ifs. Office 365 increased this limit even more - to 127.

  35. I need help with formula.

    Sheet 1
    Prdtucts Name Buy/sell Qty
    Bed BedBuy 100
    Chair ChairBuy 200
    Table TableBuy 50
    Sofa SofaBuy 300
    Park bench Park benchBuy 440
    Coeffe Tabel Coeffe TabelBuy 602
    Bed BedSell 20
    Chair ChairSell 30
    Table TableSell 12
    Sofa SofaSell 120
    Park bench Park benchSell 40
    Coeffe Tabel Coeffe TabelSell 205
    Table TableSell 30
    Park bench Park benchSell 250
    Chair ChairSell 105
    Tabel TableBuy 45

    I want a formula to find out the stock of each item in different sheet.
    Thanks,
    Sandip Dhakal

  36. Hello, would you be able to help me with this.

    For example I have your conditions (which consist of two different statements) and 4 different answers for each.

    For example:

    I have
    A1=no, B1=short
    A1=no, B1=long
    A1=yes, B1=short
    A1=yes, B1=short

    Both conditions must be met:

    If No/short - formula (E1-D1)*C1
    If no/long - formula (D1-E1)*C1
    If yes/short (D1-G1)*C1
    if yes/long (G1-D1)*C1

    All this needs to go to one cell, because there are 4 outcomes possible. How it needs to be written in one cell?

    I am confused.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =CHOOSE((A1="no")*1+(A1="yes")*2+(B1="short")*3+(B1="long")*5, "","","",(E1-D1)*C1,(D1-G1)*C1,(D1-E1)*C1,(G1-D1)*C1)

      You can learn more about CHOOSE function in Excel in this article on our blog.
      I hope I answered your question.

    2. correction

      A1=no, B1=short
      A1=no, B1=long
      A1=yes, B1=short
      A1=yes, B1=long

  37. resending
    Hi, please help to correct my formula: =IF(A2-B2)>=500, "SAFE", IF(A2-B2)>=400,"AVERAGE", IF(A2-B2)500 - SAFE
    (A2-B2)>400 - AVERAGE STOCKS
    (A2-B2)<400 - LOW STOCKS

    Thank you!

    1. Hi!
      Please re-read the article above, it covers your case completely.
      Your conditions contradict each other:
      IF(A2-B2)>=400,”AVERAGE”
      (A2-B2)>400 – AVERAGE STOCKS

  38. Hello,

    Looking at the examples of IF AND and OR on your page I'm trying to create a formula that displays whether a call was made during an evening and weekend call plan.

    Evenings would be 7pm-7am
    Weekends would be 7pm Friday - 7am Monday

    If the call was made during Evenings/Weekends I'd like to display Yes in column E otherwise display No.
    Column D is what I would expect to see.

    I've created the following two parts of the formula which work on their own but I don't know how to create 1 formula that includes all the rules for evening and weekend calls .
    =IF(AND(C2"Sat", C2"Sun"),"No", "Yes")
    =IF(AND(B2>=TIMEVALUE("07:00:00"),B2<=TIMEVALUE("19:00:00")), "No", "Yes")

    I've manually entered values into the expected output column. Apologies if the column headings don't line up.

    A B C D E
    Date Time Weekday Expected output Covered by evening and weekend call plan
    02/08/2021 14:11:11 Mon No
    31/07/2021 12:37:10 Sat Yes
    31/07/2021 16:52:23 Sat Yes
    30/07/2021 21:11:19 Sun Yes
    29/07/2021 20:25:36 Sun Yes
    28/07/2021 20:30:21 Wed Yes

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

      =IF(OR(WEEKDAY(A1,2)+A1-INT(A1)<(1+TIMEVALUE("7:00:00")), WEEKDAY(A1,2)+A1-INT(A1)>(5+TIMEVALUE("19:00:00"))),"Yes","No")

      Hope this is what you need.

      1. Hi Alexander,

        Thanks for taking a look at this and for your suggestion, unfortunately it didn't give me the result I was looking for.

        The conditions are a Yes if either Weekday=Sat or Sun, OR time of day for remaining days is =19:00

        Date Time Weekday Expected_output Alexander_solution_evening_and_weekend_call
        02/08/2021 14:11:11 Mon No Yes
        31/07/2021 12:37:10 Sat Yes Yes
        31/07/2021 16:52:23 Sat Yes Yes
        30/07/2021 21:11:19 Sun Yes No
        29/07/2021 20:25:36 Sun Yes No
        28/07/2021 20:30:21 Wed Yes No

        1. Hi!
          If I understand your task correctly, the following formula should work for you:

          =IF(OR(A1-INT(A1)<=TIMEVALUE("7:00:00"), A1-INT(A1)>=TIMEVALUE("19:00:00"),WEEKDAY(A1,2) > 5),"Yes","No")

  39. Hello,

    If D2 cell contains value which start from word "Root" and here i cannot use entire sentence of "Root " so
    if i have to use it in multiple if conditions how can use that?

    =IF(AND(A2)="Y", (C2)="n/a", (D2)="???","Met","Miss")

  40. If in a column range is Safdar and in next column his number is 6 which formula can tell me that if in that range Safdar exist then bring his number 6

  41. Hello! I am trying to solve for the correct way to use IF and VLOOKUP when:

    IF H5 refers to "Basic Monthly", "Plus Monthly", "Elite Monthly" (I'd like these to just result in "Monthly" for the VLOOKUP. Same format exits for Annual portion of the formula.

    VLOOKUP Table Example (monthly rate table):
    $1.00 $5,000.00 10%
    $5,001.00 $10,000.00 15%
    $10,001.00 $99,999.00 20%

    I've tried variations of the following:
    '=
    IF($H5="Monthly",
    VLOOKUP($L5,'Direct Sales Commission Tables'!$A$26:$C$28,3,TRUE),
    IF($H5="Annual",
    VLOOKUP($L5,'Direct Sales Commission Tables'!$E$26:$G$28,3,TRUE),
    IF($H5="NA",0)))

    Thank you for any guidance!

    1. Hello!
      Use instead $H5=”Monthly”
      formula

      ISNUMBER(SEARCH("Monthly",$H5,1))

      I hope it’ll be helpful.

  42. Hello,

    I would like to create a formula for three test conditions with dates, but I'm having trouble with adding the dates. This is the formula I would like:

    If test priority is "High" (A1), add one week from initiated date (B1) to due date (C1)
    If test priority is "Medium" (A1), add two weeks from initiated date (B1) to due date (C1)
    If test priority is "Low" (A1), add three weeks from initiated date (B1) to due date (C1)

    Thank you!!

  43. Im trying to do a formula to calculate for me result if I multiply the rate with the consumption but based on different ranges or slabs each range with different rate;
    1) if equal or more than 2000kwh calculate for me: x*rate 1
    2)if more than 2000 but less than or equal 4000 : x*rate 2
    3) if more than 4000 : x*rate 3

  44. Hello,
    May i know how to combine below formula in 1 formula? Thank you very much!

    =IF(ISBLANK(J1793),"",IF(WORKDAY.INTL(J1793,-4,1,holiday!A:A),WORKDAY.INTL(J1793,-3,1,holiday!A:A)))
    =IF(ISBLANK(J1800),"",IF(OR(WEEKDAY(J1800,2)=6,WEEKDAY(J1800,2)=7),WORKDAY(J1800,-5,holiday!$A:$A),WORKDAY(J1800,-4,holiday!$A:$A)))
    =IF(ISBLANK(J1801),"",IF(OR(WEEKDAY(J1801,2)=6,WEEKDAY(J1801,2)=7),WORKDAY(J1801,-4,holiday!$A:$A),WORKDAY(J1801,-3,holiday!$A:$A)))

    1. Hello!
      The terms in these formulas are not related to each other. All of them can be performed at the same time. If ISBLANK(J1793), ISBLANK(J800) and ISBLANK(J1801) return TRUE at the same time, which condition will you fulfill? Therefore, your desire cannot be fulfilled.

  45. Hi I'm having trouble writing a formula, where if there's a date entered in cell AG2 for the text "Closed" to be entered automatically, and the same for AD2 and E2, but with differing text. Otherwise to leave the cell empty.
    But I can't seem to get it to work, as it returns an empty cell, even though there's dates in these cells.
    However for some reason, if I take the asterisk out of the quotations it'll enter the text for the cell without anything in (which shows it does kind of work, just not the way I want it to). I'm probably doing something wrong here, could you have a look and adjust it if possible?

    =IF(AG2="*","Closed",IF(AD2="*","Awaiting Sign Off",IF(E2="*","Open","")))

    Thank you

    1. Hello!
      Please try the following formula:

      =IF(AG2<>"","Closed",IF(AD2<>"","Awaiting Sign Off",IF(E2<>"","Open","")))

      I hope it’ll be helpful.

      1. Works perfectly. Thank you for the fast response.

  46. Is it possible to create an IF formula that writes different results (separated with a comma) in the same cell? for example, let's say
    A2 = #10 031
    A4 = #15 213
    A1= 0
    A4= 0
    If A1 is equal to "1" do not write anything but if A1 is equal to "0" then result is equal to A2
    If A3 is equal to "1" do not write anything but if A1 is equal to "0" then result is equal to A4
    If.... continues

    then the formula should write in the same cell =
    #10 031, 15 213

    I don't know if I am making myself clear xD, any master out there who knows if its possible?

      1. oh :c
        I will try to work around that,
        thanks a lot, you saved me a lot of time.

    2. My bad
      A2 = #10 031
      A4 = #15 213
      A1= 0
      A3= 0

  47. I am looking to solve this formula - where X is a variable (% performance)
    IF(X80100150,550000)))))

    what am I doing wrong?

    1. Sorry this was captured wrongly. Here are the conditions:

      % performance Award
      150% 550,000.00
      >100%80%<100% 250,000.00
      <80% -

  48. I need help to get an appropriate formula for my table.
    The table headers have A remark(Suppressed, Unsuppressed), B indicator(Routine, Targeted), C (Date of Result), and D (Next due date)
    I want the following to happen,
    1) If (A2) is Suppressed and (B2) is Routine, (D2) should add 11 months from (C2) date.
    2) If (A2) is Suppressed and (B2) is Targeted, (D2) should add 5months from (C2) date.
    3) if (A2) is suppressed and (B2) is either Routine or Targeted, (D2) should add 3months from (C2) date.

      1. Thank you, Alexander. You are a genius. This is the modification I made from the formula you gave me
        =IF(A2="SUPPRESSED", IF(B2=TARGETED", DATE(YEAR(C2), MONTH(C2)+5,DAY(C2)), IF(L6="SUPPRESSED", IF(B2="ROUTINE", DATE(YEAR(C2), MONTH(C2)+11,DAY(C2))), C2))).
        It works. However, my No 3 condition should be (A2) is "unsuppressed" and (B2) routine or targeted.

        1. Hi!
          If condition 3 is satisfied, then conditions 1 and 2 are satisfied. You cannot perform 3 actions at once.

  49. i want to fill cell variable values from 1 to 9 according to the many conditions
    i used this formula but not reflect result

  50. Hi Friends,

    pls can u help me to get the formula for example ive explained below

    if B2 cell value is greater than c2 cell value (good) or d2 cell value(very good) or e2 cell value (excellent)

    i want to get this good or very good or excellent comments in F2 cell

      1. thx for ur reply, just chked it but my point is i want to take cell values dnt need have particular numbers,

        eg:

        B3 cell value is 50, D3 cell value is 51, E3 cell value is 60 and F3 cell value is 61

        =CHOOSE((B3>=D3) + (B3>=E3) + (E3>=F3), "GOOD", "VERYGOOD", "EXCELLENT")

        So how to use cell value instead of any number

        1. Hello!
          I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

          =CHOOSE((B3>=D3) + (B3>=E3) + (F3>=F3), "","GOOD", "VERYGOOD", "EXCELLENT")

          If this is not what you need, explain what kind of result you would like to get in your example.

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