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 17. Total comments: 2538

  1. Hello

    I've read your guidance and created the following formula to achieve the following. Unfortunately it doesn't work. Any advice would be greatly appreciated.

    If L3 is empty return 'No Appt',

    If the date is L3 is in the future return 'Future Appt'

    and If both R3 is empty AND the date in L3 is in the past return 'No Report'

    =IF($L3="","No Appt", IF(L3>TODAY(),"Future Appt", IF(AND($R3="", L3<TODAY(),"No Report"))))

    1. Jonathan:
      Start building this IF And Statement from the inside and see where it breaks.
      Begin by putting IF($R3="", L3<TODAY(),"No Report", "T") in a cell. See if that will calculate.
      Then continue to build the formula piece by piece and you'll see where it breaks. When it does this may help you to determine what your goal is and how to accomplish it in another way.

  2. I have Two columns one for Ps1 and k12 , I want to calculate the value of k12 to comply with these three conditions:

    1- if Ps1<=10 then k12=1
    2- if 10<=ps1=20 then k12=200/(ps1)^2

    Thanks in advance for any help

    1. Nazar:
      The first condition looks to be something Excel can determine.
      The second condition is unclear.
      If PS1 is equal to 20 it can't be equal to 10 at the same time.
      Do you mean "IF PS1 is between 10 and 20"?
      Can you clarify the conditions?

  3. dear all

    pls advise for the excel formula for below condition

    i have two rate for multiple currency ,if amount is greater then eur 10000 the rate 71 other wise 72, if amount is greater then USD 10000 then rate 65 other wise 66.
    formula required for below task

    Currency 10 lacs
    USD rate 67.50 67.2
    Euro rate 82.01 81.65
    Pound rate 93.49 93.07

    Currency Amount Rate
    USD 100000.00 ?????

    1. Parkash:
      If this is the condition where currency in in column A the rates are in columns B and C, then USD dollar amount is in column B and the final amount is in column C this is what this looks like:

      Currency
      USD rate 67.5 67.2
      Euro rate 82.01 81.65
      Pound rate 93.49 93.07

      USD $100,000.00 $6,750,000.00

      The formula to achieve this is:
      =IF(B6>=10000,(B6*B2),(C2*B6))

  4. Would you help me remedy this formula?

    =IF(T2=3)AND(I2>1)AND((M2-AC2)>(I2*.7)),"N","Y")

    the second qualifier is my issue. T2 greater equal to or greater than 3 AND I2 greater than 1 AND Value of M2-AC2 is greater than I2*7, return N, if not return Y.

    1. Tim:
      I believe this needs to be an IF AND statement like this:
      =IF((AND(T2>=3,I2>1,M2-AC2>I2*0.7)),"N","Y")
      Notice the T2 >=3 per your logic.

  5. Hi,
    I have a big list (almost of 100 places with different toll amounts) of toll names and amounts. Every month I have to prepare all the toll list manually.I want to make a sheet where I put the place name in a particular column and the other cell (in column) take value of its toll amount automatically. I have tried to put multiple if formulas in a single cell but it doesn't works or showing error.

    Can you please help me on this.

    1. Are each of the place names unique?

      1. yes !!

        for an example.

        place 1 ABC , amount - 100
        place b BBB , amount - 200
        place c, BBD , amount - 700
        Place d, e ,f,g,h........

        I need all the condition in a single cell. I just want to put the place name in a cell and the another parallel cell should take the amount automatically !!

  6. =IF(OR(AND(E5="Critical",F5="OK"),1), AND((E5="Critical",F5="NOK"),0),"")

    What is error here

    Thanks in Advance

    1. Hello, Reza,

      It looks like the problem is in the usage of the logical AND / OR functions. Please read how to correctly use these functions here.

      Or if you describe the conditions for your formula in detail, we'll be able to specify what is wrong with the formula.

  7. HELP PLEASE :)

    I am trying to create an "IF" function with several variables. Below is my formula, the first two functions seem to work correctly, but the second two do not.

    Any HELP would be greatly appreciated!

    B19 C19
    1 Dollar Amount
    2 Dollar Amount
    3 Dollar Amount
    4 Dollar Amount
    5 Dollar Amount
    6 Dollar Amount

    =IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))

    1. the formula did not copy identically

      =IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))

      1. The formula will not copy correctly.

        it should be
        "=IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))"

        1. UGGGGGG it will not copy correctly!

          =IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",

          IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))

  8. Hi Brainstrust, I am really hoping for some guidance here. I have a cell that will either say Buy or Sell. I have the 2 following formulas that work on there own but I need them combined somehow into 1 cell so that it determines what to do depending on the cell saying Buy or Sell, hope this makes sense, Thanks in advance

    =IF(B5="BUY";(H5-E5)*100-1)

    =IF(B5="SELL";(H5-E5)*100)*-1

    1. Hi Cory,

      You can nest one If inside another, like this:

      =IF(B5="BUY"; (H5-E5)*100-1; IF(B5="SELL"; (H5-E5)*100*-1; ""))

  9. Maybe this (above question) is best solved using VLOOKUP??

    1. Hello, Brantley,

      You should use the nested IF function along with AND. The formula will look similar to the one below:

      =IF(AND(D3="Single",E26="65 & Blind"),"$2600", IF(AND(D3="Single",E26="65 or >"),"$...", IF(AND(D3="MFJ",E26="65 & Blind"),"$...", IF(AND(D3="MFJ",E26="65 or >"),"$...",0))))

      Please just specify the deduction amount for each pair of the selected values in cells D3 and E26.

      Hope this will work for you.

  10. Hi everyone,

    I am trying to use a formula to calculate a value using multiplication depending on different text.

    Say C16 is 6 then I want to be able to get the below = values:

    IF SSC then C16*390*1.1 = 2574
    IF SO then C16*360 = 2160
    IF SS then C16*390 = 2340
    IF PSO then C16*320 = 1920
    IF PSS then C16*360 = 2160

    I have tried adding more criteria to the below but can't get it to work. What am I doing wrong??

    =IF(D16:D18="SSC",C16*390*1.1)

    Many thanks and all help is much appreciated!

    1. Hi Melina,

      You should use the nested IFs and absolute cell references with the $ sign in your formula. It will look like this:

      =IF($D$16:$D$20="SSC", $C$16*390*1.1, IF($D$16:$D$20="SO", $C$16*360, IF($D$16:$D$20="SS", $C$16*390, IF($D$16:$D$20="PSO", $C$16*320, IF($D$16:$D$20="PSS", $C$16*360, "")))))

      Where $C$16 is the cell that contains your number 6, $D$16:$D$20 is the range with the text values.

  11. Dear Sir/Madam,

    Thank you so much. I just learned something new. I have only one question to ask relating to one of my project.

    How will I use the nested if condition(or other if-conditions) to get the difference of two cells. And the difference must always rounded down to the decimal points provided below.
    a) 0.00
    b) 0.25
    c) 0.50
    d) 0.75
    e) 1.00

    Example:
    1) If my difference is 7.15 then my rounded value will be 7.00
    2) If my difference is 7.48 then my rounded value will be 7.25
    3) If my difference is 7.74 then my rounded value will be 7.50
    4) If my difference is 7.99 then my rounded value will be 7.75

    Please I really need your help on this problem.

    Thank you again.

    Aloysius Agobe

    1. Dear Aloysius,

      If I understand your task right, you don't actually need to use the IF function. Please try to enter the following formula in cell C1 to get the result you need:

      =FLOOR((A1-B1),0.25)

      Where A1 and B1 are two cells with the numeric values from which you are trying to find the difference.

      Hope this will help.

  12. Hi again,

    If my column as a different text value can I do a count for each of the values in the same column..I need to know how many "Retiree Family", "Active Individual" etc.. not sure if this can be done in a formula or not.

    Thanks for your help...

  13. I am trying to create a formula that will return either "Unable" or "Able" based on the three criteria below. these three are the combination of options for the three cells that should come back as "Unable"

    1. L2=yes, M2=no, N2=yes
    2. l2=no,m2=yes,n2=yes
    3. l2=no,m2=no,n2=yes

    This is the formula i have, but it is not working. can anyone help?

    =if(OR(AND(L2=yes, M2=no, N2=yes), AND(l2=no, m2=yes, n2=yes), AND(l2=no, m2=no, n2=yes)),”Unable”,”Able”)

    i am looking to have a return of "unable" if any one of these three criteria are met.

    thanks

    1. Hello,

      Please note that text values are always enclosed in "double quotes" in Excel formulas. So the correct formula should be as follows:

      =IF(OR(AND(L2="yes",M2="no",N2="yes"),AND(L2="no",M2="yes",N2="yes"),AND(L2="no",M2="no",N2="yes")),"Unable","Able")

      Hope this is what you need.

      1. Hi,
        yes, thank you so much! I should have realized that.

        How would the equation change if I wanted to add to it that if any of the areas are left blank, it should also have a return of "unable"

        I could add in the various =AND equations, but that would be a huge list of them. is there another way to do this?

        1. Hi,

          If you want to return "Unable" when any of cells L2:N2 is empty, then just add one more condition to the formula. It will look like this:

          =IF(OR(AND(L2="yes",M2="no",N2="yes"),AND(L2="no",M2="yes",N2="yes"),AND(L2="no",M2="no",N2="yes"),OR(ISBLANK(L2),ISBLANK(M2),ISBLANK(N2))),"Unable","Able")

          Hopefully it will help you.

  14. A1 Passed C1 Passed E1 Passed G1Failed I1Failed. I Want J1 to be if 3/5 Cells are "Passed". Final Result is Passed, if not Failed.

    1. Hello,

      Please try the following formula:

      =IF((COUNTIF(A1,"Passed")+COUNTIF(C1,"Passed")+COUNTIF(E1,"Passed")+COUNTIF(G1,"Passed")+COUNTIF(I1,"Passed"))>=3,"Passed","Failed")

      Hope it will help you.

  15. I want to put a formula if
    Gross salary is below than Rs.7500 then Professional Tax should 0,
    if greater than 7500 and less than 10000 then PT should 175 and
    if gross salary is greater than 10000 then PT should 200

    How to write it? please guide

    1. Hi Try this below example with your scenario;
      Formula - =IF(B1<7500,"0",IF(B110000,"200","")))

      Name Salary PT
      A 19,131 200
      B 11,937 200
      C 7,984 175
      D 18,692 200
      E 13,105 200
      F 17,416 200
      G 7,238 0

  16. Hi I need help for a formula for the following

    cell D4 = value of C5 if cell D2 contains 1

    thank you

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(D2=1,C5,"")

      Hope it will help you.

  17. Ps could u help...if I type something in A1, I want to show Name code automatically in A2.
    If A1 is Anil, then i want A2 to show 9
    If A1 is Sunil, then i want A2 to show 10
    If A1 is Peter, then i want A2 to show 11
    If A1 is Harry, then i want A2 to show 12

    Ps help me
    Thanks!!

    1. Hello, Anil,

      If I understand your task correctly, please try to enter the following formula in cell A2:

      =IFS(A1="Anil",9,A1="Sunil",10,A1="Peter",11,A1="Harry",12)

      Hope this is what you need.

  18. Hi,

    Can anyone help me to create this formula,

    If the total value is equals to:
    1. 0 - 2,000, then times it by 0.071
    2. 2,001 - 25,000, times it by 0.075
    3. 25,001 - 75,000, times it by 0.085

    And so on... can anyone help me with the correct formula please!

    Thank you!

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IFS(A1<=2000,A1*0.071,A1<=25000,A1*0.075,A1<=75000,A1*0.085)

      Hope it will help you.

  19. Hello Svetlana,

    pls. provide formula for this :

    If C1= start with “ST” and D1=”18” then result “214810”
    If C1= start with “S0” and D1=”18” then result “214810”
    If C1= start with “S0” and D1=”9” then result “214800”
    If C1= start with “EX” and D1=” ” then result “ ”
    If C1= start with “ST” and D1=”9” then result “CHECK”

    Thanks in advance

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(AND(LEFT(C1,2)="ST",D1=18),"214810",IF(AND(LEFT(C1,2)="S0",D1=18),"214810",IF(AND(LEFT(C1,2)="S0",D1=9),"214800",IF(AND(LEFT(C1,2)="EX",D1=""),"",IF(AND(LEFT(C1,2)="ST",D1=9),"CHECK","")))))

      Hope this will help.

  20. Hi there,

    I am having issues with my If(And formula. Here's the formula =IF(AND(AQ2=AR2=AS2),"No Change",IF(AND(AR2=AS2,AQ2AR2,AQ2AS2),"Title Change Only",IF(AND(AQ2=AS2,AR2AS2,AR2AQ2),"Job Code Change",IF(AND(AQ2=AR2,AS2AR2,AS2AQ2),"Comp Change",IF(AND(AQ2AR2,AQ2AS2),"Comp & Code Change","ERROR")))))

    Where columns AQ, AR and AS are:

    AQ= Yes or No, AR= YES or NO, AS=YES or no

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(AND(AQ2=AR2,AQ2=AS2),"No Change",IF(AND(AR2=AS2,AQ2<>AR2,AQ2<>AS2),"Title Change Only",IF(AND(AQ2=AS2,AR2<>AS2,AR2<>AQ2),"Job Code Change",IF(AND(AQ2=AR2,AS2<>AR2,AS2<>AQ2),"Comp Change",IF(AND(AQ2<>AR2,AQ2<>AS2),"Comp & Code Change","ERROR")))))

      Hope this will help you with your task.

  21. I need the similar formula but want to select the value from my excel cell.
    Please share the formula

    Thanks in Advance

    1. Hi Sakib,

      Instead of typing a value directly in a formula, enter a reference to the cell containing the value.

      1. hi,
        i am trying to create reference to a cell in IF function.
        e.g. IF(k19<0, "f18=k19","0") instead of applying f18=k19 its showing result as f18=k19 as text. i changed the cell format etc. but .....

        1. Hello MAQBOLL, when you use "" you are telling to excel to write as a text whatever is between "" For example "f18=k19" will be written in your cell f18=f19 just like that.
          If you want that F18 be equal to K19 if K19=0 then...
          Try writing this formula directly in F18 cell
          =IF(K19<0,K19,0)
          Hope this helps

      2. Thanks a lot Dear ... its working :)

  22. can someone help me in creating a formula in excel using nested IFs?

    basically, what I need is:

    IF column A is equal to "Complex" and value of column B is 6 the column C would indicate "Exceeded"

    IF column A is equal to "Non Complex" and value of column B is 4 the column C would indicate "Exceeded"

    Thanks in advance!

    1. basically, what I need is:

      IF column A is equal to "Complex" and value of column B is 6 the column C would indicate "Exceeded"

      1. Hello,

        If I understand your task correctly, please try the following formula:

        =IF(AND(A1="Complex",B1=6),"Exceeded",IF(AND(A1="Non Complex",B1=4),"Exceeded",""))

        Hope it will help you.

  23. Hi, I just cannot get this to work.. I'm making an invoice template. And I want an IBAN number to appear only if I select English from the dropdown menu. And it has to be one relative to a bank account I choose from another.

    something like:
    if A1="eng" and B1="bank1" = IBAN1,
    if A1="eng" and B1="bank2" = IBAN2,
    if A1="cz" = ""

    1. nevermind, I figured it out.
      =IF((AND(D1="eng", B11="bank1")), IBAN1, IF( (AND(D1="eng", B11="bank2")), "IBAN2", "" ))

  24. How to write formula in R colunm using or condition

    if q>=2 (1 days deduction)
    if q>=40 (2 days decuction)
    if q>=6 (3 days decuction)
    if q>=8 (4 days decuction)
    if q>=10 (5 days decuction)
    if q>=12 (6 days decuction)
    if q>=14 (7 days decuction)
    if q>=16 (8 days decuction)

    1. TRY THIS
      =IF(Q>=2,(NO OF DAYS) -1)

  25. can anyone help with this formula, looking at two fields:
    Due date e.g. 01/01/18 = Cell S2
    Paid = True/ False = Cell Q2

    Formula
    If paid = "Paid"
    if >today & false = "Not due"
    if <today & false = "Overdue"

    Thanks

    1. Hello,

      Please try the following formula:

      =IF(Q2,"Paid",IF(S2>=TODAY(),"Not due","Overdue"))

      Hope it will help you.

  26. I want to use 3 conditions in once cell using If, as below sample.
    The condition is =I(E9>0.79,"PASSED","FAILED"). On that condition, if I'm going to drag down the cell it will show nothing or blank, but its not appearing blank, its showing the "PASSED or FAILED".

    Score Date Remarks
    80% 23-Nov-17 PASSED

    1. Hello, Wyndel,

      As far as I can see from your formula, it contains only one condition (E9>0.79). Please specify what two other conditions are and what should be displayed as the result.

      I’ll try to adjust your formula for your needs.

  27. Hi, may I ask if how can I able to solve this

    here's my formula
    =INDEX(A1:I34,MATCH(L13,A2:A34,0),MATCH(K13,A1:I1,0))

    by which L13 = G.22
    and K13 = /2

    they'r using two cells for look up values (K13, L13)

    what I wanted is, to use just ONE cell, with delimiter, per se: N6 = G.22/2

    =INDEX(A1:I34,MATCH(N6,A2:A34,0),MATCH(N6,A1:I1,0))

    1. I solved that formula using this
      =INDEX('CY2019'!A1:I34,MATCH(LEFT(G13,4),'CY2019'!A1:A34,0),MATCH(RIGHT(G13,2),'CY2019'!A1:I1,0))

      BUT how to solve this?

      what I wanted is if the result is less than 10,000 it'll post 137.50, if it exceed 10,000 it'll use this formula =((A13*2.75%)/2)*12
      BUT if result exceeds 40,000 it'll post 550

      =IF (AND(K13*2.75%)=10000/2)*12

      1. Hello,

        If I understand your task correctly, please try the following formula:

        =IFS(A1<10000,137.5,A1>40000,550,A1>=10000,((A13*2.75%)/2)*12)

        Hope this will help you!

  28. Suppose there are three different values in three different cells of ms-excel.. Use if statement to find the greatest of three numbers stored in cell.

    1. Hi Amanpreet,

      It can be done with a simple MAX formula like this: =MAX(A1:A3)
      Or this one for non-contiguous cells: =MAX(A1, C1, E1)

      1. 18.00% ₹ 79.20
        18.00% ₹ 40.50
        18.00% ₹ 15.48
        18.00% ₹ 18.90
        12.00% ₹ 122.40
        18.00% ₹ 68.40
        18.00% ₹ 270.00
        18.00% ₹ 45.00
        18.00% ₹ 48.60
        18.00% ₹ 990.00
        18.00% ₹ 75.60
        18.00% 27.00
        18.00% 360.00
        0.00% 0.00
        18.00% 15.12
        0.00% 0.00
        i want to calculate result of this both column in a separate row if column has 18% then in row it should be calculate its percentage and amount if column has 12%,5% or 0% it should be shown in below row

  29. Can someone help me with making a formula for a cell so that IF A1=X and B1=Y, then C1=Z, or IF A1=X, B1=W, then C1=U, where x is any number between 740 and 760, Y=65-70, Z=-.250, W=71-75, U=-.5.

    i want to make it all in one cell so that base on the first two cell, Cell C would know what value to input.

    1. Hello, Nguyen,

      Please try the following formula:

      =IF(AND(A1>=740,A1<=760),IF(AND(B1>=65,B1<=70),"-.250",IF(AND(B1>=71,B1<=75),"-.5","")),"")

      Hope it will help you.

  30. Hi, I have some data given below:

    Company Invoice# Currency
    1060 0004A00000983 BRL
    1045 3837 BRL
    1060 238109 USD
    1055 688143 USD
    1091 TOL16036 MXN
    1065 0004A00005420 MXN

    I want to know that:
    1. is there any invoice number preceding with zero
    2. if it is there of which company (1045/1060/1091/1055/1065)
    3. if it is of 1060 then the currency should not be BRL
    4. if it is of 1065 display the text as "1065-no need to consider"

    is possible to check all these conditions together in one cell. could any one please help me on this..?

    1. Hello,

      Please try the following formula:

      =IF(LEFT(B2,1)="0"," 1. is there any invoice number preceding with zero ","") & IF(OR(A2=1045,A2=1060,A2=1091,A2=1055,A2=1065)," 2. if it is there of which company (1045/1060/1091/1055/1065) ","") & IF(AND(A2=1060,C2="BRL")," 3. if it is of 1060 then the currency should not be BRL ","") & IF(A2=1065," 1065-no need to consider ","")

      Hope it will help you.

  31. I have this formula:

    =MAX(IF('sheet1'!A:A="Door",'sheet1'!T:T))

    In above formula, it consider the Max in column T, by returning name "Door" in column A.

    Now, if I want to add a date frame (dates in column B in sheet1- formula below), how should I go through:

    'sheet1'!B:B,">="&P2,'sheet1'!B:B,"<="&P3

    P2= Start date
    P3= End date

    Thanks,

    1. Hello,

      Please try the following formula:

      =MAXIFS('sheet1'!T:T, 'sheet1'!A:A, "Door", 'sheet1'!B:B, ">="&P2, 'sheet1'!B:B, "<="&P3)

      Hope it will help you.

  32. I have two variables/servies in two columns for sets of client of our organization.

    Some client client received only service A, some have received only B, some have received both services.

    Example
    Service A and Service B

    I want to identify the clients who received "only A", "only B", and "both"

    How i can use the if or and and nested function.

    1. Hi Amal,

      The formula depends on how you identify clients who received one or both services. Do you have some mark or specific text in Service A and Service B columns? If a client has not received the service, is a cell in the corresponding column blank?

  33. Hi,

    how to combine this
    if A1=TRUE, A2>=3 then minus 1
    if A1=TRUE, A2=6 then minus 2
    if A1=FALSE, A2>=6 then minus 1
    if A1=FALSE, A2=8 then minus 2

    1. Hi Shayna,
      Looks like you need to use a nested IF function with the AND function:
      =IF(AND(A1=TRUE,A2=6),-2, IF(AND(A1=TRUE,A2>=3),-1, IF(AND(A1=FALSE,A2=8),-2, IF(AND(A1=FALSE,A2>=6),-1,""))))
      You can find the details in the first two sections of this blog post.

      1. Thank you Irina. You made my day :)

  34. Hello -

    I am looking for a formula that would calculate service pricing which is discounted after many hours.

    We could enter the total hours worked and the corresponding rate would appear.

    The list would look something like this:

    Total hours: _______
    Adjusted Price per Hours: _____

    Hours Rate
    1 $125
    2 $125
    3 $125
    4 $125
    5 $100
    6 $100
    7 $100
    8 $100
    9 $80
    10 $80
    11 $80
    12 $80
    >13 $50

    Can you help?

    Thanks!

    1. Thanks for you comment to Daniel above. I was able to figure it out.

  35. Hi Natalia,

    I need an IF formula for commission rates, Can you take a look at this and see if it can work?
    =IF(B7=100%,B13, IF(B7>=110%, B14, IF(B7>=120%, B15, IF(B7>=130%, B16, IF(B7>=140%, B17, IF(B6>=150%, B17)))))))
    B7 shows the target achieved
    B12 - B17 shows the commission rates.

    I'd really appreciate your help :)

    1. I think that I've got it with this.
      Commission rate selector.
      =IF(B7>=150%,B18, IF(B7>=140%,B17, IF(B7>=130%,B16, IF(B7>=120%, B15, IF(B7>=110%,B14, IF(B7>=100%,B13,B12))))))

  36. Hello,

    I need to right a statement to sum the total of something based what's put in a cell.

    So I have a range of cells B3:B52 and you can only enter the numbers 0-6 in them.
    Depending on what is input, we need a certain number:
    0=0
    1=90000
    2=128000
    3=185000
    4=261000
    5=356000
    6=470000

    Once those have been input I need to sum the total of the output in the range of cells.

    I can't figure out how to do this.

    Thanks in advance!

    1. Hello, Ryan,

      if you feel confused by the article above, please learn the basics of the IF function from another article of ours. When you understand what arguments should be present in the formula, you will be able to construct a nested IF :)

      Also, to sum the total in the range of cells, please take a look at these multiple options Excel offers :)

  37. I am trying to use a formula to pick a specific field but can seem to get it working

    I am using a drop-down so there will be no false

    =IF(K9="Tier 4",Data!A2,IF(K9="Tier 3",Data!A4,IF(K9="Tier 2",Data!A6,IF(K9="Tier 1",Data!A8))))

    1. Hello, Steve,

      As far as I can see from your formula, it misses one argument at its very end. Please bear in mind that IF function requires 3 arguments.
      I kindly ask you to take a look at this article to find our what you're missing :)

  38. Hello,
    I have gotten this formula to work to a degree, but I want the result of the column to be blank if there is not data in cell E2 but I don't know how to add that to this formula.I have tried several things but to no avail.

    =IF(ISBLANK(F2)=FALSE,"COMPLETED",IF(E2-TODAY()>1,"IN PROGRESS",IF(TODAY()-E2>1,"OVERDUE","")))

    help please :)

    1. Hello, Kristina,

      If I understand your task correctly, I would suggest adding one more IF to your nested formula, at the very beginning:
      IF(E2="","",IF(ISBLANK(F2)=FALSE,"COMPLETED",IF(E2-TODAY()>1,"IN PROGRESS",IF(TODAY()-E2>1,"OVERDUE",""))))

      Hope this is what you need.

      1. Thank you, that is exactly what I needed! I appreciate your prompt response.

  39. Hi, I work for an organization did a survey recently and I need a formula to calculate number of YESs from male and female in a separate manner.
    Here is the story. In the excel sheet I developed, there is a column counting the number of male and female and there is another column counts number of YES and NO answers for each questions received from both gender. Now, I need a formula to count number of YESs received from females separate and from male separate.

  40. I need a formula
    if an amount > 400000 and >500000 then 2% of exceed amount

    1. >400000 and <500000
      Greater then 400000 and less then 500000

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

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

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

    1. Try this to combine
      =CONCATENATE(G3, H3)

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

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

    Thanks in advance.

    1. Hello, Zarina.
      Thank you for contacting us.

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

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

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

  43. Hi,

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

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

    Any help would be greatly appreciated!

    1. Hi, Pearl. Thank you for your question.

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

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

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

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

    1. Thanks a lot

      1. Hello. Thank you for contacting us.

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

  45. Hi,

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

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

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

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

    Please help

    thank you

    1. Hi

      Thank you for your question.

      Please try to apply the formula below:

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

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

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

    1. Hi, Vinit,

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

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

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

    1. Hello, Joevan,

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

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

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

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

  49. Hi all,

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

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

    Thanks!

    1. Use vlookup

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

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

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

    2. Hi, Yanick,

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

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

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

    Works great!

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

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

    I can not get this to work!

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

    Any help is appreciated!

    1. That did not post correctly! My specific ranges are:

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)