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 3. Total comments: 307

  1. Hello there,
    Thank you for such insightful site!
    I tried following your web but I still don't really get the logic, and when i tried my formula below, some of the case it's good, but some of the case it said FALSE. I think there's something missing in my formula.

    Input: Row E is every 25th of the month, no matter what day it is
    Wanted Output : I want to create an automatic calendar for every 25th of the month for payroll system.
    The condition is, if 25th of the month is a public holiday or weekends (Saturday, Sunday), it should be moved to H-1 (24th) or the nearest working day.

    What I get right now:

    =IF(COUNTIF($H$22:$H$25,E13)>0,IF(WEEKDAY(EDATE(E13,0),12)>5,EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),12)-4),IF(WEEKDAY(EDATE(E13,0),12)5,EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),2)-5),EDATE(E13,0)))))

    This is the description of the formula
    =IF(COUNTIF($H$22:$H$25,E13)>0, [to see If 25th is a public holiday]
    IF(WEEKDAY(EDATE(E13,0),12)>5, EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),12)-4),
    [if the public holiday falls on Sunday or Monday- 6 or 7, then this is to move the date to nearest weekday - Friday]
    IF(WEEKDAY(EDATE(E13,0),12)5,EDATE(E13,0)-(WEEKDAY(EDATE(E13,0),2)-5),[if no, 25th of the month is not a public holiday but it is on weekend Saturday, Sunday, then this is to move the date to nearest weekday - Friday
    EDATE(E13,0))))) [if the 25th is a workday]

    I hope you understand what I'm trying to say since it is a bit complicated and English is not my first language.
    I would very highly appreciate it if you can help me with this!

    Many thanks, Dahlia

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

      =IF(WEEKDAY(E13,2) > 5,IF(COUNTIF($H$22:$H$25,E13-WEEKDAY(E13,2)+5) > 0,E13-WEEKDAY(E13,2)+4,E13))

      I hope it’ll be helpful.

      1. Hi, thank you for replying!

        I tried the formula but sometimes the result is FALSE

        For example, i put the E13 date is Monday, 2nd May 2022 which is a holiday, so it should be Friday, 29 April 2022 but the result written FALSE.

        Also, I have a case if the holiday happened at Monday, 2 May 2022 and Friday, 29 April 2022, can you help me to revise the formula?

        Thank yiu so much for your help!

        1. Hi!
          I don't know which days of the week are your holidays. Therefore, if necessary, change the argument of the WEEKDAY function as you need. In this formula, the first day of the week is Monday. Holidays are 6 and 7 days.

          =IF(WEEKDAY(E13,2) > 5,IF(COUNTIF($H$22:$H$25,E13-WEEKDAY(E13,2)+5) > 0,E13-WEEKDAY(E13,2)+4,E13-WEEKDAY(E13,2)+5),IF(COUNTIF($H$22:$H$25,E13) > 0,IF(WEEKDAY(E13,2)=1,E13-3,E13),E13))

          1. Hey! Thank you very much again for replying!
            This formula works well, but I'm getting new issue now.

            So, if i want to apply the formula to holiday that falls on Tuesday or other weekdays (except monday) then how i should add the formula but different weekday function?

            2. Hi, a little update on the formula so I tried to move here and there, now it kinda work for 3 holidays in a row on the weekdays, but now it can not filter the weekends anymore

              here is the formula:

              =IF(COUNTIF($A$2:$A$18,I26)>0,

              IF(WEEKDAY(I26,12)>=5,I26-(WEEKDAY(I26,12)-4),

              IF(COUNTIF($A$2:$A$18,I26-1)>0,

              IF(WEEKDAY(I26-1,12)>=5,I26-1-(WEEKDAY(I26-1,12)-4),

              IF(COUNTIF($A$2:$A$18,I26-2)>0,

              IF(WEEKDAY(I26-2,12)>=5,I26-2-(WEEKDAY(I26-2,12)-4),(I26-3)),(I26-2))),(I26-1))),I26)

              I want to fix it but I think it makes the formula even harder, again can you please help me the formula?

              Really really appreciate your help!

  2. Hello, I have multiple conditions for calculating faculty workload and can't seem to get my formula right. The original formula was:
    =IF((AD80-40)/2>0,(AD80-40)/2,"--")

    However I need to check some conditions before performing the operation above.
    -If the total workload credits are >= 24
    -And the total contact hours are >= 48
    -Then run =IF((AD80-40)/2>0,(AD80-40)/2,"--")

    Is this possible?

  3. Hi there,
    Thank you for such a valuable site!
    Question: I have a check out system for items where I need the item to show status in column C:
    -Blank if there is no date entered as due
    -"Not due" if there is a date that does not exceed today's date
    -"Overdue" if there is a date past today's date
    -"Returned" if there is a date entered into the Returned cell.
    Column C is Status, D is Item, E is Name, F is Notes, G is Due Back Date, H is Returned Date
    The formula I have so far that works to address the first 3 criteria using line 8 as my example:
    =IF(ISBLANK(G8),"",IF(G8<TODAY(),"Overdue","Not due"))
    but I cannot figure out how to get the final criteria to supersede the other criteria if a return date is present in H8 with a result of "Returned".
    If you can help with this I would appreciate it very much!
    Many thanks - Georgina

    1. Good day from South Africa. I am struggling with a formula containing AND and OR.
      Question:
      All books with cost prices between $50 and $80 (both prices included) on Accounting (Acc) and Tax supplied by the publisher Butterworths. Cost price is in column F, Type of book is in Column D and the publisher is in column E.
      My formula:
      =and(F4>=50,F4<=80),OR(D4="Tax",D4="Acc"),AND(E4="Butterworths")

      Why is my formula not working?
      Thank you

      2. On the same topic I also have the following question where my formula is not working:

        Extract a list of all Afrikaans, English and Japanese (Column I) books and DVD's (Column G) that were issued since 2000 (Column D), the year 2000 excluded.

        My formula:
        =OR(I2="English",I2="Afrikaans,I2="Japanese),OR(G2="Book",G2="DVD"),AND(D2>2000)

        Please help me - I would be very grateful. I find putting the formula in three different line using the ALT + ENTER option, splits them so that I can understand them, but as soon as I need to string them together, I somehow fail.

        Thank you and kind regards

      1. As fate would have it - I actually saw the formula working and now it is no longer working....
        Here is what I have on line 8:
        =(ISNUMBER(H8),"Returned",IF(ISBLANK(G8),"", IF(G8<TODAY(),"Overdue","Not due")))
        Initially this exact formula showed a value of "Returned", when a return date was entered into H8. After going back to it and entered more data it only showed "Overdue" as the value even though a date (correct format) was entered into the Returned Date field, now I am receiving nothing but a "There's a problem with this formula" prompt. Please help again...

        1. Hello!
          You didn't exactly copy the formula.

          =IF(ISNUMBER(H8),"Returned",IF(ISBLANK(G8),"",IF(G8 < TODAY(),"Overdue","Not due")))

          1. Well that's embarrassing - my apologies for wasting your time in responding as I have been using the full formula and it is not working
            This is a direct paste from the first line (row 2):
            =IF(ISNUMBER(H2),"Returned",IF(ISBLANK(G2),"",IF(G2 < TODAY(),"Overdue","Not due")))

            Am I missing something else?
            Continued gratitude for your help,
            Georgina

            1. Hi!
              I'm not sure I understand your conditions, but try this formula -

              =IF(ISBLANK(G8),"",IF(ISNUMBER(H8),"Returned",IF(G8 > TODAY(),"Overdue","Not due")))

              1. You have been extremely helpful - cannot thank you enough!

              2. With 2 minor tweaks of your formula corrections it is now working. Here's what ended up working:
                =IF(ISBLANK(G2),"",IF(ISTEXT(H2),"Returned",IF(G2 > TODAY(),"Not due","Overdue")))

                The "ISNUMBER" would not accept a date as the data so I changed it to "ISTEXT" and switched Overdue and Not due and all working now.

                If it wasn't for your kindness in giving alternate solutions I would still be experiencing such frustration.

                Alexander - Thank you thank you thank you!!

              3. Hi!
                ISNUMBER doesn't work because your date is written as text. This is not normal, but I cannot know about it. For a normal date, ISNUMBER works, since a date is a number.

      2. Totally worked thank you SO much!! I really appreciate the help!

  4. Hello

    I have a list of students from countries all over the world and I would like to complete a column which says which continent they are from.

    For example if a students comes from Spain , I want Europe to be written in the continent column and find a formula that can do that.
    I don't know if it's possible though.
    I would like to have all the continents ( Europe, Amercia , Asia ...) and every country on our list to be enclosed in one specific continent. We have almost all countries in the world, it's a lot of names.

    Something like if (student column A) ( column B spain , France, italy , germany (and so on) , " Europe" " America" "Asia".. )

    I went through all the examples mentionned and I tried many times different formulas but I can get my head around it. Help would be highly appreciated

    Many thanks
    Cyil

      1. Many thanks for your quick reply !!

        I had a look at this function but it's not what I am looking for because from the list of students coming from all over the world I won't be able to differenciate the countries and have a specific country aattributed to matching continents

        I had another idead, I have listed European countries under the number 1 , Asian countries under th number 2 and so on ..
        I am now looking to have a function to say in colum A (the countries from the students list I want to identify as continents) in the column the following (B1:B5) is Europe , (B16:B50) is Asia ...) so if B16 then it's Asia , if it's B4 then it's Europe

        Sorry I hope I made myself clear

        1. Hello!
          Unfortunately, I can't understand why the VLOOKUP function is not suitable for you and what you want to do.

  5. Hi, I need an help in the IF formula in number and text combined. I will post the test can resolve this for or suggest the what can I do for this.

    A B
    CODE CATEGORY
    1600 A Team
    1601 A Team
    1602 A Team
    1603 A Team
    1604 B Team
    1605 B Team
    1606 B Team

    If I enter the any code number in A2 cell and the B2 auto pick the correct team. this is what i want can you please give me the solution for this.

    I have 100 combination like this and to complete the task my self only.

    1. Hello!
      If I understand the problem correctly, you can find the command that matches the code using the VLOOKUP function. You can find examples and detailed instructions in this article.
      I hope it’ll be helpful.

      1. Thank you for the help Mr. Alex

  6. Hi! I have built a formula that calculates when we need to send out an order (20 days after receiving previous order), based on how many months were ordered. I built in an IF function that hides numbers if there isn't any data in the formula. Now I want to add into the formula that stops calculating a ship date if there are no more paid orders. I have another column that has how many months were purchased. How do I integrate an AND function into this that would only calculate if months>2? I cannot get the formula to compute.

    =IF([@[First order Delivered/Received]]="","",[@[First order Delivered/Received]]+20)

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

      =IF(AND([@[First order Delivered/Received]]=””,[@[months_purchased]]>2) ,””,[@[First order Delivered/Received]]+20)

      1. Hi!

        Thanks so much for the response. For some reason, it isn't. It is still calculating, even when the number of months is 2. The formula makes sense to me as well.

  7. Hey, i am looking for formula that can calculate at different amounts. For example. I have 40 apples, but the first 10 apples are at $2, the other 11 to 30 apples @ $2.50 and any apples over 40 @ $3.00. Then get the summation of all this in one cell.

    can someone help.

    thanks

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

  9. 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',"-"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    How to join above in one formular??
    Kindly help

  32. I'm unsure why the following formula is not working. I tested all 3 separately and they function correctly but not when I put them together. When I use them together the result is always "Not Started", even when I add text to C3 and a date to I3.

    =IF(ISTEXT(E3), "Not Started",IF(AND(ISTEXT(E3),ISTEXT(C3)), "In Process", IF(AND(ISTEXT(E3),ISTEXT(C3),ISNUMBER(I3)), "Complete")))

    1. can you add a bit more context to your post. its a little vague as to what you're trying to do.

  33. =IF(AND(D2>55,E2>65),"Passed both Theory and Practical"),IF(AND(D2>55,E2<65),"Passed Theory and Failed Practical Assessment"),IF(AND(D265),"Passed Practical Assessment Failed Theory Assessment"),IF(AND(D2>55,E2>65),"Unsuccessful"))))))

    1. Hi,

      Try this:
      =IF(AND(D2>=55,E2>=65),"Passed both Theory and Practical",IF(AND(D2<55,E2=55,E2<65),"Passed Theory and Failed Practical Assessment",IF(AND(D2=65),"Passed Practical Assessment Failed Theory Assessment",""))))
      I hope that helps :)

      1. OOPS sorry thta one has a typo, the correct one is below:

        =IF(AND(D2>=55,E2>=65),"Passed both Theory and Practical",IF(AND(D2<55,E2=55,E2<65),"Passed Theory and Failed Practical Assessment",IF(AND(D2=65),"Passed Practical Assessment Failed Theory Assessment",""))))

        1. Ok for some reason this website is changing my posts:
          after this bit:
          This part of the formula: IF(AND(D2<55,E2=55,E2=55,E2>=65),"Passed both Theory and Practical", IF(AND(D2<55,E2=55,E2<65),"Passed Theory and Failed Practical Assessment", IF(AND(D2=65),"Passed Practical Assessment Failed Theory Assessment",""))))

          1. jeez this is frustrating me remove the e2=55, bit

  34. I'm looking for a formula that will calculate how much someone gets paid if they work 46.50 hours a week with the following criteria (36-37 hours paid at regular pay, 37-45 paid @1.5x, 45 hours and above paid @ 2x). Regurla pay is $25/per hour.

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

      =(MAX(0,C1-45)*2+MIN(MAX(0,C1-37),8)*1.5+MIN(MAX(0,C1-36),1)*1)*25

      This can replace the IF and AND functions.

  35. Please may someone help me with a If/And formula?
    I'm working on a formula to highlight if annual spend is 50% higher than estimated spend.BUT ignore if estimated spend value is blank.
    I have a simple - If A1(spend) is greater than B1(estimted spend), yes or no. (Below)
    =IF(A1>B1,"Yes","No")
    But i'm struggling to understand how i can expand that to ignore if B1 value is blank?
    Any help would be greatly appreciated - thank you.

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

      =IF(A1<>"",IF(A1>B1,"Yes","No"),"")

      I hope this will help

  36. I am looking to combine the following 2 statements:
    =IF((AND(D3="Put",F3>E3)), ((E3-F3)*(B3*C3)), 0)
    =IF((AND(D3="Call",F3<E3)), ((F3-E3)*(B3*C3)), 0)
    They work independently from one another but I cannot figure out how to combine them. The goal is to use the correct formula depending on what I have in D3 (Call or Put).
    Some guidance would be greatly appreciated.

    Thanks.

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

      =IF(D3="Put",IF(F3 > E3,((E3-F3)*(B3*C3)),0), IF((AND(D3="Call",F3 < E3)), ((F3-E3)*(B3*C3)), 0))

      This will combine the two IF formulas

    2. I've worked it out on my own.

  37. hello sir,
    i want count sunday of till date in excel

    =COUNTIFS(L9:AO9,"sun")it give me 4 sunday, whole month of june-2020(date 01/06/2020 to 30/06/2020)
    i want for till date like =countifs(01/06/2020:10/06/2020,"sun")

    How can I do this with excel formula?
    if possible please suggest or help me

    jai

    1. Hello!
      I recommend that you read the COUNTIFS Feature Guide. 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. Thank you.

  38. Set 1 Set 2 Set 3 Result
    Y N N AA
    N N Y BB
    Y N Y CC
    N Y N DD
    Y Y N EE
    Y Y Y FF
    N Y Y GG
    N N N HH

    Need help with the logic formula - Conditions to be used are only IF, AND. OR.

    1. Hi Shariff,

      Is the formula below what you're looking for?

      =if(and(A1="Y",B1="N",C1="N"),"AA",if(and(A1="N",B1="N",C1="Y"),"BB",if(and(A1="Y",B1="N",C1="Y"),"CC",if(and(A1="N",B1="Y",C1="N"),"DD",if(and(A1="Y",B1="Y",C1="N"),"EE",if(and(A1="Y",B1="Y",C1="Y"),"FF",if(and(A1="N",B1="Y",C1="Y"),"GG",if(and(A1="N",B1="N",C1="N"),"HH",""))))))))

  39. I have two cells that are binary either 1 or 0 so I have 4 variables
    A1=0, A2=0
    A1=0, A2=1
    A1=1, A2=0
    A1=1, A2=1
    and I need a cell to determine what the cells are and if of example
    A1=0, A2=0 then cell A3= open
    A1=0, A2=1 then cell A3= Right
    A1=1, A2=0 then cell A3= Left
    A1=1, A2=1 then cell A3= Short
    How can I do this with excel formula?

    Thanks
    Eddie

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

      =IF(AND(A1=0,A2=0),"Open", IF(AND(A1=0,A2=1),"Right", IF(AND(A1=1,A2=0),"Left", IF(AND(A1=1,A2=1),"Short",""))))

      I hope it’ll be helpful.

  40. I have a spreadsheet with over 5000 rows of a machine id all with varying installation dates ranging from 2012 to today. Column A is machine id. Column B is Installed date. In Column C each machine has a classification code about 8 different classes eg Ste, Alu etc. In column D a disposal date. In Column E i have costs for year 2012. Column F for 2013 and so on. If disposed of for eg in 2012 there will be no costs for other yrs. I need to summarise by month by year the count of installations by classification. Then sum by month and year and by classification the costs. What is the best approach and formulas

    Any help appreciated

    1. Hello Lizzy!
      The easiest and most correct way to get an answer to your questions is to use a pivot table. Our blog has many articles about this. I recommend here and here.

      I hope it’ll be helpful.

  41. I need help with a multiple IF /IF AND formula because I am totally lost here.
    I have 5 colons I need to take in my formula, with a total of 4 conditions and I need to calculate the following:
    IF(AND(D1460, G14=0) then D14*$L$10,
    IF(D14+G14<=59, then $M$9,
    IF(AND(G14=<60, P14=J), then D14*$L$10+$M$9,
    and in all other cases it should be D14+G14
    How do I get them all in one field and make excel calculate the result with all those parameters? Is it possible at all? I tried with:
    IF((AND(D1460, G14=0), D14*$L$10, IF(D14+G14<=59, $M$9, IF((AND(G14=<60, P14=J), D14*$L$10+$M$9, D14+G14)))) But the formula is obviously wrong :-(
    Since I am a linguist and the last time I had maths was in 1983, you can understand my confusion... Many thanks!

    1. Hi Maria,
      It looks like you might have some overlaps for solutions is why it might not be working.
      Could you list the range of values the cells could have?

  42. I need one equation.
    One cell contain one value suppose 100 i need in next in following conditions.
    25 percentage of 100 plus 4 percentage of coming answer of 25 percentage.

    1. =(A1*25%)+(A1*25%*4%)
      You can also have the percentages in their own columns that way if you want to adjust, you can just change the values in the reference columns.
      =(A1*$B$1)+(A1*$B$1*$C$1)

  43. I have a formula issue, below need help;

    If A3 shows “any value”, show D3 as “In Progress”
    If A3 and E3 shows “any value”, show D3 as “Completed”
    If A3 and I3 shows “any value”, show D3 as “Pending Approval” < Particular this one having trouble with as the rest work in below formula. I’m close the issue is that this shows “In Progress”
    If A3 and E3 shows “blank” show D3 as “Blank”.

    This is the formula in its current value, any chance you could take a look and see if there is something wrong?

    =IF($A3"",IF($E3"","Completed","In Progress"),IF($A3"",IF($E3="",IF(AND($I3"","Pending Approval","In Progress"),"Completed")),""))

    1. =IF(AND(A3"",E3="",I3=""),"IN PROGRESS",IF(AND(A3"",E3"",I3=""),"COMPLETED",IF(AND(A3"",E3="",I3""),"PENDING APPROVAL",IF(AND(A3="",E3=""),""))))

  44. I am trying to include a formula in excel where if "EUROS" is entered in one cell the next cell will populate the € symbol but will also include the value that I enter.
    What formula would I need for this?

    1. Hi Sally,
      Please try the formula below.
      =IF(A1="EUROS","€"&B1)

  45. I have a spreadsheet with list of schools and states. Column G lists the state abbreviation and I have 13 analysts. I would like a blanks column B to pre-fill with an assigned analyst name based on their assigned states. For example I tried =IF(G6="NC", "Jay", IF(G6="MT", "Jay", IF(G6="FL", "Rami", IF(G6="VT", "Rami", IF(G6="TX", "Joe"))))...... but I get a warning there are two many arguments.

    Any suggestion?

    Thanks,

    J

    1. Hi Jay,
      I would try the Excel IFS function instead:
      =IFS(OR(G2="MT",G2="NC"),"Jay",G2="TX","Joe",OR(G2="FL",G2="VT"),"Rami")

  46. I have this Data how to solve?
    Grade>=17 And Sex =F And Location it is Bhandup, Mulund, 10% on Basic, Grade>19, 20% on basic , otherwise 8%

    1. Hi Ravina,
      Condition 1 and Condition 2 seem to be overlapping in your task. However, the following formula may serve as a starting point and can easily be modified if you decide to alter the conditions:

      =IFS(B2>19,20,AND(B2>=17,C2="F",D2="Bhandup, Mulund"),10,TRUE,8)
      PS If you wonder why there is ‘TRUE’ before 8 there, press ‘F1’ to search for the Excel IFS function, and you will find the answer. That’s where I learnt it myself.

  47. Hi Everyone! I'm building an estimating spread sheet and I'm having "#Value!" issues with what should be a very simple process. In my case Columns A & B contain a drop down menu. In column A there is only one appropriate choice, let's say "Floor". In column 2 there can be up to three appropriate choices, "Bathroom", "Ensuite" & "PWD" from which, obviously, only one is chosen. In simple English the process is this:
    IF cell A1 = "Floor" & cell B1 = "Ensuite", multiply cells F1*G1*O1, otherwise return "" blank.
    Of course cell B1 might read "Bathroom, "Ensuite" or "PWD" so this needs to be included in the formula. My problem appears to be incorporating these 3 variables for that cell and I can't find a way around it. Any help that you can give would be greatly appreciated!
    Many thanks,
    Dick

    1. =IF(AND(A1="FLOOR",OR(B1="BATHROOM",B1="ENSUITE",B1="PWD")),F1*G1*O1,"")
      That one should work

  48. Hi
    I'm trying to achieve what I believe the be a IF AND OR formula please. This is what I have so far:
    =IF((AND(B3="linux/Unix",D5="Run the following network scan:")),"nmap -A ","1..255 | % {echo ''192.168.X.$_''; ping -n 1 -w 100 192.168.X.$_} | Select-String ttl")

    B3 is a drop box so if "Linux/Unix" is not selected, then Windows is the only other option which displays the second outcome.

    However, I want to add an extra piece =if(D5="Next Question", " ", " "). Basically I want a black cell left if D5="Next Question" regardless of what B3 equals please.

    I have tried adding this to the end with extra brackets, but this does not work and I'm not sure where else to nest it please?

    1. =IF(D5="NEXT QUESTION","",IF(AND(B3="linux/Unix",D5="Run the following network scan:"),"nmap -A ","1..255 | % {echo ''192.168.X.$_''; ping -n 1 -w 100 192.168.X.$_} | Select-String ttl"))

      That should work

  49. I have 2 scenario's which have 2 diff IF statements. I need this to be in the same cell and cannot get it to work. Any suggestions.
    IF(AND(J4="MOLCO",Y4="SHORE",W4>S4),"-",S4-W4)
    IF(AND(J4="MOLOO",Y4="SHORE",W4>U4),"-",U4-W4)

    In both statements if all the AND(.....) are true then it only needs to return "-".

    Thanks
    Michael

    1. =IF(AND(J4="MOLCO",Y4="SHORE",W4>S4),"-",IF(OR(J4"MOLCO",Y4"SHORE",W4U4),"-",IF(AND(J4"MOLOO",Y4"SHORE",W4<U4),U4-W4,""))))
      It's a little messy but, this should work.

      1. IF(OR(J4"MOLCO",Y4"SHORE",W4<U4)
        The formula is missing "" back to back. Not sure why it won't show up.

        1. Less than "" back-to-back.

  50. so, for my homework im supposed to do this:
    In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:
    a. Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7 equals 0 and the Total in cell B8 is greater than 20000.
    b. If the logical test is true, display Yes (using “Yes” for the value_if_true argument).
    c. If the logical test is false, insert a nested IF function.
    d. Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
    e. If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
    f. If the logical test is false, display No (using “No” for the value_if_false argument).

    but i cant get it to work. i tried making this formula:
    =IF(AND(B7=0,B8>20000),Yes,IF(AND(B7>0,B8>200000),Yes,No))
    but it only gives a result of #NAME?

    1. you can try this
      =IF(AND(A7=0,B7>20000),"Yes",IF(AND(A7>0,B7>200000),"No"))

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