Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 15. Total comments: 2534

  1. Hi I am using the following formula =MAX(0,C105-C106)so that I do not get results with a negative answer (e.g. -356). However if cell 106 is blank i would like to have a zero returned. How do I amend the current formula to include this perameter?

    1. Figured it out myself.
      =IF(AND(ISNUMBER(C105),ISNUMBER(C106)),MAX(0,C105-C106),"")
      Works a treat.

  2. I need a formula that will return the following:
    8 or lower = Pre Contemplation
    8-11 = Contemplation
    12-14 = Preparation
    14 and above = Maintenance

    I have a running total cell that will give me the number. Based upon the result of that cell I need it to return the text that corresponds. The below examples shows the column B totaled then averaged. The average equates to 12 therefore based upon the above table should return "Preparation"

    Column A. Column B
    1 2
    2 4
    3 6
    4 Total 12
    5 Preparation

    Thank you!

    1. Nevermind...figured it out

  3. Hi,
    I want to calculate the sum of the cells if in column B have text "paid" then calculate values from Column A in respective cell and if Column B have text "Un-Paid" then the values from Column A will not calculated... reference list is below...

    Rs 3,000.00 Paid
    Rs 30.00 Paid
    Rs 50.00 Paid
    Rs 5,000.00 Paid
    Rs 10,000.00 Un-Paid
    Rs 10,000.00 Un-Paid
    Rs 5,000.00 Un-Paid
    Rs 330.00 Un-Paid
    Rs 650.00 Paid
    Rs 785.00 Un-Paid

    Looking forward for prompt response please...

    Thanks

    1. If you want something simple you can have another column that determines if column b says paid.

      =IF(B1="PAID",A1,0) - this goes in column c
      Sum values in column c.

      1. Just realized you can use sumif here

        =SUMIF(B1:B10,"PAID",A1:A10)

        Change the range as needed.

  4. Looking for help with: ie. if d43 - 3.75 is <= 0 then equal blank if not then equal d43 - 3.75

    1. =IF(d43-3.75<=0,"",d43-3.75)

  5. help me to calcute my commission my commission is .5% but condition is minimum 2 rs and maximum 12 rs

    1. =IF(and(A1>2,A1<12),A1*0.5%,"")

  6. =IF(V2=-2,"C","L"),IF(AND,V2>-3,"E")

    THIS DOES NOT WORK. NEED TO SHOW C IF 0,-1,-2,L IF GREATER THAN 0 AND E IF -3

    1. =IF(V2>0,"L",IF(AND(V2>=-2,V2<=0),"C",IF(V2<-2,"E","")))

  7. Can someone help me with the following logical statement:
    IF
    "TIMEIN PV"<"TIMEIN DH" AND "TIME IN DH"<"TIMEOUT PV"<"TIMEOUT DH"
    OR
    "TIMEIN DH"<"TIMEIN PV"<"TIMEOUT DH"
    OR
    "TIMEIN DH"<"TIMEIN PV""TIMEOUT DH",
    ALERT,
    0)
    The TIMEIN or TIMEOUT (DH or PV) are formatted in 24:00 hours. I want to create an alert when two schedules are clashing like this:
    PV (Private) (in)xxxxxxxxx(out) (in)xxxxxxxxxxxx(out) (in)xxxxxxxxx(out)
    DH (Dayhab) in ================================================= out

    1. Hello Ash,

      Thank you for your comment. As far as I can see, there is a symbol missing in the last part of your formula - "TIMEIN DH"<"TIMEIN PV"”TIMEOUT DH”. Once you add the necessary logical operator (>,<,<>,=) there, it should work. I hope this will help you.

  8. Basic chart Final chat
    Person Product Quantiy Person Product Quantiy
    A Pen 200 C Rubber Forumula to get quantiy from basic chart
    A Rubber 500
    B Pen 400
    C Pen 800
    C Rubber 200

    1. You'll need a few formulas

      =SUMIF(B1:B10,"PEN",C1:C10)
      Formula above sums the values in C1:C10 for the cells in B1:B10 that has "PEN" in it.
      Copy the formula for other products you have.
      =SUMIF(B1:B10,"XXXX",C1:C10)**change the XXXX into the product name**

  9. Help with a formula please:
    If A1<=4 and B1 <=2 then C1 is G
    If A1 =1 and B1 =3 Then C1 is G
    If A1 =5 and B1 =1 Then C1 is A
    If A1 =3,4 and B1 =2 Then C1 is A
    If A1 =2,3 and B1 =3 Then C1 is A
    If A1 =1,2 and B1 =4 Then C1 is A
    If A1 =5 and B1 =2 Then C1 is HA
    If A1 =4 and B1 =3 Then C1 is HA
    If A1 =3 and B1 =4 Then C1 is HA
    If A1 =1,2 and B1 =5 Then C1 is HA
    If A1 =5 and B1 =3 Then C1 is R
    If A1 =4 and B1 =4 Then C1 is R
    If A1 =3 and B1 =5 Then C1 is R
    Many thanks.

    1. Hello Adrian,
      First, write down your conditions in different columns:
      – Column G: G1 - 4, G2 - 1, G3 - 5, G4 – 3, etc.
      – Column H: H1 - 2, H2 - 3, H3 – 1, etc.
      – Column I: I1 - "G", I2 - "G", I3 - "A"

      Then paste the array function below in C1:

      =IFERROR(INDEX($I$1:$I$17, MATCH(A1&B1, $G$1:$G$17&$H$1:$H$17, 0)), "")

      Press Ctrl + Shift + Enter for this array function to work and copy this formula down the column. Hope this is exactly what you need. :)

  10. I need a formula that essentially says the following:
    if a2 is " a" and b2 is "no" c2 is 1
    if a2 is "a" and b2 is "yes" c2 is 2
    if a2 is "b" and b2 is "no" c2 is 3
    if a2 is "b" and b2 is "yes" c2 is 4
    I tried if(or(and) but I can only get two results with that formula. Can someone help?

    1. Sir, Please write this formula in C2.

      =IF(AND(A2="A",B2="NO"),1,IF(AND (A2="A",B2="YES"),2,IF(AND(A2="B",B2="NO"),3,IF(AND (A2="B",B2="YES"),4,""))))

    2. Hello,
      If we understand your task correctly, you need to create a few helper columns:
      - Column G: G1 - "a", G2 - "a", G3 - "b", G4 - "b"
      - Column H: H1 - "no", H2 - "no", H3 - "yes"
      - Column I: I1 – 1, I2 – 2, I3 – 3, I4 – 3

      Then enter the following array function into C1:

      =INDEX($I$2:$I$5, MATCH(A2&B2, $G$2:$G$5&$H$2, $H$5, 0))

      Press Ctrl + Shift + Enter for this array function to work and copy this formula down the column. Hope this solution will work for you.

    3. Should be typed in C2
      =IF(OR(AND(A2=a,B2=no)),"1",IF(OR(AND(A2=a,B2=yes)),"2",IF(OR(AND(A2=b,B2=no),"3",IF(OR(AND(A2=b,B2=yes)),"4"))))

    4. =IF(A2="a", IF(B2="no",1,2), IF(A2="b", IF(B2="no",3,4)))

    5. =IF((AND(A2="A",B2="NO")),1,IF((AND(A2="A",B2="YES")),2,IF((AND(A2="B",B2="NO")),3,IF((AND(A2="B",B2="YES")),4,0))))

  11. good tips, but pls. note that this is wrong: "=IF((AND(C2>=20, D2>=30))"
    it works only with ; instead of , like this: =IF((AND(C2>=20;D2>=30))

    1. Hi Bácskai,

      That depends on which List Separator is set in your Regional Settings: in North America it's the comma, in most European countries - semicolon.

  12. How I calculate using the condition of 25% of old salary ,if qualification is MA and sex Female, 15% of old salary ,if qualification is BA or BSC, 17% of old salary ,if qualification is MA or MSC and 20% of old salary, otherwise

    1. Hello Kassahun,
      Please try the following formula:

      =IF(AND(C2="Female", B2="MA"), 25%, IF(OR(B2="BA", B2="BSC"), 15%, IF(OR(B2="MA", B2="MSC"), 17%, 20%)))

      Just copy this formula down the column if necessary.

  13. Hi guys,
    Need help, basically I need to add 15% to a cell value based on the value of another cell. its to do with weight, so for every 1000 "pounds/kilograms", I need to add 15% to the fuel consumption.
    =IF(AND(B16>=1000),B5*15/100,IF(AND(B16>=2000),B5*30/100))
    This is the formula I have, any advise/support/help will be greatly appreciated. happy to share the sheet if that will help

    1. Hi Mark,
      Please try the following formula to solve your task:

      =ROUNDDOWN(B16/1000, 0) * 15/100 * B5+B5

  14. Is there a formula for:

    If Column A contains the letters "REQ" and column B contains the word "Accepted", then column C shows "Status w/ Acceptance"?

    And if Column A does not contain the letters "REQ" and column B does not contain the word "Accepted", then column C shows "Status w/out Acceptance".

    Thanks!

    1. Hi Crystal,
      Here is the formula for you:

      =IF(IFERROR(FIND("REQ", A1, 1)*FIND("Accepted", B1, 1), 0) > 0, "Status w/ Acceptance", "Status w/out Acceptance")

  15. i am looking for a formula where a letter appears in A11, (A to E)
    depending what that letter is determines which cell i want data for.
    -If A11 equals "A", then A41
    -If A11 equals "B", then A42
    -If A11 equals "C", then A43
    -If A11 equals "D", then A44
    -If A11 equals "E", then A45

    whats the best way to do multiple arguments for the data?

    1. Hello Steve,
      It looks like the following formula will do the trick:

      =IF(A11 = "A", A41, IF(A11 = "B", A42, IF(A11 = "C", A43, IF(A11 = "D", A44, A45))))

  16. Hi,
    I am looking for a formula which will autopopulate value of "Primary" if the time start is 8:00 to 17:00 and 'Non-Primary' if the value is set to the other part of the clock, primarily 17:00-24:00 and 0:00 to 8:00.
    Any pointers would be awesome!
    Thanks in advance!
    Christina

    1. Hi Christina,
      Please try to apply the following formula:

      =IF(AND(HOUR(A1) >= 8,HOUR(A1) <= 17), "Primary", "Non-Primary")

      Where A1 is the cell that contains the time.

  17. Test - 1 Marks - 70
    Test - 2 Marks - 65
    Test - 3 Marks - 78
    if a student gets more than 70 in any two tests from the above then he/she will get A otherwise F. Then what will be the formula??
    Thanks in advance.

    1. Hi Nik,

      =IF(COUNTIF(A2:C2, ">70")>=2, "A", "F")

      Where A2:C2 are the test marks.

  18. Can you change it to "you performed well" instead of "you performed good?"

  19. I am trying to find a way to make this all work so that it will give me the new value of bonuses to give sales rep
    if value (A1) is between 100 and 149 add $40 if value (A1) is between 150 AND 199 Add $70 and if value (A1) Is greater than 200+ add $100.

    1. Have you tried this?
      =IFS(A1>100,A1+40,A1>150,A1+70,A1>200,A1+100)

    2. Hi Anna, I have a similar formula question, did you ever find anything out?

  20. Hi,
    I am looking for a formula to satisfy the following condition:
    IF cell A1 = Y, then multiple A12*J12, but if cell A1 = N, then state 0 as a number

    I would appreciate your help.
    Thank you,
    Julia

    1. =IF(A1="Y",A12*J12,IF(A1="N",0))

  21. I want to write a simple Conditional format rule -

    If column H contains either texts - VTA or ADI or SBI , value in column P should be a difference of (Value in Column O - Value in Column G)

    Please help or guide me where to look

    1. Hi Abhay,

      Here goes the formula for P2:
      =IF(OR(H2="vta", H2="adi", H2="sbi"), O2-G2, "")

      Just copy it down to as many cells needed.

  22. I am trying to find a way to make this all work so that it will give me the new value.

    if value is between 0 and 3999 multiply by 3% if value is between 4000 and 5999 then multiply by 10% if value is grater than 6000 multiply by 14%

    1. =IF(AND(A1>=0,A1=4000,A15999,A1*0.14,"")))
      Swap A1 for the cell which contains the value you wish to multiple.

      1. "=IF(AND(A1>=0,A1=4000,A15999,A1*0.14,"")))"
        The first formula didn't post correctly

  23. I need help writing a formula that will look at the text value in one column and then the numeric value in another column and tell me if it met target or not. Here's what logic I want...

    If text in A2 is "Tier 1" and the value in F2 is <=3 then I want it to return "Target Met" and if not then "Target Not Met"
    Or
    If text in A2 is "Tier 2" and the value in F2 is <=7 then I want it to return "Target Met", and if not then "Target Not Met"

    How can I do this? Thanks!

    1. =IF(OR(AND(B4="TIER1",C4<=3),AND(B4="TIER2",C4<=7)),"TARGET MET","TARGET NOT MET")

  24. I need help to create a formula to cover below logic
    if cell H8 contains ..1, W8/W7, if cell H8 contains ..2, W8/W6, if cell H8 contains ..3, W8/W5, if cell H8 contains ..4, W8/W4,if cell H8 contains ..5, W8/W3, if cell H8 contains ..6, W8/W2, otherwise W8/W1
    Apparently, there is a problem with the formula below
    =if((H8="*..1"),W8/W7,if(H8="*..2"),W8/W6,if(H8="*..3"),W8/W5),if(H8="*..4"),W8/W4,if(H8="*..5"),W8/W3),if(H8="*..6"),W8/W2,if(H8="*..7"),W8/W1,W8/W8))
    Pls help.
    Thank you

    1. Try this
      =IF(IFERROR(FIND("1",H8),0)>0,W8/W7,IF(IFERROR(FIND("2",H8),0)>0,W8/W6,IF(IFERROR(FIND("3",H8),0)>0,W8/W5,IF(IFERROR(FIND("4",H8),0)>0,W8/W4,IF(IFERROR(FIND("5",H8),0)>0,W8/W3,IF(IFERROR(FIND("6",H8),0)>0,W8/W2,IF(IFERROR(FIND("7",H8),0)>0,W8/W1,W8/W8)))))))

      I have tried it, works correctly.
      Thanks

  25. Hey I am trying to make a condition of if value yes then it will multiply first set of logical conditions but if value is "no" then it will multiply by other set of values
    =IF(OR(D1="yes",D1="no"),IF(A1=10,A1=100, 1*S1))),IF(A1=10,A1=100, A1*1))))
    It does calculate the first set of conditions correctly but ignores the "or" condition
    Please Help! Thanks!

    1. for whatever reason the message is not full
      =IF(OR(D1="yes",D1="no"),IF(A1=10,A1=100, 1*S1))),IF(A1=10,A1=100, A1*'S1))))

      1. Basically the condition set is that
        for whatever reason the message is not full
        IF(OR(D1="yes",D1="no"),IF(A1=10,A1=100, 1*S1)))
        ,IF(A1=10,A1=100, A1*'S1))))

  26. I have a 3 data entry fields. I want a warning to print if anyone enters in more than 1 of the data fields. When I had 2 fields, it worked. This is the formula I have written for the 3 fields. However, it only prints the warning if there are entries in the first 2 fields. If there is an entry in either of the first 2 fields and the third field, the warning isn't printing.
    =if(and(a2>0, a3>0, a4>0),"Choose one", "")
    Help! Thanks!

    1. Enter this formula in cell B2 =A2>0
      Enter this formula in cell B3 =A3>0
      Enter this formula in cell B4 =A4>0
      Then use this formula
      =IF(COUNTIF(B2:B4,"TRUE")>1,"Choose one","")

  27. Hi
    I need help For this Formula.
    Total Point.
    How to used (I used it before long time more than seven years)
    Total point Score
    =if(H16="A",4*G$14,(if(H16="AB",3.5G$14,(if(H16="B",3*G$14,(if(H16="BC",2.5*G$14,(if(H16="C",2*G$14,(if(H16="CD",1.5*G$14,(if(H16="D",1*G14,(if(H16="E",0.5*G$14,0)))))))))))))))

    1. =IF(H16="A",4*G$14,(IF(H16="AB",3.5*G$14,(IF(H16="B",3*G$14,(IF(H16="BC",2.5*G$14,(IF(H16="C",2*G$14,(IF(H16="CD",1.5*G$14,(IF(H16="D",1*G14,(IF(H16="E",0.5*G$14,0)))

  28. Hi,

    Could you help with a formula like below?
    • If Andi 1-20, multiple number by 4
    • If Andi 21-40, multiple number by 8
    • If Andi 41-60, multiple number by 10
    • If Andi 61-80, multiple number by 12
    Thank you!

    1. =C6*IFERROR(VLOOKUP($C$6,$A$6:$B$85,2,),"")

  29. I am interested in using the IF/AND function with multiple arguments on text, but can't seem to get this to work.

    Column H Column I Column J
    Classification Level Approved
    EX 1 Yes
    PM 5 Yes
    AS 2 No
    EC 6 Yes
    MD-MOF 3 Yes

    IF column H = EX and Column I = 1,2 or 3 then Column J = Yes
    If column H = PM and Column I = 5 or 6 then Column J = Yes
    If column H = EC and Column I = 6 or 7 then column J = Yes
    If column H = MD-MOF and Column I = 3 then column J = Yes
    Anything else in column H or I then column J = No

    I'm stumped... any help is appreciated.

    1. First make a table array that have EX, PM, EC, 1, 2, 3, etc...
      A B C D E F
      1 EX1 Yes =D1&E1 Input Input =IFERROR(VLOOKUP(C1,$A$1:$B$8,2,FALSE),"No")
      2 EX2 Yes (EX,PM etc,)(1,2,6 etc.)
      3 PM5 Yes
      Etc....

    2. First make a table array that have EX, PM, EC, 1, 2, 3, etc...
      A1 B1
      EX1 Yes
      EX2 Yes
      =IFERROR(VLOOKUP(G19,$E$18:$F$25,2,FALSE),"No")

  30. Hi, is there any formula that I can use with this data?
    Ana Rose Jen Least Value
    20 10 40
    I want to get the least value among the three but the return value in column 4 should be the name.
    Thanks

    1. Hi Rose,

      Assuming the names are in A1:C1 and the numbers in A2:C2, the following formula will work a treat:
      =INDEX($A$1:$C$1,MATCH(MIN(A2:C2),A2:C2,0))

  31. Need some help, have been trying to figure this out for awhile. How would a formula look:
    If a cell has 1 it would be $7 if more than 1 $6?

    need some help

    1. =if(A1=1,7,6)

  32. A1, C1, E1 and G1 >=1 IF TRUE RESULT "PASS" IF FALSE RESULT "FAIL"
    B1, D1, F1 and H1>=1 IF TRUE RESULT "PASS" IF FALSE RESULT "FAIL"
    EACH CELL MUST >=1 SO PASS, IF ONE OF THEM 0 RESULT "FAIL"
    Please help me. How can I solve the problem?

    1. =IF(SUBTOTAL(6,A1:H1)=0,"FAIL","PASS")

  33. Kindly help with suitable formula for C=A-B but should not exceed 15000

    1. Hi Praveen,

      And if A-B exceeds 15000, what is a C cell to display?

  34. I want to write a formula:

    If column B2 is higher than 10000 and lower than 20000, put 100 in column c2

    1. Hi Laya,

      Here goes the formula for C2:

      =IF(AND(B2>10000, B2<20000), 100, "")

  35. HOW DO I AUTOMATICALLY COLOR A CELL BASED ON THE FIRST 2 LETTERS OF A TEXT?

    1. Use conditional formatting and format rule if text start with

  36. Hi, I am trying to write a formula for the following:

    Cell A1 is a Number ranging between 0 & 400. I want Cell A3 to display the following:
    If Cell A1 is between 0 - 99 = "Text 1"
    If Cell A1 is between 100 - 249 = "Text 2"
    If Cell A1 is 250+ = "Text 3"

    I can make it work for Text 1 & Text 2 but adding a third, breaks the formula...

    If anyone can help me with this, that would be great.

    Thanks,

    Ash

    1. IF(AND(A1>=0,A1=100,A1=250,"TEXT 3","")))

  37. Hi kindly help me to do this.
    I have a value in Cell A1. I needs to do that When I put value as "Yes" in cell B1 then the cell C1 pick the value of cell A1 automatically. And if I put value as "No" in cell B1 then the cell C1 pick 0 as a value automatically.

    1. In cell C1 enter the following:
      If(b1="Yes",a1,if(b1="No",0))

      1. Thanx alot....

  38. Hi Friends,
    Could you help me with the below example.
    if we have 1000 in one column which is splitted in many values in another column with 20000, how to total only 10000.
    In the below example you may see, for each day, we have same amount in two columns but these amount are splitted in third column, how can we match these. Any suggestions.
    Example:
    Monday 1000 2000 500
    Monday 1000 2000 500
    Monday 1000 2000 500
    Monday 1000 2000 500
    Monday 1000 2000 500
    Monday 1000 2000 250
    Monday 1000 2000 250
    Tuesday 2900 1100 750
    Tuesday 2900 1100 750
    Tuesday 2900 1100 600
    Tuesday 2900 1100 500
    Tuesday 2900 1100 500
    Tuesday 2900 1100 450
    Tuesday 2900 1100 450
    Wednesday 1500 2500 750
    Wednesday 1500 2500 750
    Wednesday 1500 2500 1000
    Wednesday 1500 2500 1000
    Wednesday 1500 2500 250
    Wednesday 1500 2500 250
    Thurday 2250 2750 1000
    Thurday 2250 2750 750
    Thurday 2250 2750 250
    Thurday 2250 2750 2000
    Thurday 2250 2750 1000
    Friday 8450 1550 450
    Friday 8450 1550 550
    Friday 8450 1550 2000
    Friday 8450 1550 3000
    Friday 8450 1550 1000
    Friday 8450 1550 1000
    Friday 8450 1550 2000
    Please help.

    1. use sumif formula for that.

  39. Hi,
    I am trying to convert to date format from column A "20190520" to column B "05/20/2019"
    please advise. Thanks in advance.

    1. highlight column, click "text to columns" in the data tab. Then bubble in delimited, click "next" twice, bubble in Date and use "YMD" in the dropdown and click finish.

  40. Hi Everybody,
    I have a problem in if function with following conditions:
    1. I have 4 words in C3, C4, C5 & C6 and each word has condtion 1 and otherwise condition 2 if not returns false
    2. I want to add both conditions againt each word in a single formula
    I.e. if(C3="a", condition 1, condition 2, if(C3="b", condition 1, condition 2, if(C3="c", condition 1, condition 2, if(C3="d", condition 1, condition 2))))

    But it does not help me out.

    Requested to please help me in this regards

    Waiting

    1. if(C3="a", condition 1, if(C3="b", condition 1, if(C3="c", condition 1, if(C3="d", condition 1, condition 2))))

  41. Hi! I'd like to check 3 different conditions at once in excel, each consisting of 2 checking cells.
    e.g. If U>=0 and AE>0 then AE, or if U0 then +X+AE, or if U>0 and AE<=O then 0.
    Any clue how to do it? Thanks in advance!

    1. Hi! I'd like to check 3 different conditions at once in excel, each consisting of 2 checking cells.
      e.g. If U>=0 and AE>0 then AE, or if U0 then +X+AE, or if U>0 and AE<=O then 0.
      Any clue how to do it? Thanks in advance!

  42. I really need help creating a NESTED IF/AND STATEMENT. I feel like I'm close but it's not working yet. All help is greatly appreciated. I need a statement that meets all the following criteria:
    IF C9 is >=2500 AND C10 is >10, 300,000
    IF C9 is >=2500 AND C10 is >5, 200,000
    IF c9 is >=2500 AND C10 is >2.5, 100,000
    OTHERWISE, if C10 =2500,C10>10),300000,IF(AND(C9>=2500,C10>5),200000,IF(AND(C9>=2500,C10>2.5),100000,0)))

    1. oops, looks like I messed up that last line. Should be:
      I need a statement that meets all the following criteria:
      IF C9 is >=2500 AND C10 is >10, 300,000
      IF C9 is >=2500 AND C10 is >5, 200,000
      IF c9 is >=2500 AND C10 is >2.5, 100,000
      OTHERWISE, if C10 =2500,C10>10),300000,IF(AND(C9>=2500,C10>5),200000,IF(AND(C9>=2500,C10>2.5),100000,0)))

      1. So sorry, when I paste my formula it keeps messing up my other stuff. Here is my formula that doesn't work:
        =IF(AND(C9>=2500,C10>10),300000,IF(AND(C9>=2500,C10>5),200000,IF(AND(C9>=2500,C10>2.5),100000,0)))

  43. Good day
    I am battling to write this formula:
    info available as follows
    I3 req del date
    J3 actual del date
    current formula:
    =IF(J3=I3,"on time",IF(J3I3,"late")))
    and it is working all over the spreadsheet.
    now I want to add : if J3 has no date, what do I have to do to the formula to have the status column be "none"

    1. =IF(J3=I3,"on time",IF(J3I3,"late")))

  44. If a=2, if b=6, if c=8 then formula to calculate average of a,b,c

    1. =SUM(D15+E15+F15)/3

      would this do it?

  45. I have a spreadsheet that contains money spent on different vehicles in the company. I would like a formula that would add costs in one column if its a particular Vehicle listed in another column. I would like a grand total of what is spend for each vehicle.

    Can you please help?

    1. Use pivot table

  46. I need help setting up an IF formula.
    I am trying to track my blood pressure, and I want it to display whether the readings are "High", "Elevated", or "Normal". I am recording each number in a separate cell, so I have the Systolic readings in the "B" column, Dystolic in the "C" column, and "Pulse" in "D" column. I want the "E" column to throw back "High", "Elevated", or "Normal" if both "B" and "C" columns meet certain criteria.

    1. That should be similar to what the example above is using.

      =IF(SUM(C2:F2)>=120, "Good", IF(SUM(C2:F2)>=90, "Satisfactory", "Poor "))

      So depending on the values you are calculating, your forumla may look something like this:
      =IF(SUM(B1:C1)<=120,"Normal",IF(SUM(B1:C1)<=150,"Elevated",IF(SUM(B1+C1)<=180,"High")))

      OF course, substitute the example numbers I used with the actual numbers you are using.

  47. Hi, I am trying to find the right IF function but can't quite get it.
    If A1 is greater than or equal to 9, and B1 is zero, then A1 minus 9, else sum of A1+B1. But no matter what I try, it doesn't work. the result is always either deducted or added by 9. Only the value in A1 should be deducted by 9 if its =/> 9. Value in B1 stays as is. Can you please help? thank you.

    1. =IF(AND(A1>=9;B1=0);A1-9;A1+B1)

  48. =IF(B23=24&B23=30&B23=36&B23=48&B23=60,5))))))

    is above formula is correct iam getting false as result. Please check and suggest

    1. B23 can only equal one value. You are saying IF it equals 24, AND 30, AND 36, AND 48, AND 60. That is obviously false since none of those values equal each other. Also, why do you have so many closed parentheses at the end?

  49. HELP PLEASE! I have a column recording tshirt sizes and i want to minus one from 100 anytime an XL tshirt size is entered in that tshirt size column. So far i have =IF(D1="XL",E544-1). The number 100 is stored in cell E544 hence using E544-1. Note well, it works for only D1 cell. I want it to work for the entire D column. PLEASE HELP.

    1. =IF(D1="XL",$E$544-1)
      If you are copying the formula, the reference cell will change (eg, E545-1, E546-1, E547-1 and so on. Formula is automatically updated on the assumption that you will want to source data from the next row - so in this case, if you entered 100 in E545 & E546 you will notice that D2 and D3 cells are now calculating correctly). The dollar signs will lock that cell in the formula so it can't change.
      Enter your formula as:
      IF(D1="XL",$E$544-1)

  50. Hi,

    I have a combination of different type of employee and I want to write the position name in a column. There is 3 types of position : scientist, Head and Analyst. I have too much analysts and it change too often to enter all the names in the formula. Is there a way with my formula to say that if the name does not corresponds to one mentioned, instead of writting ''false'' to write ''Analyst''? Here is my formula with letters instead of names :
    =IF(OR([@Nom]="A",[@Nom]="B",[@Nom]="C",[@Nom]="D",[@Nom]="E",[@Nom]="F",[@Nom]="G",[@Nom]="H",[@Nom]="I",[@Nom]="J", @Nom]="K"),"Scientist",IF(OR([@Nom]="L"),"Head"))

    I tried addind ... ,''Analyst'')) at the end of the formula but excel does not take it.

    Thanks,

    1. Hi Vanessa, use vlookup function.

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