Comments on: IF AND formula in Excel

On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time. Continue reading →

Comments page 7. Total comments: 494

  1. Hi I am trying to work out a formula to update on cell based on the conditions of 2 cells.
    If Cell 1 = 2 and Cell 2 = Yes then cell 3 should = No. Hope that is a clear expalanation

  2. Previously posted incorrectly.

    I am trying to get formula to return either 19/20 20/21 or 21/22 depending on when the date falls.

    However the formula does not seem to be working.

    '=IF(AND(x>=01.04.2019,x=01.04.2020,x=01.04.2021,x<=31.03.2022),'21/22',"-"

  3. I cannot understand why my formula is not working.
    =IF(AND("x">="01.04.2019","x"="01.04.2020","x"="01.04.2021","x"<="31.03.2022"),"21/22","--"

    1. Hello!
      You need a parenthesis at the end of your formula. Then it will work properly if it matches your data.

      =IF(AND("x">="01.04.2019","x"="01.04.2020","x"="01.04.2021","x"<="31.03.2022"),"21/22","--")

  4. I am working to get a formula that calculates if (D6 says "Yes" and C6 says "Pay Run" ,B6*26) or if (D6 Says "Yes" and C6 says "Month", B6*12) or if (D6 says "Yes" and C6 says "Year", B6*1) or if D6 says "No" return 0.00.

    I have tried =IF(AND(D6="Yes",C6="Pay run"),B6*26,IF(OR(D6="Yes",C6="Month"),B6*12,IF(OR(D6="Yes",C6="Year"),B6*1,"0"))) and this works for D6 saying "Yes" and C6 saying "Pay Run" and D6 saying "Yes" and C6 saying "Month" but doesn't work for D6 saying "Yes" and C6 saying "Year" or if D6 says "No"

    Can you help?

    1. Hi!
      Replace OR with AND in the formula

      =IF(AND(D6=”Yes”,C6=”Pay run”),B6*26,IF(AND(D6=”Yes”,C6=”Month”),B6*12,IF(AND(D6=”Yes”,C6=”Year”),B6*1,”0″)))

      1. Thank you for your quick reply! This works perfectly - thank you!

  5. I'm trying to return a date in another worksheet if S3 is showing an N/A. I'm using this formula that is working but I need to go a step further. I'm looking to show blank/nothing in the cell if S3 doesn't equal N/A, what do i need to add to make that happen?

    =IFNA(S3,VLOOKUP(@N:N,Completed_Ocean[[Equipment '#]:[Date and Time '@ Consolidator]],26,FALSE))

    Thanks!

    1. Hello!
      Use an IF formula with condition an ISNA function

      =IF(ISNA(S3),VLOOKUP(@N:N,Completed_Ocean[[Equipment ‘#]:[Date and Time ‘@ Consolidator]],26,FALSE),"")

      Hope this is what you need.

      1. This worked, thanks for the help and all the insight the site provides!!

  6. Hi

    i need help doing something very simple - i need a certain cell to say 0 if the cell previous says CLOSED.

    What formula do i use?

    thanks,

  7. I am trying to do a compensation file based on full-time and part-time FTE to come back with -$500 if they do not meet a certain number of meetings. I can't get the below formula to work, but the work separately but I need to combine them. Any/all help with this matter is greatly appreciatied

    =IF((AND($F:$F>=0.51,$R:$R<3)),-500,0),OR(IF((AND($F:$F<=0.5,$R:$R<1.5)),-500,)

    Fulltime fte 3 or more meetings or -500
    parttime fte l.5 or more meetins or -500

    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((AND($F:$F>=0.51,$R:$R<3)),-500,IF((AND($F:$F<=0.5,$R:$R<1.5)),-500,0))

  8. I am Trying to =if(and(E11="Gram",G11>=1.08,G11=3.7,G11<=4),"Pass","Fail"))) then I will add more depending on packaging requirements. How do I Create a single "If/and formula" all based on what Product is entered into Column E to show a pass fail in column I based on the weights entered into column G?

  9. Hello! I am trying to set up a code with two conditions and three options within each condition. The value I would like to generate in E depends on BOTH columns C and D.

    I would like to do the following if the value in D is =30,"3",
    IF(C1>=25,"2",
    IF(C1>=20,"1","0"))) /=70:
    =IF(C1>=30,"3",
    IF(C1>=25,"2",
    IF(C1>=22,"1","0"))) /=20 and D/=22 and D>/=70.

    I understand how to do these arguments separately (as written above), but I would like to combine the arguments into a single code, where, depending on the value in D, different conditions for C are applied. Not sure how to apply 'AND' arguments in this scenario. Thank you for your help!

    1. Hello!
      According to your formula, I cannot understand all the conditions. What is D is = 30, ”3 ″ or D / = 22 and D> / = 70?
      At the same time, the article above has all the necessary information to write the formula

  10. Hi, Thank you for the great content. I have a question related to the the following formula I have created:

    =OR(AND($BB63>=P$7,$BB63

    1. Hi!
      I am not sure I fully understand what you mean. Part of the formula is missing.
      Please describe your problem in more detail. Write an example of the source data and the result you want to get.

  11. Hello Sir,

    How do you formulate a scenario like this "if studentA is taking a maths subject this semester as per this class registration list, then they should pay $1000.
    I have list of students pursuing different subjects this semester, but each subject is charged a different rate.

    Thank you!

  12. I am trying to find a formula to calculate a commission value.

    Here is the table:

    Goal Attainment Commission Value
    0 - 15% $- 0.00
    16% - 30% $10.00
    31% - 40% $12.00
    41% - 50% $15.00
    51%+ $20.00

    The formula I came up with is:

    =IFS(J4<16,”0”,J4<31,”10”,J4<41,”12”,J450,"20")

    This keeps resulting in #NAME?

    I am using MS Excel for Mac version 16.54

    2. sorry, type O in above table. this is what I am using

      =IFS(J4<16,”0”,J4<31,”10”,J4<41,”12”,J450,"20")

  13. Is there a way to have the "value_if_true" be the contend of a cell that is not always the same? (i.e. a name generated by a sign up list)

    I tried this: =IF(AND($A$3="Ms.Atchison",$B$3=G$1),$C$3," ")

    The false value is just leaving the cell blank. In C3 is the name of the person that signed up with Ms. Atchison. $B3$3=G$1 is the time of the confernce transposed.

      1. 11/11/21 3:30 PM 3:40 PM 3:50 PM
        Conference Name Appt Time Last Name First Name
        Ms. Atchison 6:30 PM Kim-Gorup Lia
        Mrs. Blanchard 3:30 PM Turner Levi
        Mrs. Blanchard 3:40 PM Rectenwald June
        Mrs. Blanchard 3:50 PM Robinson Rayna

        The report I was able to download has all of the conferences listed as seen on the left: teacher name (column A), Appt. Time (column B), Student last name (column C). I would like to transpose this information so the times are in columns start in column G through column AD (10 minute intervals). So when the time in column B is equal to time in column G through AD AND the teacher name is in column A, the last name listed in column C will show up in the cell under the matching time. I would then have the teacher name listed in column F with the last names under the times going across the row.

        Hopefully that makes more sense.

        1. Hello!
          Unfortunately, your question is not clear. What do you want to write in the cell under the time - a name from A1 or from C1?

          1. I am wanting to have the name in column C1, which in this case would be Kim- Gorup.

            1. Hi!
              Your formula prints the name from cell C3 in one of the cells F3:AD3 versus the time in cells F1:AD1. Isn't that what you want?

              1. Yes, it is what I want, however, if I change the teacher's name from "Mrs. Atchison" to another teacher, that shows up in column A more than once, it will only fill the first name in Column C even if all the other criteria match.

                For example: Blanchard is in the next 13 cells in column A (A4-A16) with various times in Column B and different names in Column C.

                If I adjust the formula, removing $ before the cell number, before $A3 and $B3, it will pick up the first conference for Blanchare at 3:30 (time in B4), but it will not copy across the row and pick up the conferences that show up in lines A5-A16.

  14. Good Day sir,

    What formula could I use to write the following in excel:

    if =26 but =51 but =76 but <=100 Print 10%

  15. I am looking for a formula that does the following for an answer in cell J2:
    If cell B2=300 then =SUM(D2)*0.03
    If cell B2=400 then =SUM(D2)*0.04
    If cell B2=500 then =SUM(D2)*0.05

    In other words, cell J2 will calculate IF cell B2 is equal to 300, 400 or 500...then the result will calculate the amount in cell D2 and multiply it by 0.03,0.04 or 0.05.

    I thought this was simple enough...but can't quite get it right. Any assistance would be greatly appreciated!

    1. Hello!
      You can use this formula:

      =IF(B2=300,D2*0.03,IF(B2=400,D2*0.04,IF(B2=500,D2*0.05,"")))

      or

      =IFS(B2=300,D2*0.03,B2=400,D2*0.04,B2=500,D2*0.05)

      You can learn more about multiple conditions and nested IF in Excel in this article.
      The formula SUM(D2) doesn't make sense.

  16. Please help me

    This is the formula i am using

    IF(AND(E6>0,F6>0,I6="PS"), "Yes", IF(AND(F6>0,I6="OP"), "Yes", "No"))

    Cell value are E6=1, F6=0, I6=PS, I6= OP

    In evaluating formula from Formulas>Evaluate Formula showing #N/A but the output is correct.

    IF(False,#N/A, IF(AND(F6>0,I6="OP"), "Yes", "No"))

    How to over come #N/A

    1. Hi!
      I have not been able to replicate your problem. Perhaps a formula is written in cell F6. When checking the condition F6> 0 using Evaluate Formula, Excel tries to calculate it and gets an error. Evaluate Formula cannot evaluate the formula in another cell.

  17. Date USD GBP AUD
    01-04-21 72 105 55
    18-04-21 72.5 104.5 55.25
    02-05-21 71 102 53.8
    15-05-21 72.4 103 55
    01-06-21 73.25 105 56

    Date Amount Ex.Rate Total
    02-04-21 USD 10.25 PLEASE SUGGEST FORMULA TO GET APPlICABLE EXCANGE RATE AS PER THE DATE AND CURRENCY SYMBOL
    01-04-21 GBP 10.40
    01-04-21 AUD 25.50
    05-04-21 USD 220.10
    03-04-21 GBP 105.80
    04-04-21 AUD 205.25
    01-04-21 USD 150.50
    02-04-21 GBP 150.18
    18-05-21 USD 165.25
    10-05-21 AUD 190.75
    30-05-21 USD 135.25

  18. Help!
    • Shipping Cost which is the cost based on the Region and weight
    How do I get this using the two tables below?
    i tried If (and but it is very long and I'm getting confused any suggestions?

    A B C D E

    1 Region Weight Shipping Cost

    2 North America 1
    3 Asia 0.5
    4 Caribbean 5
    5 Caribbean 2
    6 Caribbean 7.9
    7 North America 20

    8 Shipping Origination 0.5 – 5 Kg 5.1 – 10 kg 10.1 – 15 kg Over 15 kg

    9 South America $5,000 $8,000 $12,000 $15,000
    10 Europe $18,000 $10,000 $15,000 $18,000
    11 Caribbean $2,000 $6,000 $13,000 $18,000
    12 Africa $20,000 $25,000 $35,000 $50,000
    13 Australia $25,000 $35,000 $47,000 $60,000
    14 Asia $20,000 $31,000 $45,000 $58,000
    15 North America $3,000 $6,500 $13,350 $18,770

  19. Region Weight QTY Cost
    North America 1 3 $1,230.00
    Asia 0.5 21 $3,330.00
    Caribbean 5 3 $3,340.00
    Caribbean 2 54 $3,350.00
    Caribbean 7.9 21 $2,330.00
    North America 20 32 $54,310.00

  20. HOW CAN WE CALL LOWER VALUE AGAINST SOME FILED LIKE THAT

    CONTAINER VALUE
    ABCS1234567 1
    ABCS1234567 2
    ABCS1234567 3
    ABCS1234567 4

    WE NEED LOWER VALUE AGAINST SAME CONTAINER NUMBER

    IN EXL

    THANKS

    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:

      =MIN(FILTER(B1:B10,$A$1:$A$10=A1))

  21. HOW CAN WE CALL LOWER VALUE AGAINST SOME FILED LIKE THAT

    CONTAINER VALUE
    ABCS1234567

  22. you can try this

    =IF(AND(L13>0,L134,L138,L1316,L1320,L1342,L1350,"25")))))))

  23. Hi there,

    Did I get a situation with 6 conditions, How to write this excel formula?

    OD Allowance
    O≤Ø4 0.8mm
    Ø4<O≤Ø8 1.5mm
    Ø8<O≤Ø16 2.0mm
    Ø16<O≤Ø20 2.5mm
    Ø20<O≤Ø42 3.5mm
    O≤Ø50 5mm

    The allowance must add to the Actual OD value。 In other words, Actual OD + Allowance, actual OD varies ranging 0 to 50 and above.
    Hope to hear from you.
    Thank you.

  24. I am looking for help with a formula.
    Column C is the Application Date

    Column D is the Application Expiration Date. Formula in that cell is: =IF(C132="","No Start Date",DATE(YEAR(C132)+3,MONTH(C132),DAY(C132)))

    Column E is "Days Left". That formula, and I don't know if it's correct or not is: =IF(D133="No Start Date","0",D133-TODAY())

    Column F is where I need help. Base Date off of a Today Date of 5/20/2021

    Column C Column D Column E Column F
    App Date Exp Date Days Left STATUS
    2/08/2011 11/10/2020 -191
    No Start Date 0
    3/11/2019 3/11/2022 295
    5/29/2018 5/29/2021 9

    What I want is a formula that would be in the STATUS Column that states IF Column E is 0 or less as in a negative number, I want the STATUS to read "EXPIRED. If the Days Left number is =1, I want it to read "RENEW NOW" AND if possible, if Column D Reads "No Start Date", I want the STATUS to read "No Start Date"
    Thanks for your help. I have not been able to get any formula to work.

  25. Hi,
    I am trying to use a toggle to say, if the cell = 1, answer is cell a, if the cell = 2, answer is cell b, if the cell = 3, answer is cell c etc. How would I solve this?

  26. I have been using two different formulas to get my data but I would like to combine them.
    Example: Cell (D) has a date (30 June 2021)
    Cell (E) might have a date or might not have one : (blank)
    In Cell (I) I want to subtract Cell (d) and cell (E). But if Cell (E) does not have a date use "DATE"
    Two formulas are:
    =IF(ISBLANK(D8),"",-DATE(2021,4,1)+D8) used if there is no date in cell (E)
    =IF(E8="","NA",$D8-E8) Used if there is a date in Cell (E)

    How would I combine these?

    Thank you,

    Delila

    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(E8="",IF(ISBLANK(D8),"",-DATE(2021,4,1)+D8),$D8-E8)

  27. Trying to create a conditioning format with a number that looks like this ($20.00). I need all numbers greater then that in a range of ($20.00) to ($500.00) to be highlight white letters and back fill, but it will not do it? I know it is something so simple I am hitting wrong?

    1. select entire cell value and then go to conditional formatting > Select Between > then Chang value what you want for range and then Custom format then change font and background color whatever you want.

  28. Formula on Sheet A =IF(AND('Sheet B'!$A:$A=$C165,'Sheet B'!$D:$D="6",'Sheet B'!$L:$L less than greater than symbol "Completed"),'Sheet B'!$P:$P,0)

  29. not sure why the sign will not show between $L:$L and "Completed", but is in my formula on my sheet.

  30. Need help with this:
    Customer Name - Sheet A Column C = Customer ABC (a list of 166 Customers)
    Customer Name - Sheet B Column A = Customer ABC (a list of 166 Customers)
    Customer Folder - Sheet B Column D = Folder #6 (folders range from 1-6)
    Customer Completed - Sheet B Column L = Blank, Not Started, WIP or Completed
    Days to Complete - Sheet B Column P = from today to ETA (this can also be a negative if it is pass due)

    Want to know in a particular folder how many days to complete if not completed.

    Formula on Sheet A =IF(AND('Sheet B'!$A:$A=$C165,'Sheet B'!$D:$D="6",'Sheet B'!$L:$L"Completed"),'Sheet B'!$P:$P,0)

    Results have = 0 regardless of the true results.

    1. Correction:

      Formula on Sheet A =IF(AND('Sheet B'!$A:$A=$C165,'Sheet B'!$D:$D="6",'Sheet B'!$L:$L"Completed"),'Sheet B'!$P:$P,0)

      1. Hello!
        Unfortunately, without seeing your data it is difficult to give you any advice. I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

        We'll look into your task and try to help.

  31. Requirement Shortfall
    stock month 1 month 2 month 3 month 1 month 2 month 3
    item A 300 400 500 600 -100 -500 -600
    item B 100 50 400 600 0 -350 -600
    item C 200 50 100 100 0 0 -50

    how to calculate shortfall for three months

  32. Thanks for this, But I can't seem to manage my formula.

    I need to know the following:

    If value (D1) is 5000 and 10000 and 20000 then 4

    =IF(D15000,2,IF(D1=10000, 3,IF(D1=20000,4)))))

    Afterwards I need to know how many times there was 1,2,3 and 4 in the formula row of course.

    What am I doing wrong?
    Thanks in advance!

  33. Hi Alexander,

    thanks for your reply.

    can I share my Excel file with you?

  34. Hi,

    thanks for sharing such amazing excel stuff.

    I will be grateful if you can help me with this formula:

    =IF(AND($F$3=1,M6>L6),J6*M6,J6*L6,IF(AND($F$3=2,N6>M6),J6*N6,J6*M6,IF(AND($F$3=3,O6>N6),J6*O6,J6*N6,IF(AND($F$3=4,P6>O6),J6*P6,J6*O6,IF(AND($F$3=5,Q6>P6),J6*Q6,J6*P6)))))

    I am trying to calculate value of work done for each month , (F3 is custome list of months from 1 to 12)
    M6 is Jan 21 and L6 is Dec 20 and J6 contract value. if the subsquent month is zero or less than previous month % than, the value should arrive from previous month % multiplied by contract Value.

    Kind regards,
    Hussain

    1. Hi,
      The information you provided is not enough to understand your case and give you any advice, sorry. But I see J6*M6. Are you multiplying the date by a number? Also, your IF functions are not nested within each other, but simply written one after the other.
      Here is the article that may be helpful to you: Nested IF in Excel – formula with multiple conditions.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  35. =IF(AND(Z14>89%,AF5=0),"LOW RISK"),IF(AND(Z14>59%,Z140,AF54,"HIGH RISK")

  36. Hello, community.

    I have been straggling to right a formula based on the below parameters, is there anyone here that can give a hand, it will be highly appreciated.

    - Low Risk = 90% to 100%
    - Medium Risk = 60% to 89%
    - High Risk = 59% and below

    Critical risks: Critical risks are raised when scores an "NC" on any items deemed "critical risk" in the checklist.

    Note1: If 3 or less critical risks are raised, the overall risk level shall be at least "Medium" or "High" if the score is below 59%.
    Note2: If 4 or more critical risks are raised, the risk level shall be "High" regardless of the score.

    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(AF5 > = 4,"High",IF(Z14 < 59%,"High",IF(Z14 < 89%,"Medium","Low")))

      I hope this will help

    2. I wrote it, in this way but still don't get the logic right.
      Being:
      Z14 =the cells that contain the score in percentage
      AF5= number of high-risk questions scored.

      =IF(AND(Z14>89%,AF5=0),"LOW RISK"),IF(AND(Z14>59%,Z140,AF54,"HIGH RISK")

  37. Hello

    I'm not experienced with excel at all and I am try to create a formula on a number of conditions.
    It is

    IF B="X" and E="Y" then the value at F = D/1.1
    but also
    If B="x" but E is not "Y" then the value at F = D
    but also
    If B is not "x" then the value at F =0

    Thank you for your assistance as it is doing my head in.

    Gary

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

      =IF(AND(B1="X",E1="Y"),D1/1.1, IF(AND(B1="X",E1<>"Y"),D1,IF(B1<>"X",0,"")))

      I hope I answered your question.

      1. Hello Alexander,

        Thank you for your assistance. Formula works perfectly and I have been able to adapt it to other calculations.

        Gary

  38. Hello

    Good Afternoon!

    I'm Here From UAE i just want to ask some questions regarding IFS function.
    Please see below Problems.

    Thanks in advance.

    Cell A2 Where i will Input the data then Cell A3 will be the Output when i Input the data from A2 other data will appear in Cell A3 as an output.
    While using the formula in IFS Function the formula is not working in other text value like this "4000-323-1-5"

    Formula: A3 Cell =IFS($D$2=4000-323-1-5,"SCS",$D$2=2,"CCTV",$D$2=3,"GBS",$D$2=4,"IPTV",$D$2=5,"IPTEL",$D$2=6,"DNS",$D$2=7,"HSIA",$D$2=8,"PA-BGM",$D$2=9,"GRMS",$D$2=10,"ACS-BOH / GR ACS",TRUE,"")

    When i input the data in Cell A2 = 4000-323-1-5 the data that will appear in Cell A3 as an Output is Empty.
    But when i input the data in Cell A2 = 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 and 10 Data is appearing in Cell A3 as per the Formula showing.

    Please help me to find what error is coming when i Type the data at Cell A2 as 4000-323-1-5 the data output in Cell A3 is empty. Or please give me a solution how to fix it.

    1. Hello!
      4000-323-1-5 is a text that consists of numbers. Therefore, in your formula, it must be enclosed in quotes.

      =IFS($D$2="4000-323-1-5", ”SCS”,$D$2=2,”CCTV”,$D$2=3,”GBS”,$D$2=4,”IPTV”,$D$2=5, ”IPTEL”,$D$2=6,”DNS”,$D$2=7,”HSIA”,$D$2=8,”PA-BGM”,$D$2=9, ”GRMS”,$D$2=10,”ACS-BOH / GR ACS”,TRUE,””)

      I cannot check the work of your formula, since it contains unique links to your data.
      I hope my advice will help you solve your task.

      1. Hi!

        Good Afternoon!

        Dear Mr. Alexander,

        Thanks for the Support now the formula is working you are right i just need to put this strings ("").

    2. Hello

      Please find the revised Message i put it in a wrong cell as per in the formula.

      Good Afternoon!

      I'm Here From UAE i just want to ask some questions regarding IFS function.
      Please see below Problems.

      Thanks in advance.

      Cell D2 Where i will Input the data then Cell D3 will be the Output when i Input the data from D2 other data will appear in Cell D3 as an output.
      While using the formula in IFS Function the formula is not working in other text value like this "4000-323-1-5"

      Formula: D3 Cell =IFS($D$2=4000-323-1-5,"SCS",$D$2=2,"CCTV",$D$2=3,"GBS",$D$2=4,"IPTV",$D$2=5,"IPTEL",$D$2=6,"DNS",$D$2=7,"HSIA",$D$2=8,"PA-BGM",$D$2=9,"GRMS",$D$2=10,"ACS-BOH / GR ACS",TRUE,"")

      When i input the data in Cell D2 = 4000-323-1-5 the data that will appear in Cell D3 as an Output is Empty.
      But when i input the data in Cell D2 = 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 and 10 Data is appearing in Cell D3 as per the Formula showing that is Equal to 2 = cctv , 3 = gbs.

      Please help me to find what error is coming when i Type the data at Cell D2 as 4000-323-1-5 the data output in Cell D3 is empty. Or please give me a solution how to fix it.

  39. The best article teaching you how to use IF AND OR formula in Excel!!

  40. Trying to write if statement that analyzes a number within in single cell and returns 1 of the following 3 options:

    -- if number in cell is greater than or equal to 10, then take the date in another cell and add 3,650 days

    OR

    -- if number in cell is less than 10, but greater than or equal to 5, then take the date in another cell and add 1,4650 days

    OR

    -- if number in cell is less than 5, then take the date in another cell and add 730 days

    I had this formula but it is not picking up the less than 5 group

    =IF(H3>=10,G3+3650,IF(H3<10,G3+1460,IF(H3<5,G3+730)))

    Can anyone help?

    1. Disregard, I was able it figure it out.

      =IF(AND(H2>=10,H2=5,H2=0,H2<5),G2+760,"Error-Recheck")))

  41. Have following data
    ABCD
    Column A credit days 60,90,75,30,
    Column b actual days 143,200,115,28
    Column C Deviation -83,-110,-40,2
    Column D Deviation % 138%,122%,53%,-7%
    Column E required : If column D >100% then column A/2, If D50% then A*3/4, If D<50% then A

    First two conditions are working but not the last one. Pl help me in fixing this issue.

  42. Using 365

    This going to be pretty elaborate so im doing my best to describe whats going on.

    I have two sheets..one is called Summary and the other is called Cost.

    The Summary has 4 drop downs, which in A9 drop down I am to choose my paint system. The other three drop downs, D7, D8 and D9 i am to choose my surface preps...i can have one, two or three surface preps, depending on the job.

    Paint system choices in A9: I have 39 choices, ...the main one driving a majority of the formula is TSA....so id choose either TSA or one of the other 38 choices (im not going to list them all..to much to list)

    Surface Prep choices for D7,D8 and D9 are as follows (they all pull from the same list):
    N/A
    LPWC
    SP2
    SP3
    SP6
    SP7
    SP10
    SP11
    SP15

    I can chose any combination of the above surface preps in the three drop downs.

    On the cost sheet, L28 ( which is the cell the formula will be in) it should populate the existing values in cells L21 or L25, based on the 4 drop down selections on the Summary.

    The criteria for L28 populating would be as follows:

    If A9 on Summary equals TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then give me the value in L25.

    So for an example, I can have this as a combo:
    A9=TSA
    D7=SP10
    D8=SP11
    D9=SP2

    If I have that above combo then I should have the value of L25 show up in L28. There may be a scenario where D8 and D9 will have the choice of N/A...(that means for this particular job only one surface prep was required, not three, but i still have to make a choice in D8 and D9 so I made N/A a choice and in this case I would still get the value of L25 because SP10 and SP11 is in the combination of the three.)

    On to the next criteria:

    If A9 on Summary equals anything but TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then give me the value in L21.

    For example, to get the value of L21 to show up in L28, I would have this combo:

    A9= anything but TSA (I have 39 choices in this drop down...so im chosing any one of the other 38 options)
    D7=LPWC
    D8=SP2
    D9=SP3

    In that scenario above im choosing anything but TSA in A9 ..and again, if D7,D8 and D9 contain any one or more of the following, in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then this time give me the value in L21....(N/A could be a choice too but since I have that SP3 in there, then L21 value should still show up in L28)

    The combo to leave L28 at zero would be anything but TSA in A9 and any one or more of the following combinations in D7, D8 and D9: SP2 , LPWC

    A9 = anything but TSA
    D7=LPWC
    D8=SP2
    D9=N/A

    So basically the only time L28 is left at zero is if anything but TSA is in A9 and the only surface prep is SP2 and/or LPWC.

    That pretty much sums up what im looking for. I hope I was as precise and clear as possible.

    Thanks for your time and reading and possibly helping me out.

    1. here is the formula for my description above:

      =IF(Summary!B9="TSA",IF(OR(OR(Summary!D9="SP3",Summary!D9="SP6",Summary!D9="SP7",Summary!D9="SP10",Summary!D9="SP11",Summary!D9="SP15"),OR(Summary!D8="SP3",Summary!D8="SP6",Summary!D8="SP7",Summary!D8="SP10",Summary!D8="SP11",Summary!D8="SP15"),OR(Summary!D7="SP3",Summary!D7="SP6",Summary!D7="SP7",Summary!D7="SP10",Summary!D7="SP11",Summary!D7="SP15")),L25,""),IF(OR(OR(Summary!D9="SP3",Summary!D9="SP6",Summary!D9="SP7",Summary!D9="SP10",Summary!D9="SP11",Summary!D9="SP15"),OR(Summary!D8="SP3",Summary!D8="SP6",Summary!D8="SP7",Summary!D8="SP10",Summary!D8="SP11",Summary!D8="SP15"),OR(Summary!D7="SP3",Summary!D7="SP6",Summary!D7="SP7",Summary!D7="SP10",Summary!D7="SP11",Summary!D7="SP15")),L21,0))

  43. I am trying a conditional statement with this logic:

    If Q101ABS(Q101), then Q101*0.01672
    If Q101<0 and K101<Q101, then K101*0.01672
    If neither statement is true, then return a blank cell.

    This is what I tried that doesn't work
    =IF(AND(Q101ABS(Q101)),Q101*0.01672),IF(AND(Q101<0,K101<Q101), K101*0.01672,"")
    ty for your help.

  44. I am trying to write a formula which will return answers based on data within the 'I' column, plus return a blank cell if column 'D' is blank. I've tried the following two formulas (based on information you provided to another user which I now can't find in the chat thread):

    =IF(I2="Retaliation","TO DO","NA",IF(AND(D2=""),"").
    The first half of the formula works, but when I add my IF/AND statement it fails.

    =IF(AF2="Not determined","NA",IF(AF2="Substantiated","TO DO",IF(AND(G2=""),"")))
    The response I get is "FALSE".

    I have checked for typos multiple times but clearly I'm missing something. I hope you can provide some guidance!

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

      =IF(D2="","",IF(I2="Retaliation","TO DO","NA"))

      Hope this is what you need.

  45. Trying to build an if and statement If in a column there are multiple Letters but each letter = a word.

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice, sorry. Could you please describe it in more detail? What result do you want to get?

  46. I'm sure this is simple but I can not seem to figure out how to get it to work.
    Cell M9 is dependent on what happens in cell L9
    if L9 is greater than or equal to 3, M9 should be "P",
    if L9 is less than 3, M9 should be zero
    if L9 is equal to "D", M9 should be zero
    if L9 is equal to "N", M9 should be zero

    What is the best way to make this happen - if it is possible to make happen?
    Thanks
    D

    1. Oh.... I got it. Sorry. I knew it was easy, but my mind is fried.
      Sorry.

      1. Ok.... but I still have a problem.
        So..... the initial part of the formula is obviously =IF(L9>=3,"P")
        I have another column that is counting the "P"s, another counting the "D"s, and another counting the "N"s. So here is the problem. When I type in a "D" (for instance) in L9, M9 is putting a "P" in the column and thus, being counted in the "P" count. Also happens if I type a "N" in L9.... a "P" shows up in M9. So I definitely need to know how to make M9 appear as a zero when typing either a "D" or an "N" in L9.

        I was think the If/And/or formula would work but can't seem to get the correct combination of things.

        So ….. yea, I could still use some help on this.
        thanks again

        d

        1. =IF(L9="D",0)*AND(L9="N",0)*OR(L9>=3,"P")

          This is the formula I was trying. It woks until I add the "OR" part. is it because it two different types of functions? the first two are dealing with alpha characters and the 3rd with a numeric?
          I didn't think it would be, but I could be wrong.

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

            =IF(OR(L9="D",L9="N"),0,IF(L9>=3,"P",0 ))

            Hope this is what you need.

            1. Alexander,
              Thank you so much.
              I see now why mine didn't work.
              I really appreciate the help.
              It totally works now!!!!

              D

  47. Sorry, even though my formula shows correctly to me, by the time it sends it changes to something else, interesting!

  48. Sorry, when I cut and pasted the formula did not paste correctly:

    =IF(B27=7,B27=11,B27=14,B27<=16),4)

  49. Here is the formula I created, but it returns nothing but "0". I am trying to assign a scale of 1-4 for the following brackets:
    0-6=1
    7-10=2
    11-13=3
    14-16=4
    =IF(B27=7,B27=11,B27=14,B27<=16),4)
    Any help is greatly appreciated!

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!

  50. if a1>=b1 then a1=5
    if a1<b1 then a1=0

    How to join above in one formular??
    Kindly help

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