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 27. Total comments: 4557

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

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

  2. 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")))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  19. 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")

  20. 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")

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

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

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

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

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

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

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

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

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

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

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

  32. Am developing an incentive and the entry level is 91 scoring 8% and the maximum is 253 scoring 15%. If one scores below 91 thats a zero and if they score more than 253 they earn 15%. What is the formula that will calculate anything between 91 and 253?

  33. Hi Alexander,
    Requesting you to please help me find the formula for the situation that we discussed the other day.
    Thanks,
    Jasmeet Singh

  34. Can someone assist please?
    I have the following

    A1 = Yes
    B1 = a Date
    C1 = a string of 6 digit number with a letter

    I want D1 to have the Value "Fully Verified" if all three cells in the row are filled.
    If either B1 or C1 is blank, the return value in D1 to be "Not Verified"
    If both B1 and C1 are blank, the return value in D1 to be "Not Verified"

    What formula will suit this argument and how can that be arranged to give the desired return value?

    Thanks ...

    1. Hello!
      The formula below will do the trick for you:

      =CHOOSE((A1<>"")+(B1<>"")*2+(C1<>"")*4,"NV","","NV","","NV","","FV")

      You can learn more about CHOOSE function in Excel in this article on our blog.

  35. Hi,
    Please help me finding the following:
    cell A2 contain6/2/21 7:14 PM
    If the value for A2 is between 7:30 PM to 10:30 PM and the day of the week is Tuesday, then the value should be a text "ABCZYZ"

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

      =IF(AND(WEEKDAY(D1,2)=2,(D1-INT(D1)>19.5/24),(D1-INT(D1)<22.5/24)),"ABCZYZ","")

      You can learn more about WEEKDAY function in Excel in this article on our blog.

      1. Hi Alexander,
        Thank you so much for the swift response.
        I need help with another formula. There are 2 sheets in excel:
        Sheet1 :
        Date start time End Time Analyst
        1/5/2021 7:30 13:30 ANALYST1
        1/5/2021 13:30 16:00 ANALYST2
        1/5/2021 16:00 18:30 ANALYST3
        1/5/2021 18:30 21:00 ANALYST4
        1/5/2021 21:00 0:00 ANALYST5
        1/5/2021 0:00 3:00 ANALYST6
        1/6/2021 7:30 13:30 ANALYST1
        1/6/2021 13:30 16:00 ANALYST2
        1/6/2021 16:00 18:30 ANALYST3
        1/6/2021 18:30 19:00 ANALYST4
        1/6/2021 21:00 0:00 ANALYST5
        1/6/2021 0:00 3:00 ANALYST6
        Sheet 2:
        1/5/21 5:32 AM
        1/5/21 3:39 PM
        1/5/21 3:45 PM
        1/5/21 7:05 PM
        1/5/21 8:51 PM
        1/5/21 10:47 PM
        1/5/21 12:57 AM
        1/6/21 5:05 PM
        1/6/21 7:53 PM
        1/6/21 9:13 PM
        1/6/21 11:50 PM
        1/6/21 5:53 PM
        1/6/21 11:36 PM
        1/6/21 6:11 PM
        1/6/21 6:15 PM
        1/6/21 7:03 PM
        1/6/21 7:03 PM
        1/6/21 7:03 PM
        1/6/21 7:03 PM

        I want a formula to find out the name of the analyst who is responsible as per sheet 2 data. For instance, 1/5/21 3:39 PM incident is for Analys2 or 1/6/21 9:13 PM should pull up Analyst 5. In other words, if the date in sheet 2 matches the date in sheet 1 then the time in sheet 2 for the same date should look up the value in start time and end time of sheet 1 and populate the value for the Analyst.

        Thanks,
        Jasmeet Singh

          1. Hi Alexander,
            Thank you so much for sharing the formula.
            I have tried but it is not giving me value as N/A. Can you please share the complete formula.
            This would be of a great help as I have some deliverables and need this urgent. This will be of a great great help.

            Thanks,
            Jasmeet Singh

            1. Hello!
              An NA error means that the required value was not found. The IFERROR function can be used.

              =IFERROR(INDEX(D1:D12,MATCH(1,(INT(F1)=A1:A12)*(F1>A1:A12+B1:B12)*(F1<A1:A12+C1:C12),0)),"")

              Please check out this article to learn how to use IFERROR in Excel.

              Also, note that the time interval is incorrect:
              1/5/2021 21:00 0:00
              00:00 is the beginning of the day. The interval from 21:00 to 00:00 is nonsensical. Use 21:00 23:59

              1. Hi,
                I have put all the values given above in one sheet from column A2 to F12 and applied the above given formula in G2, however no result is displayed. The column F contains both date and time, such as 1/5/21 5:32 AM. Please see the formula below:

                =IFERROR(INDEX(D2:D12,MATCH(1,INT(K2)=A2:A12)*(K2>A2:A12+B2:B12)*(K2<A2:A12+C2:C12),0),"REVIEW")
                It only shows "REVIEW".

                Please help to fix this.

                Thanks,
                Jasmeet Singh

              2. Hi!
                The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. Hence, the formula fails to work. I'm sorry for the wasted time. When I have more time, I will think of another formula.

            2. Hi Alexander,
              Apologies to pester you.

              To add to this, the Sheet 2 has 2 columns not 1:
              Column1 : Date
              Colum 2 : time

              Thanks,
              Jasmeet Singh

  36. Hi,

    I want to ask I need to sum A2 + B2 and the total in column C2 and if the total C2 is more than 100 it will display the balance in column D2.

  37. I read through this article and came up with this for the project I'm working on. But it's not working. Please let me know what I need to do to fix this.

    =IF(AND($A2=”Conduct”,$F2="Final"), "Location 01", IF(AND($A2=”Development”,$F2="Final"), " Location 02", IF(AND($A2=”Development and Amendment”,$F2="Final"), " Location 03")))

    Thanks,
    Betty

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. The formula contains no errors. But I cannot check its work, sorry.

      1. Hello again,

        I got it to work! Thanks so much for publishing this article.

      2. Do you need to see the excel file? If so, how do I send it to you?
        Thanks

  38. Hey Hi,

    i have some doubts,

    if john and peter 2 peoples is available

    john is 10
    peter is 10

    if the name is john i need +2 for example 10+2 = 12
    if the name is peter i need +4 for example 10+4 = 14

    wherever i see john name is 12 instead of 10
    wherever i see peter name is 14 instead of 10

    i need to know the formula... please help me

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

  39. I have a formula that I want to make more specific
    =IF(AND(AM5="WASE-AH", (OR(U5="OVERSEAS/INELIGIBLE",U5="PRISONER", U5="T.A.C.", U5="VETERANS AFFAIRS", U5="WORKCOVER")),P5="4"),"Check", "Ignore")

    but I want to add for each item another set of criteria against cell F5 to check
    If u5= "overseas/ineligible" and F5= I then ignore, else check.
    If u5= "PRISONER" and F5= PR, then ignore, else check and so on for all the claim types.
    P5 still also needs to =4

  40. Hello!

    I'm trying to use the following formula for a commision function but I've obviously got something wrong. Can you please help me? Thanks so much!

    =IF(H4>=25%,10%,IF(H4>=30%,15%,IF(H4>=35%,20%,IF(H4>=40%,25%))))

    1. Hi!
      The formula below will do the trick for you:

      =IF(H4>=40%,25%,IF(H4>=35%,20%,IF(H4>=30%,15%,IF(H4>=25%,10%))))

      1. Thanks so much!!

  41. Hi, I am using a "if" and "and" combination but I'm not getting it right please help me with this.

    If(and(A1>0,b1>0),(average(A1,b1)),(A1,b1)

    How do I correct the false section of this statement if there are options to choose from two alternatives for that

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

      =IF(AND(A2>0,B2>0),AVERAGE(A2:B2),MAX(A2:B2))

    2. A1=4 B1=5
      A2=6 B2=0
      A3=0 B3=3

      So basically I want to find the averages of these two columns such that cells containing zeros are not ignored. For example the average for A1 and B1 is 4.5. Ideally the average of A2 and B2 will be 3 but I want Excel to write 6 instead and then 3 for A3 and B3 and not 1.5

  42. Hello,

    I've tried the formula below however the forth IF doesn't give a logical answer. Would you please assist:

    =IF(G4<5,H4,IF(G4=10,H4+4,IF(G4>=15,H4+6,))))

    G4 = years of seniority
    H4 = annual leave entitlement
    My objectif is to increase 2 days of leave entitlement every 5 years

  43. Hi,

    Would like to calculate some valuse.

    Example day counts the person one who resolves the calls in counts per day ranges is like mentioned below

    1. 1 calls =Rs.75

    2. 2 calls=Rs.150

    3. >3 calls =Flat Rs.300

    Blanck should not consider.

  44. Create a column called “Credit”. If the “Score” of the customer is “less than” 40, then give a “Poor” value in the Credit column. If the “Score” of the customer is “equal to” or “higher than” 40 but “equal to” or “less than” 70, then give an “OK” value. If the “Score” of the customer is “higher than” 70 but “less than” 90, then give a “Good” value. If the “Score” of the customer is “equal to” or “higher than” 90, then give an “Excellent” value. You must use a lookup function WITH an ARRAY Form for this column. Be sure that you implement the EXACT cutoff here using a range of scores with no more than 4 rows. No points will be given for using IF function.

      1. can you please help me to find a formula for this question.

        this what the professor said.
        "You must use a lookup function WITH an ARRAY Form for this column. Be sure that you implement the EXACT cutoff here using a range of scores with no more than 4 rows."

        1. Hi!
          Try the following formula:

          =VLOOKUP(B2,{0,"Poor";40,"OK";70,"Good";90,"Excellent"},2,1)

          This is Vlookup for approximate match

  45. I have a list of names and emails, i need to confirm with a yes or no if the person paid some fees

    im using this formula for the names in one cell:
    =IFNA(IF(VLOOKUP(A14,Table1[[#All],[Name of Person]],1,0)=A14,"yes","no"),"not found")

    and this one for the emails in another cell:
    =IFNA(IF(VLOOKUP(B14,Table1[Email],1,0)=B14,"yes","no"),"not found")

    how can i make it just one formula in one cell, instead of 2 formulas in 2 different cells?

    thank you so much!

    1. Hello!
      Try this formula

      =IFNA(IF(VLOOKUP(A14,Table1[[#All],[Name of Person]],1,0)=A14,”yes”,”no”),IFNA(IF(VLOOKUP(B14,Table1[Email],1,0)=B14,”yes”,”no”),”not found”))

      1. thank you so much!!! it works :)

  46. working days lessthan 26day insentive value is "0"
    working days Morethan 26day to 27 days insentive value is "40" per day Total value (26*40=1040),(27*40=1080)
    working days Morethan 27day to 31 days insentive value is "40" per day Total value (28*60=1680),(29*60=1740),(30*60=1800) and (31*60=1860)
    I want Formula

  47. Hello Expert,

    I need a formula with support following terms...

    if i press a digit between 0 to 5000 then we received result "1"
    5001 to 10000 = "2"
    10001 to 15000 = "3"

    200001 to 205000 = "41"
    2000001 to 2005000 = "401"

    5000 difference

    =IF(C6<5000,"1",IF(C6<10000,"2",IF(C6<15000,"3",IF(C6<20000,"4","5".........................infinity))))

    I need Solution of infinity

    kindly help

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

      =INT(A1/5000)+1

      You can learn more about INT function in Excel in this article on our blog.

  48. I have been working on this for a while and I know I am trying to overcomplicate but I cannot get this formula to cover the one oddball instance.
    I have 3 columns B is my deal credit which will be either 50 for a split deal and 100 for a full deal. So if the deal is 50, the max amt to be paid is 100.00, if it is 100, the max paid is 200.00. Column C is the commission amt that has already been paid, so therefore if the deal is a split deal (50) and they have been paid 50.00, they are paid the difference which would be 50.00 which will go in column C. If the deal is a full deal (100) and the comm amt is 100.00 the amt paid would be 100.00 , the difference between the amt paid and the max amount.
    Here is my formula: =IF(C4>=200,0, IF(B4=50,100-C4, IF(B4=100,200-C4))) It seems to work for them all except the one oddball instance where my comm amt for a half deal is 109.10, so it is leaving my column C with a minus amount of -9.10 which I would like to show up as a 0.
    Thanks so much for your help. I hope I am not too confusing.

    1. Hello!
      I didn't quite understand your calculations, but I suggest this formula

      =IF(IF(C4>=200,0, IF(B4=50,100-C4, IF(B4=100,200-C4)))>0,IF(C4>=200,0, IF(B4=50,100-C4, IF(B4=100,200-C4))),0)

      I hope it’ll be helpful.

      1. That worked! Thank you so much!

  49. What is formulas for incentive calculation contains of this condition
    <26day up to 28days rs40perday and 26 days incentive is "0")

  50. Hello Alexander,

    I'm afraid I have a similar problem to my previous SUMIF one further up the page, but with text instead.
    I'd like to count the number of cells that have specific text, but only if a cell on the same row also has specific text.

    In this case, I'd like to count the number of cells in column B that contain "Category M", but ONLY IF a cell in the same row in column G also contains "Yes". Is this clear enough?

    Because it's text, I suspect SUMIF won't be applicable here. Any help is appreciated.

    1. Hello!
      The formula below will do the trick for you:

      =COUNT(IFERROR(SEARCH("Category M",B1:B10,1),"")*(--(G1:G10)="Yes"))

      This is another way to solve your problem

    2. I think I've solved the problem using COUNTIFS and multiple criteria. Thank you for all the help and advice you've given to everyone!

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