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 2. Total comments: 490

  1. I am trying to figure out this formula that if column B is empty then data should be empty, if B has a date then I need today - date in B2, where as C OR D if one column is present take date from there, I am unable to add this, please help

    =IFS(AND(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2)),"",IF(ISBLANK(C2),today()-B2,C2-B2),"",IF(ISBLANK(B2),ISBLANK(D2),today()-B2,D2-B2))

    1. Hi! Based on your description, it is hard to completely understand your task. I recommend reading this guide: The new Excel IFS function instead of multiple IF. Here's an example formula, but I'm not sure I'm guessing, as the above description is completely unclear.

      =IFS(AND(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2)),"",ISBLANK(C2),TODAY()-B2,ISBLANK(B2),D2-B2)

      To understand what you want to do, give an example of the source data and the expected result.

      1. Hi thanks for checking!

        Here it is, we have 4 columns below, and I want formula in column A, this is how result should be, I need to multiple conditions

        1. if there is no data in Column B then column should be empty
        2. If there is data in Column C then result in column A should be C-B
        3. If there is no data in Column C and data present in column D then result in column A should be d-B

        A. Ageing - B. Request date - C. Creation date - D. Approved date

  2. I HAVE TYPED THIS WHOLE FORMULA BUT ITS OVER LIMIT FORMULAS...HOW CAN I USE THIS LENGTHY FORMULA

    =IF(AND(F2=15,F2=25,K2="AJ"),F2*20,
    IF(AND(F2=15,F2=25,K229="ANIL"),F2*20,
    IF(AND(F2=15,K2="AR"),F2*30,
    IF(AND(F2=15,K2="BH"),F2*25,
    IF(AND(F2=15,F2=25,K2="BHAVANI"),F2*25,
    IF(AND(F2=15,F2=25,K2="BK"),F2*20
    IF(AND(F2=25,K2="BR"),F2*18,
    IF(AND(F2=15,F2=25,K2="DATAR"),F2*25,
    IF(AND(F2=15,F2=25,K2="ECO"),F2*30,
    IF(AND(F2=15,F2=25,K2="GHN"),F2*25,
    IF(AND(F2=15,F2=25,K2="HG"),F2*25,
    IF(AND(F2=25,K2="HL"),F2*30,
    IF(AND(F2=15,K2="KS"),F2*20,
    IF(AND(F2=15,K2="LAXIT"),F2*25,
    IF(AND(F2=15,F2=25,K2="MAHESH"),F2*50,
    IF(AND(F2=25,K2="MEHUL"),F2*25,
    IF(AND(F2=15,F2=25,K2="MI"),F2*25,
    IF(AND(F2=15,F2=25,K2="MIRA"),F2*25,
    IF(AND(F2=15,F2=25,K2="MK"),F2*25,
    IF(AND(F2=15,F2=25,K2="ND"),"300",
    IF(AND(F2=15,F2=25,K2="PAN"),F2*25,
    IF(AND(F2=15,F2=25,K2="PG"),F2*23,
    IF(AND(F2=15,F2=25,K2="PP"),F2*25,
    IF(AND(F2=15,K2="PRADIP"),F2*20,
    IF(AND(F2=15,K2="PURU"),F2*30,
    IF(AND(F2=15,K2="RAJ303"),F2*30,
    IF(AND(F2=15,K2="RP"),F2*25,
    IF(AND(F2=15,F2=25,K2="SG"),F2*25,
    IF(AND(F2=15,F2=25,K2="SJ"),"500",
    IF(AND(F2=15,K2="VINIT"),"500",
    IF(AND(F2=15,K2="SNK"),F2*30,
    IF(AND(F2=15,F2=25,K2="SP"),"500",
    IF(AND(F2=15,F2=25,K2="SS3",
    IF(AND(F2=15,F2=25,K2="SUN"),F2*23,
    IF(AND(F2=15,F2=25,K2="VD"),F2*70,
    IF(AND(F2=15,F2=25,K2="VI"),F2*22,
    IF(AND(F2=15,F2=25,K2="VINIT"),F2*21,
    IF(AND(F2=15,F2=25,K2="VISHAL",
    IF(AND(F2=15,K2="ZD"),F2*20)))))))))))))

  3. Hello! I'm a bit stuck after trying multiple IF AND functions to return a single value in a cell. The following is what I have so far:

    =IF(AND(ISNUMBER(SEARCH("ACCOUNT 1",[@[Account name]])), SEARCH("Nonbrand",[@Category])),"Nonbrand", IF(AND(ISNUMBER(SEARCH("ACCOUNT 1",[@[Account name]])), SEARCH("Branded",[@Category])),"Branded", IF(ISNUMBER(SEARCH("ACCOUNT 2",[@[Account name]])),"PLA","Other")))

    Currently the function returns "Nonbrand" but not "Branded" or "PLA" which are all of my desired values based on the criteria; the latter two are returned as #VALUE!

    Hopefully this is enough information, but please let me know if I should add more context. Thank you for your help!

    1. Hi! I can't validate a calculation that contains unique references to your data that I do not have. The #VALUE! error can occur in SEARCH("Nonbrand",[@Category]) SEARCH("Branded",[@Category]) because you are not using ISNUMBER.

  4. Hi please need your help on this scenario If cell less than 50 is equal to zero and If cell greater than 50 then add the over whats the formula? I tried this formula =IF(AND(A150,50-A1)) 

    Hoping for your help on this formula.. Thank you in advance

  5. I need help with a formula please. I have amounts in columnA1, if a1 is under 100 I want the amount in b1, if not I want it in c1

  6. I have three shifts (1,2,3) on the drop down menu in column G. If it is the first shift it should show the results in column A from column B, if it is the second shift it should show the results in column A from column C, if it is the third shift it should show the result in column A from column D. I need the formula. Thank you

  7. Dear Sir,
    how use if condition for the below condition, kindly advise

    1st check SHEAR or HAND and go my base kgs 50 and minus 40 kgs balance will through for amount calculation?

  8. What is wrong with my formula? I've tried it multiple ways, and I keep getting errors:
    =
    IF(AND(L13="PM",E13=.5)),4,
    IF(AND(L13="PM",E13>=.35)),6,
    IF(AND(L13="PM",E13>=.45)),8,
    IF(AND(L13="EST",E13=.3)),4,
    IF(AND(L13="BOTH",E13=.25)),4,
    IF(AND(L13="BOTH",E13>=.3)),8,
    IF(AND(L13="BOTH",E13>=.35)),10,
    IF(AND(L13="BOTH",E13>.45)),12%)))))))))))

    1. Hi! Check the formula below, it should work for you:
      =IF(AND(L13="PM",E13=0.5),4,
      IF(AND(L13="PM",E13>=0.35),6,
      IF(AND(L13="PM",E13>=0.45),8,
      IF(AND(L13="EST",E13=0.3),4,
      IF(AND(L13="BOTH",E13=0.25),4,
      IF(AND(L13="BOTH",E13>=0.3),8,
      IF(AND(L13="BOTH",E13>=0.35),10,
      IF(AND(L13="BOTH",E13>0.45),12%))))))))
      If you have a lot of conditions, to avoid errors, I recommend using the IFS function. Read more: The new Excel IFS function instead of multiple IF.

  9. i need to use the multiple if statement to calculate the % growth how can i calculate it ?

  10. I'm trying to write an IF formula that will give me results as such for each additional year in service:
    Year 1 = 80
    Years 2-6 = 120
    Year 6 = 128
    Year 7 = 136
    Year 8 = 144
    Year 9 = 152
    Year 10 = 160

  11. So I'm trying to get the formula correct and it is not picking up. I have 3 classifications of vehicles (A,B,C). The formula I'm working on is if a vehicle is an A and less than 11k miles it is Pool if more than 11k it is a Personal. if a vehicle is an B and less than 9k miles it is Pool if more than 9k it is a Personal. if a vehicle is an C and less than 5k miles it is Pool if more than 5k it is a Personal. is that possible with the IF, AND formula?

    1. Hello! Pay attention to the following paragraph of the article above: Nested IF AND in Excel. For example:

      =IF(AND(A1="A",B1<9000),"Pool", IF(AND(A1="A",B1>9000),"Personal", IF(AND(A1="B",B1<9000),"Pool", IF(AND(A1="A",B1>9000),"Personal",))))

      You can also use the IFS function as described in this article: The new Excel IFS function instead of multiple IF.

      =IFS(AND(A1="A",B1<9000),"Pool", AND(A1="A",B1>9000),"Personal", AND(A1="B",B1<9000),"Pool", AND(A1="A",B1>9000),"Personal")

      I hope it’ll be helpful.

      1. Thank you! It worked. I see where I made the mistake.

  12. I have tried to write an IF AND formula that will give me the results of the given criteria below.

    Criteria 1(A1) Criteria 2(B2) Result
    <500,000 0-25 Small
    3,000,000 0-50 Large
    >3,000,000 51-100 Mega

    =IF(AND(A1<500000,B1<26)"Small","Medium") works. But when I try to nest other IF AND it does not work. I was trying to use IF AND two criteria as shown and nest IF AND with three criteria. Like =IF(AND(A1<500000,B1=500000,A1<=3000000,B1<26)"Medium","Large"))). This did not work. Will IF AND work for this? What is the best formula to use as to capture all the results above?

    1. Hi! You can find the examples and detailed instructions in this guide: Nested IF AND statements. Based on this information, the formula could be as follows:

      =IF(AND(A1<500000,B1<26),"Small",IF(AND(A1<3000000,B1<50),"Large",IF(B1<100,"Mega","")))

      1. I have a scenario where I tried to rank customers as Large, Medium, Small, and Informal. If cells A3 and B3 contain values for a customer for 2021 and 2022 respectively. The condition for “Large” is both A3 and B3 being greater than or equal to 5,000,000, or A3 being greater than or equal to 5,000,000. The condition for “Medium” is both A3 and B3 being greater than 1,000,000 but less than 5,000,000, or A3 being greater than 1,000,000 but less than 5,000,000. The condition for “Small” is both A3 and B3 being greater than 300,000 but less than or equal to 1,000,000, or A3 being greater than 300,000 but less than or equal to 1,000,000. The condition for “Informal” is both A3 and B3 being less than or equal to 300,000, or A3 being less than or equal to 300,000.

        A little adjustment to my earlier question.

  13. Is there a formula where it will tally the number of times a specific phrase of words or a specific number is used in a range of cells which span 4 columns?

  14. wanted to see if you can help with reading negative numbers, probably is much easier than expected.

    So far I have tried changing the format of the numbers but didn't work, see below example

    my formula

    =IF(AND(G3>=$AF$3,G3=$AF$4,G3=$AF$5,G3=$AF$6,$AE$6,""))))

    Table array with a mixture of positive and negative numbers

    Day from Day to
    -28 -21
    -21 -14
    -13 -7
    0 14
    15 28

    1. Hi! Unfortunately, your formula is written incorrectly, and I can't guess what it means and what you were trying to achieve. Please explain your problem in detail.

      1. The table array didn't help, sorry about that

        I have a list of numbers (both positive and negative) and based on the negative numbers I wanted to output
        i.e. if between -5 and -10 "1 week"
        -11 and -20 "2 week"
        -21 and -28 "3week" etc

        This works for positive numbers, its negative numbers which is proving difficult. I was hoping the IF AND statement would work for this scenario but doesn't

        1. Hi! Sorry, I do not fully understand the task. Your clarification did not make the question any clearer. How do you get weeks from numbers. Your question is not entirely clear, please specify.

  15. Hello,
    Would appreciate your help on this! So my formula isn't ignoring the negative numbers like I expected. I also tried fixing my problem with if and statements but the way in which I put it doesn't help either. * note I don't have MINIFS so i used nested if statement.

    =MIN(IF('Train '!B:B=Sheet1!A1,IF('Train '!E:E>=Sheet1!K2,'Train '!E:E0,'Train '!F:F))))

    1. Hi! I can't check your formula as I don't have your data, and it's not clear from your explanation what you want to do.

  16. I am going to explain what I want the formula to do since it is not printing the formula. I apologize for the multiple emails! I want C5 to be evaluated and, if the number is less than 500, I want it to assign the cost of $500, if that number is greater than 500 but less than 1,000, I want it to assign the cost of $1,000, if that number is greater than 1,000 but less than 1,500, I want it to assign the cost of $1,500...

    I have numbers in the column that I want to evaluate and assign a cost for that range from under 500 to over 13,500. Am I going about this the best way? Thanks in advance for your help!

  17. I want to use an IF statement with multiple conditions but can't seem to get the formula just right. Here is the formula I am trying to use that comes back with the error "The formula is missing an opening or closing parenthesis. =IF(AND(C550010001500<2000, "2000")

    I want it to evaluate the number in C5 and, based on multiple conditions, assign a cost associated with that range. Help! I'm desperate!

    1. Hi! Please re-check the article above since it covers your task. All conditions in the AND operator must be enclosed in parentheses. Your formula is written incorrectly, but there should be a parenthesis after the number 2000)

  18. Hello,

    I am trying to write a formula that does the following:

    I want the function to look at a cell (G9) and determine if it's greater than 0, then compare a column of numbers (C4:C7) to see if there is a match to the largest number in the column by comparing it to another cell (C4) and return a 1 if both conditions are met. This is what I have so far:

    =IF(AND(G9>0,(MAX(C4:C7)=C4)), 1, 0)
    IF Grand Total for Delta is greater than 0 and Cadets in current row is equal to the biggest number in Reg column, then return a 1, otherwise 0

    I also need to keep a running total of Grand Total for Delta (G9). G9 is my remainder and I want to assign the remainder one at a time to the largest to smallest numbers in my array. So my formula must also take into account when my remainder is 0 (But also not changing G9).

    This is what I want the data to look like:

    A B C D E F G
    3. Region Cadets Reg Det Aggregate Carryover Delta
    4. NE 411 41 23 15 3 0
    5. NW 354 35 19 10 6 0
    6. SE 493 49 34 8 6 1
    7. SW 449 44 29 10 5 1

    9. Grand Total 1,707 169 105 43 20 2

    I had a Grand Total Delta of 2, therefore SE and SW Regions receive 1 each

    1. Hi! Based on your description, it is hard to completely understand your task. I will try to assume that your formula in G7 refers to cell G9. The value in G9 is given as the sum of G4:G7. You have a circular reference.

  19. I am trying to get that "Yes" and "No" TAT. I don't get the formula well.
    =IF(AND(E12>23,E12>10),"No",IF(AND(E12<23,E12<10),"Yes","No"))
    I also used this formula but it didn't push through with "Yes" No".
    =IF(AND(O12,"Building to Building",E12""),"Yes","")=IF(AND(O12,"Within the Floor",E12""),"Yes","")
    Type of Request SLA
    Within the Floor 10
    Floor to Floor 10
    Building to Building 23

    1. Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Note how to correctly use IF AND is described in the article above.

  20. Pleas e help,
    How to create a formula for the following information

    Column A Column B Column C Column D Column E Column F Column G
    Milestone date Amount Paid on Milestone date Amount Paid on Total Paid

    Problem: I want to add the total amounts paid in column G (total paid) with the amounts of columns B and E ONLY if there is a date in columns C and/or F.

    Really appreciate your help

    1. Hi! To find a sum with multiple conditions, use the SUMPRODUCT function. To determine the date in a cell, use the ISNUMBER function because the date in Excel is written as a number.

      =SUMPRODUCT((G1:G10+B1:B10+E1:E10)*ISNUMBER(C1:C10)*ISNUMBER(F1:F10))

      I hope my advice will help you solve your task.

  21. I pull service numbers every quarter from our database and put into a spreadsheet because I'll be reporting them by county to 25 entities (some get the results of 1 county, some up to 7 counties) and each county has its own tab in the workbook. The spreadsheet compares the current year to 2 previous years in 15+ categories (rows). I have 3 YTD columns (one for this year and one for each of the last two) as well as 12 columns representing each of the 4 quarters for each year. It never fails that when the next quarter comes around I'll forget to update one of the previous YTD calculations and my numbers are all skewed.
    So for example, in Q1 a particular cell should read "=c3", and then Q2 the formula would be "=c3+f3", with Q3 adding in i3 and Q4 adding in L3.
    Is there a way I can get it to choose which calculation is done if I add a quarter reference somewhere. If the reference cell says 2 it'll return the result of "=c3+f3", if it says 4, it'll return the result of "c3+f3+i3+l3"?

  22. Ugh, I'm stuck here...trying to figure out how to pull data within a range and it's not working.

    Basically I can pull the info I need from the C column with this formula on anything that matches F as 7 or greater:

    =IF(F3:F5>6.9,C3:C5,"")

    I can also do the same for anything 3 and under by using: =IF(F3:F5<3.1,C3:C5,"")

    However, I want to pull the same data for the range 4-6 and I can't figure out how...I've tried a couple of things including:
    =IF(AND(F3:F5<3.1,F3:F5 or 3.9 and <6.1 I can't figure out the formula...what am I missing?

      1. Ah. Thank you thank you thank you!!!

  23. I need someone to tell me why I am not getting proper calculations:

    Below Time sheet calculations:

    Monday through Friday will have a "Y" in the E column. Y represent 7.5 hours

    However, if there is a value in G column (represent time off/leave time), needs to be deducted from 7.5 hour rate

    E Column contains either Y or N (Y = Worked 7.5 hrs, N = not work 0 hours
    G Column contains numbers (leave time). If N is used, the value is added to total hours in the total cell. However, if a person enters a Y and worked 1/2 day and took 1/2 off, as an example: 4.5 hours worked, and 3 hours vacation time. The day's total is 7.5. That is why in the "false" box the line (7.5 - G10) is added. But below formulas is not adding up or reducing hours when value Y changed to N and vice versa.

    =SUM(IF(AND(E10="Y",G10=" "),7.5,(7.5-G10)))+SUM(IF(AND(E11="Y",G11=" "),7.5,(7.5-G11)))+SUM(IF(AND(E12="Y",G12=" "),7.5,(7.5-G12)))+SUM(IF(AND(E13="Y",G13=" "),7.5,(7.5-G13)))+SUM(IF(AND(E14="Y",G14=" "),7.5,(7.5-G14)))+SUM(IF(AND(E15="Y",G15=" "),7.5,(7.5-G15)))+SUM(IF(AND(E16="Y",G16=" "),7.5,(7.5-G16)))+SUM(IF(AND(E17="Y",G17=" "),7.5,(7.5-G17)))

    When I put this in the cell, I get weird number 52.20 of something, not 37.5 if there was G column has no data. The (7.5-G10) is simply want

    I am sure there may be a better way to calculate. I am still just going about it the long way...at this time.

    I appreciate if you have a better solution...email me if you can.

    Thank you everyone for participating on this discussion.

    Gil
    email: gilinnc @ gmail . com

  24. Hi Sir,

    I need a single formula to validate multiple OR & AND to show true or false value.

    Case1: If A2=Apple is harvested in large scale, B2=Fruit, C2=1
    here if A2 has a specific text "Apple" and B2=Fruit and C2=1, the value should be "True"
    OR
    Case2: if A2=Mango is harvested in large scale, B2=Fruit, C2=2
    here if B2=Fruit & C2=2, the value should be True

    So either(Case1 or Case2) meets the above cateria the value should be true.

  25. RATING Percentage
    5 95-100%
    4 75-94%
    3 45-74%
    2 31-44%
    1 0-30%

    I want to insert if multiple conditions formula to rate 1-5 based on the percentage for above performance index. please Guide in excel how to apply if formula

  26. THANKS A LOT

  27. Can someone help me to find out the correct date using the following conditions.
    ​Earlier than July 1, 1949 - Output Age 70.6
    On or after July 1, 1949, but before January 1, 1951 - Output Age 72
    On or after January 1, 1951, but before January 1, 1960 - Output Age 73
    On or After January 1, 1960 Output Age 75

    Output Should be in Date Format.

    Thanks in Advance

  28. HI, i have a real time data in dates and time (1/1/2022 1:00am) for a year but i want to reduce the data for January by 30% , for February by 60% and so on. please help with this .thanks

  29. I have a spreadsheet where the Date in column N is dependent on data in columns H and J.
    Results for each outcome are held in columns M2, V2 and U2.
    i.e If the length of service in column J is less than 6 years, the date in column N would point to the formula in column M. If the length of service in column J is 6 years or more AND start age in column H is less than 18 then the date in column N would point to the formula in column V but if the length of service in column J is 6 years or more AND start age in column H is 18 or more then the date in column N would point to the formula in column U. How would I combine all this to bring back the desired results?

  30. I am trying to convert the following information into a formula for BMI Status on a spread sheet, but I have been unsuccessful all I get is a value error and can't figure out why. What is wrong with the formula?

    Underweight 40

    Here is my formula:

    =IF(AND(G39=18.5, G39=25, G39=30, G39=35, G39=40),"Obese Class 3","")

  31. How to formulate? IF row 2 and 3 has same EEID, then it should sum up the amount of EEID in rows 2 and 3. Then the result in ROw 3 since same EEID in row 2 should be 0. If not same EE ID like Row 1 and 2 result should be same amount in row 1
    Thanks

    EEID Code Amount Result
    1 1856996 VNGW12 3,060,000.00 3,060,000.00
    2 1716885 VNGW18 1,179,150.00 2,054,150.00
    3 1716885 VNGW18 875,000.00 - 0.00
    4 1716887 VNGW18 875,000.00 875,000.00

  32. I have problem in computing results with excel.
    I want to combine two sheets together and make it print REMARK that contains PASS OR the subjects failed

  33. I am creating an order form and I need the cell to only allow 240 or greater and in multiples of 20. Is it possible to do a nested formula in data validation that will do this?
    I use the =MOD(F64,20)=0 formula for other cells in data validation

      1. HI,
        This formula =AND(MOD(F64,20)=0,F64>=240) worked great! Thank you so much!

        Am I able to add a function to add other cells to meet the 240 minimum requirement?
        Examples:
        F64 = 240 (still needs to be in multiples of 20)
        or F64+F66 = 240 (still needs to be in multiples of 20)
        or F64+F66+F68=240 (still needs to be in multiples of 20)
        or F64+F66+F68+F70=240 (still needs to be in multiple of 20)

  34. Hi!

    I'm trying to count up from 1 (2, 3, 4...), starting in column A2 (continuing down A3, A4, A5, A6....). However, I only want the counting up to proceed if two conditions are met, those being that B2 and C2 both = 1. So counting up will continue only if those are met, otherwise, I want the numbers to remain the same until the conditions are met.

    The problem is, I cannot get the following equation to work, as it only seems to return the FALSE value, and I can't find any examples of how to format a formula in a "Value if true (or false" field for the IF(AND) scenario.

    Here's the equation:
    =IF(AND(B3="1",C3="1"), A2+1, A2)

    Any advice will be appreciated!

      1. The revised IF function worked, thanks Alexander, much appreciated!

  35. I have this formulas which i want it to work together in the same cell, I don't know which one should come first before the other.
    =IF(C5=39,"468",IF(C5=38,"456",IF(C5=37,"444",IF(C5=36,"432","no")))) Settings!E3-SUMIF('P&S'!H$5:H$34,'P&S'!B5,'P&S'!J$5:J$34)
    The cell cell is already displaying 480 pieces, so when they buy any piece it should display the remaining and also still display the SumIF function.

    In details is a stock management template and this, Settings!E3-SUMIF('P&S'!H$5:H$34,'P&S'!B5,'P&S'!J$5:J$34) do addition of pieces in cell D5 but I want it to also display the remaining of the pieces when a box is bought because we have 12 pieces in a box.
    I used the ampersand and it only do concatenate, so please can you help me with some unique idea?

    1. Hi! Unfortunately, I don't really understand what result you want to get. Explain in more detail, give an example of the expected result.

  36. I am trying to calculate Gen X, Gen Y & Gen Z using Birth year(W2 in below formula), but i get #value error. Pleas help

    =IF(AND(W2>=1965,W2=1977,W2=1996,W2<=2015),"Gen Z","NA"))

  37. I am trying to do a nesting formula using greater or less than. Example
    =SUMIF(B21,"=",M21)*OR(B21">",N21)+B21*B13*2*OR(B21,">",O21)+B21*B13*3

    It is multiplying the values to show 5 times the value $$ amount I need.
    How do I formulate this to pick one or the other or the other range?

    Is this an easy fix or do I need to do something more detailed?

    Thanks for the assistance.

    1. Hi! The arguments of the SUMIF function cannot be other functions. I can't check your formula because it doesn't work. Also, I don't really understand what you want to calculate. If you explain it in more detail, I will try to help.

  38. =IF(AND(A2<=20,E2<20),"Pass","Fail") This formula works for the base, but the A column is ages and will have ranges in comparison to value allowable in column E.

    Column A ranges Coulumn E
    21-27 <=22
    28-39 =40 <=26

    Is there a way to write a formula for IF(AND for the value in Column A compared to the ranges and once it fits a range the number in Column E value is within tolerance for that range in Column A ex. The number in Column A is 26 so it fits the range of 21-27 and the number in column E is <=22 so it would result in "Pass" as the output.

    1. Unfortunately, I can't understand what you want to do. Column E is not a number, but text. You can't compare text. Explain your question.

      1. A B C D E F
        Age HT WT Waist BF% P/F =IF(AND(A2<=20,E2=40 and there are corresponding allowances for BF% for each range 20,22,24,26 respectively. I want to be able to when you enter the data in Columns A and E it to output in Column F either Pass of Fail. I can get it for a single range with the formula in the original post, but not sure if there is a way to have a IF(AND or IF(OR formula that refers to age in Column A associates that to one of the age ranges and compares to the BF % in Column E and if it is at or below the allowable BF% for the age range that the number in Column A is in, Column F would be the output of either Pass of Fail.

        1. I'm sorry, but your second message has no information to understand what you want to do. Write concretely what is written in A1 and E1 and what the result should be in F1. All variants.

        2. I tried to add a screenshot, but it would not let me and the spacing I had on my last reply did not stick. Column A is age, Column B is height, Column C is Weight, Column D is waist measurement, Column E is BF%, Column F is where I need the output to be pass or fail. When you enter the data in Columns A and E it to output in Column F either Pass of Fail. I can get it for a single range with the formula in the original post, but not sure if there is a way to have a IF(AND or IF(OR formula that refers to age in Column A associates that to one of the age ranges and compares to the BF % in Column E and if it is at or below the allowable BF% for the age range that the number in Column A is in, Column F would be the output of either Pass of Fail. There are four ranges for the age information and a corresponding BF % associated with each age range. Age ranges for Column A are =40 with Column E corresponding BF% being at or below 20, 22, 24, 26 respectively. Thank you!

          Current formula in Column F is =IF(AND(A2<=20,E2<20),"Pass","Fail") This is just for the first set of corresponding information of age <=20 with BF% <=20

  39. can you help me to make a formula for
    1f less than 100000 commission 0.03%
    100000 to 199999 commission 0.04%
    200000 to 500000 commission 0.04%
    more than 500000 commission 0.05%

  40. Hi, I would like to ask is there any other formula to replace this formula?

    Basically it is a function says: Matching the H1 (header) with BH2 (header in data lines), if this is the result able to match with the header --> take this AND if this is the result not able to match with the header, continue to match the next column in data lines.

    - "if and" functions
    - few same header contains in data lines to look up the result
    (column "BH" to "DR" are the data)
    (column "A" to "BF" contains the look up array/ headers)

    Formula:
    =if(and(H$1=$BH$2,$BH16""),$BH16,if(and(H$1=$BI$2,$BI16""),$BI16,if(and(H$1=$BJ$2,$BJ16""),$BJ16,if(and(H$1=$BK$2,$BK16""),$BK16,if(and(H$1=$BL$2,$BL16""),$BL16,if(and(H$1=$BM$2,$BM16""),$BM16,if(and(H$1=$BN$2,$BN16""),$BN16,if(and(H$1=$BO$2,$BO16""),$BO16,if(and(H$1=$BP$2,$BP16""),$BP16,if(and(H$1=$BQ$2,$BQ16""),$BQ16,if(and(H$1=$BR$2,$BR16""),$BR16,if(and(H$1=$BS$2,$BS16""),$BS16,if(and(H$1=$BT$2,$BT16""),$BT16,if(and(H$1=$BU$2,$BU16""),$BU16,if(and(H$1=$BV$2,$BV16""),$BV16,if(and(H$1=$BW$2,$BW16""),$BW16,if(and(H$1=$BX$2,$BX16""),$BX16,if(and(H$1=$BY$2,$BY16""),$BY16,if(and(H$1=$BZ$2,$BZ16""),$BZ16,if(and(H$1=$CA$2,$CA16""),$CA16,if(and(H$1=$CB$2,$CB16""),$CB16,if(and(H$1=$CC$2,$CC16""),$CC16,if(and(H$1=$CD$2,$CD16""),$CD16,if(and(H$1=$CE$2,$CE16""),$CE16,if(and(H$1=$CF$2,$CF16""),$CF16,if(and(H$1=$CG$2,$CG16""),$CG16,if(and(H$1=$CH$2,$CH16""),$CH16,if(and(H$1=$CI$2,$CI16""),$CI16,if(and(H$1=$CJ$2,$CJ16""),$CJ16,if(and(H$1=$CK$2,$CK16""),$CK16,if(and(H$1=$CL$2,$CL16""),$CL16,if(and(H$1=$CM$2,$CM16""),$CM16,if(and(H$1=$CN$2,$CN16""),$CN16,if(and(H$1=$CO$2,$CO16""),$CO16,if(and(H$1=$CP$2,$CP16""),$CP16,if(and(H$1=$CQ$2,$CQ16""),$CQ16,if(and(H$1=$CR$2,$CR16""),$CR16,if(and(H$1=$CS$2,$CS16""),$CS16,if(and(H$1=$CT$2,$CT16""),$CT16,if(and(H$1=$CU$2,$CU16""),$CU16,if(and(H$1=$CV$2,$CV16""),$CV16,if(and(H$1=$CW$2,$CW16""),$CW16,if(and(H$1=$CX$2,$CX16""),$CX16,if(and(H$1=$CY$2,$CY16""),$CY16,if(and(H$1=$CZ$2,$CZ16""),$CZ16,if(and(H$1=$DA$2,$DA16""),$DA16,if(and(H$1=$DB$2,$DB16""),$DB16,if(and(H$1=$DC$2,$DC16""),$DC16,if(and(H$1=$DD$2,$DD16""),$DD16,if(and(H$1=$DE$2,$DE16""),$DE16,if(and(H$1=$DF$2,$DF16""),$DF16,if(and(H$1=$DG$2,$DG16""),$DG16,if(and(H$1=$DH$2,$DH16""),$DH16,if(and(H$1=$DI$2,$DI16""),$DI16,if(and(H$1=$DJ$2,$DJ16""),$DJ16,if(and(H$1=$DK$2,$DK16""),$DK16,if(and(H$1=$DL$2,$DL16""),$DL16,if(and(H$1=$DM$2,$DM16""),$DM16,if(and(H$1=$DN$2,$DN16""),$DN16,if(and(H$1=$DO$2,$DO16""),$DO16,if(and(H$1=$DP$2,$DP16""),$DP16,if(and(H$1=$DQ$2,$DQ16""),$DQ16,if(and(H$1=$DR$2,$DR16""),$DR16,if(and(H$1=$DS$2,$DS16""),$DS16,if(and(H$1=$DT$2,$DT16""),$DT16,if(and(H$1=$DU$2,$DU16""),$DU16,if(and(H$1=$DV$2,$DV16""),$DV16,if(and(H$1=$DW$2,$DW16""),$DW16,if(and(H$1=$DX$2,$DX16""),$DX16,if(and(H$1=$DY$2,$DY16""),$DY16,if(and(H$1=$DZ$2,$DZ16""),$DZ16,if(and(H$1=$EA$2,$EA16""),$EA16,if(and(H$1=$EB$2,$EB16""),$EB16,if(and(H$1=$EC$2,$EC16""),$EC16,if(and(H$1=$ED$2,$ED16""),$ED16,if(and(H$1=$EE$2,$EE16""),$EE16,if(and(H$1=$EF$2,$EF16""),$EF16,if(and(H$1=$EG$2,$EG16""),$EG16,if(and(H$1=$EH$2,$EH16""),$EH16,if(and(H$1=$EI$2,$EI16""),$EI16,if(and(H$1=$EJ$2,$EJ16""),$EJ16,if(and(H$1=$EK$2,$EK16""),$EK16,if(and(H$1=$EL$2,$EL16""),$EL16,if(and(H$1=$EM$2,$EM16""),$EM16,if(and(H$1=$EN$2,$EN16""),$EN16,if(and(H$1=$EO$2,$EO16""),$EO16,if(and(H$1=$EP$2,$EP16""),$EP16,if(and(H$1=$EQ$2,$EQ16""),$EQ16,if(and(H$1=$ER$2,$ER16""),$ER16,if(and(H$1=$ES$2,$ES16""),$ES16,if(and(H$1=$ET$2,$ET16""),$ET16,if(and(H$1=$EU$2,$EU16""),$EU16,if(and(H$1=$EV$2,$EV16""),$EV16,if(and(H$1=$EW$2,$EW16""),$EW16,if(and(H$1=$EX$2,$EX16""),$EX16,if(and(H$1=$EY$2,$EY16""),$EY16,if(and(H$1=$EZ$2,$EZ16""),$EZ16,if(and(H$1=$FA$2,$FA16""),$FA16,if(and(H$1=$FB$2,$FB16""),$FB16,if(and(H$1=$FC$2,$FC16""),$FC16,if(and(H$1=$FD$2,$FD16""),$FD16,if(and(H$1=$FE$2,$FE16""),$FE16,if(and(H$1=$FF$2,$FF16""),$FF16,if(and(H$1=$FG$2,$FG16""),$FG16,if(and(H$1=$FH$2,$FH16""),$FH16,if(and(H$1=$FI$2,$FI16""),$FI16,if(and(H$1=$FJ$2,$FJ16""),$FJ16,if(and(H$1=$FK$2,$FK16""),$FK16,if(and(H$1=$FL$2,$FL16""),$FL16,if(and(H$1=$FM$2,$FM16""),$FM16,if(and(H$1=$FN$2,$FN16""),$FN16,if(and(H$1=$FO$2,$FO16""),$FO16,if(and(H$1=$FP$2,$FP16""),$FP16,if(and(H$1=$FQ$2,$FQ16""),$FQ16,if(and(H$1=$FR$2,$FR16""),$FR16,if(and(H$1=$FS$2,$FS16""),$FS16,if(and(H$1=$FT$2,$FT16""),$FT16,if(and(H$1=$FU$2,$FU16""),$FU16,if(and(H$1=$FV$2,$FV16""),$FV16,if(and(H$1=$FW$2,$FW16""),$FW16,if(and(H$1=$FX$2,$FX16""),$FX16,if(and(H$1=$FY$2,$FY16""),$FY16,if(and(H$1=$FZ$2,$FZ16""),$FZ16,if(and(H$1=$GA$2,$GA16""),$GA16,if(and(H$1=$GB$2,$GB16""),$GB16,if(and(H$1=$GC$2,$GC16""),$GC16,if(and(H$1=$GD$2,$GD16""),$GD16,if(and(H$1=$GE$2,$GE16""),$GE16,if(and(H$1=$GF$2,$GF16""),$GF16,if(and(H$1=$GG$2,$GG16""),$GG16,if(and(H$1=$GH$2,$GH16""),$GH16,if(and(H$1=$GI$2,$GI16""),$GI16,if(and(H$1=$GJ$2,$GJ16""),$GJ16,if(and(H$1=$GK$2,$GK16""),$GK16,if(and(H$1=$GL$2,$GL16""),$GL16,if(and(H$1=$GM$2,$GM16""),$GM16,if(and(H$1=$GN$2,$GN16""),$GN16,if(and(H$1=$GO$2,$GO16""),$GO16,if(and(H$1=$GP$2,$GP16""),$GP16,if(and(H$1=$GQ$2,$GQ16""),$GQ16,if(and(H$1=$GR$2,$GR16""),$GR16,if(and(H$1=$GS$2,$GS16""),$GS16,if(and(H$1=$GT$2,$GT16""),$GT16,if(and(H$1=$GU$2,$GU16""),$GU16,if(and(H$1=$GV$2,$GV16""),$GV16,if(and(H$1=$GW$2,$GW16""),$GW16,if(and(H$1=$GX$2,$GX16""),$GX16,if(and(H$1=$GY$2,$GY16""),$GY16,if(and(H$1=$GZ$2,$GZ16""),$GZ16,if(and(H$1=$HA$2,$HA16""),$HA16,if(and(H$1=$HB$2,$HB16""),$HB16,if(and(H$1=$HC$2,$HC16""),$HC16,if(and(H$1=$HD$2,$HD16""),$HD16,if(and(H$1=$HE$2,$HE16""),$HE16,if(and(H$1=$HF$2,$HF16""),$HF16,if(and(H$1=$HG$2,$HG16""),$HG16,if(and(H$1=$HH$2,$HH16""),$HH16,if(and(H$1=$HI$2,$HI16""),$HI16,if(and(H$1=$HJ$2,$HJ16""),$HJ16,if(and(H$1=$HK$2,$HK16""),$HK16,if(and(H$1=$HL$2,$HL16""),$HL16,if(and(H$1=$HM$2,$HM16""),$HM16,if(and(H$1=$HN$2,$HN16""),$HN16,if(and(H$1=$HO$2,$HO16""),$HO16,if(and(H$1=$HP$2,$HP16""),$HP16,if(and(H$1=$HQ$2,$HQ16""),$HQ16,if(and(H$1=$HR$2,$HR16""),$HR16,if(and(H$1=$HS$2,$HS16""),$HS16,if(and(H$1=$HT$2,$HT16""),$HT16,if(and(H$1=$HU$2,$HU16""),$HU16,if(and(H$1=$HV$2,$HV16""),$HV16,if(and(H$1=$HW$2,$HW16""),$HW16,if(and(H$1=$HX$2,$HX16""),$HX16,if(and(H$1=$HY$2,$HY16""),$HY16,if(and(H$1=$HZ$2,$HZ16""),$HZ16,if(and(H$1=$IA$2,$IA16""),$IA16,if(and(H$1=$IB$2,$IB16""),$IB16,if(and(H$1=$IC$2,$IC16""),$IC16,if(and(H$1=$ID$2,$ID16""),$ID16,if(and(H$1=$IE$2,$IE16""),$IE16,if(and(H$1=$IF$2,$IF16""),$IF16,if(and(H$1=$IG$2,$IG16""),$IG16,if(and(H$1=$IH$2,$IH16""),$IH16,if(and(H$1=$II$2,$II16""),$II16,if(and(H$1=$IJ$2,$IJ16""),$IJ16,if(and(H$1=$IK$2,$IK16""),$IK16,if(and(H$1=$IL$2,$IL16""),$IL16,if(and(H$1=$IM$2,$IM16""),$IM16,if(and(H$1=$IN$2,$IN16""),$IN16,if(and(H$1=$IO$2,$IO16""),$IO16,if(and(H$1=$IP$2,$IP16""),$IP16,if(and(H$1=$IQ$2,$IQ16""),$IQ16,if(and(H$1=$IR$2,$IR16""),$IR16,if(and(H$1=$IS$2,$IS16""),$IS16,if(and(H$1=$IT$2,$IT16""),$IT16,if(and(H$1=$IU$2,$IU16""),$IU16,if(and(H$1=$IV$2,$IV16""),$IV16,if(and(H$1=$IW$2,$IW16""),$IW16,if(and(H$1=$IX$2,$IX16""),$IX16,if(and(H$1=$IY$2,$IY16""),$IY16,if(and(H$1=$IZ$2,$IZ16""),$IZ16,if(and(H$1=$JA$2,$JA16""),$JA16,if(and(H$1=$JB$2,$JB16""),$JB16,if(and(H$1=$JC$2,$JC16""),$JC16,if(and(H$1=$JD$2,$JD16""),$JD16,if(and(H$1=$JE$2,$JE16""),$JE16,if(and(H$1=$JF$2,$JF16""),$JF16,if(and(H$1=$JG$2,$JG16""),$JG16,if(and(H$1=$JH$2,$JH16""),$JH16,if(and(H$1=$JI$2,$JI16""),$JI16,if(and(H$1=$JJ$2,$JJ16""),$JJ16,if(and(H$1=$JK$2,$JK16""),$JK16,if(and(H$1=$JL$2,$JL16""),$JL16,if(and(H$1=$JM$2,$JM16""),$JM16,""))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

    1. Hi! It is very difficult to understand a formula that contains unique references to your data, which I don't have. What result do you want to get? Give an example of the source data and the expected result.

    2. Hi,

      Any other formula able to find the result from data lines?

      - currently using "if and" functions to get result
      - data lines contains same header
      - data lines contains 2 same row except the value amount

  41. Hi! Please i have two sheets in my workbook and the first sheet contains setting which want it to reflect in my second sheet. This is how I want it, I have products and number of products in setting sheet and have created a table with define name. So i now validated the table in the second sheet. I want any product i select in the validated cell also display the number of products. Below is the formula which does not want to work proper for me. Thank You =IF(C5=Settings!C3,Settings!D3,IF(C5=Settings!C4,Settings!D4,IF(C5=Settings!C5,Settings!D5,IF(C5=Settings!C6,Settings!D6,IF(C5=Settings!C7,Settings!D7,IF(C5=Settings!C8,Settings!D8,IF(C5=Settings!C9,Settings!D9,IF(C5=Settings!C10,Settings!D10,IF(C5=Settings!C11,Settings!D11,IF(C5=Settings!C12,Settings!D12,IF(C5=Settings!C13,Settings!D13,IF(C5=Settings!C14,Settings!D14,IF(C5=Settings!C15,Settings!D15,IF(C5=Settings!C16,Settings!D16,IF(C5=Settings!C17,Settings!D17,IF(C5=Settings!C18,Settings!D18,IF(C5=Settings!C19,Settings!D19,IF(C5=Settings!C20,Settings!D20,IF(C5=Settings!C21,Settings!D21,IF(C5=Settings!C22,Settings!D22,IF(C5=Settings!C23,Settings!D23,IF(C5=Settings!C24,Settings!D24,IF(C5=Settings!C25,Settings!D25,IF(C5=Settings!C26,Settings!D26,IF(C5=Settings!C27,Settings!D27,IF(C5=Settings!C28,Settings!D28,IF(C5=Settings!C29,Settings!D29,IF(C5=Settings!C30,Settings!D30,IF(C5=Settings!C31,Settings!D31,IF(C5=Settings!C32,Settings!D32, "Not in Store"))))))))))))))))))))))))))))))

  42. I have a cell that displays the current date using the NOW function. I would like to run a formula that multiplies an existing cell value by 1.03 when the Now function = 01/01/2024.

    1. Hi! Create the date you want using the DATE function and use it in the condition in the IF function. For example,

      IF(NOW()=DATE(2024,1,1),A1*1.03,"")

      Since the NOW function returns the date and time, I would recommend using the TODAY function to compare with the date.

  43. I am creating an order form and I am using data validation.
    I need the customer to enter a value equal to or greater than 3024 and in multiple of 36
    I have been using =MOD formula, how do I add the equal to or greater than?

  44. Hi,

    I have 2 columns of data, 1 with gender (F or M) & one with a score. Is is possible to write a formula that identifies those with a specific gender AND score? Can all females with a score of <16 and all males with a score of <27 be identified as "good" for example?

    Thanks for you help

  45. Good morning!
    I'm having trouble making the formula that i want within the excel formulas if you are able to give some insight it would be appreciated, or be able to tell me what i might possibly be doing wrong, the logic in my head to make the formula is this :
    =IF(AND(A1:A75=C1:C75,B1:B75>D1:D75), "Greater", "Less Than")

    I'm trying to compare two pivot tables, i need any column that matches the same name ie: any value from A1:A75 equals to exact values from C1:C75 then compare the same row from B1:B75 against D1:D75.

    I'm trying to have it do: if A1 = C1 and B1 > D1, greater, less than. but since it's a pivot table that changes, i can't get it to work properly. Any insight would be appreciated, Thank you!

      1. If i extra the data from the sheets, is there a way i can compare values from A1 against C1:C75 that will show that value of B1 and then repeat that for A2,A3,A4...

        I'm trying to do it within an if statement but i think that I'm mistaken as I'm trying "=IF((A1=C1:C75),B1, "Not on list")", but if i try to do a similar input to A2 then it is giving me a "spill"

        Thank you again and sorry for multiple questions.

      2. Sad to hear,
        Thank you very much for the response!

  46. Hi, good day to you sir.

    The information is really helpful.
    I would like to ask about the formula, this is correct?

    IF(AND(POLA=1,TARGET/REALISASI>=120),"120%",
    IF(AND(POLA=1,TARGET/REALISASI=120),"120",
    IF(AND(POLA=2,REALISASI/TARGET<120),(REALISASI/TARGET)*100%))))

  47. Hi, how can I make the formula below work?
    Can I combine IF with IF AND

    example:
    =IF(C2<90,"A",IF(AND(B2100),"APPLE","OTHERS"))

    1. Hi! Have you tried the ways described in this blog post? Based on your description, it is hard to completely understand your task. I don't know what you want to do, but maybe this formula will work:

      =IF(C2<90,"A",IF(B2>100,"APPLE","OTHERS"))

  48. Hi

    I am trying to create formula that will give a true or yes (I don't mind which) result if in 3 cells, in different columns, certain codes are present. I have read these pages as thoroughly as I can but I think because I want it to look for more than one code it is not giving a 'true/yes' outcome when the conditions are met. This is the formula I have tried:

    =IF(AND(OR(AB8="GD*",AB8="EX*"),(OR(AU8="GD*",AU8="EX*")),(OR(CH8="GD*",CH8="EX*"))),"YES","NO")

    I also tried:
    =IF(AND(AB2={"EX*","GD*"},AU2={"EX*","GD*"},CH2={"EX*","GD*"}),"YES","NO")

    I need it to come back true if the cells contain a code starting with EX or GD. If all 3 cells do then it should return true/yes. It doesn't have to return anything if the conditions are not true, I don't mind having a blank cell.

    Thank you for your time.

  49. I need a formula to calculate tax slab First 100,000 is 0% Tax Next 350,000 is 25% Next 2,050,000 is 30% Excess of 2,500,000 is 35%
    I have achieved to get the correct result upto 30% Tax slab but I m failing to add the last slab of 35%
    =IF(O8<100000,0,IF((O8-100000)<450000,(O8-100000)*0.25,87500+((O8-450000)*0.3)))
    Kindly assist me

    1. Hi!
      Here is a sample formula that you can use:

      =MIN(C2,100)*0% + MIN(MAX(C2-100,0),350)*25% + MIN(MAX(C2-350-100,0),2050)*30% + MAX(C2-2500,0)*35%

  50. I have an excel file with the following columns (MINS, HOURS FREQUENCY, AVERAGE WEEKLY HOURS), and I am trying to write a formula to calculate the average weekly hours based on an average 12 week period, depending on the answer in the frequency column. Can anyone help with a nested formula

    If frequency = Daily, multiply hours by 5 (based on working days)
    If frequency = weekly, multiple hours by 1
    if frequency = monthly, divide hours by 4
    if frequency = quarterly, divide hours by 12

    Thanks
    Hazel

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