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 20. Total comments: 2534

  1. Privet Svetlana, ty voobshe genie!

    I need the formula for S9 to give me either "Dog", "Top Seller", "Workhorse" or "Challenge".

    For eg.:
    If P9 = "Low" and R9 = "Low" I need "Dog" in S9
    If P9 = "High" and R9 = "High" I need "Top Seller" in S9
    If P9 = "Low" and R9 = "High" I need "Workhorse" in S9
    If P9 = "High" and R9 = "Low" I need "Challenge" in S9

    I hope you can help on that..

    1. Hi Ivan,

      Don't think I am, but thanks anyway:)

      This can be done by using nested IF's with embedded AND statements, like this:

      =IF(AND(P9="low", R9="low"), "dog", IF(AND(P9="high", R9="high"), "top seller", IF(AND(P9="low", R9="high"), "Workhorse", IF(AND(P9="high", R9="low"), "Challenge", ""))))

      Please note, if you use Russian of European localization of Office, you may need to replace commas with semicolons in the formula (depending on which List Separator is set in your Regional Settings).

      1. Thanks so much! Problem solved

        Удачи

  2. Hello,

    Beginner here..I am trying to get a range of cells containing text and display only the most severe result.

    My formula that works for only 2 results:

    =IF(COUNTIF(F5:F10,"Not OK")>0, "Not OK", "OK")

    what i am trying to do is add a "Ok But" text result.

    So if there is at least one "Not Ok" with multiple "Ok Buts" the result should be "Not Ok"
    If i have at least one "Ok But", no "Not Ok", i need it to show "Ok But"
    If all are Ok it should result "Ok"

    Thanks

    1. Nevermind,

      =IF(COUNTIF(F5:F10, "not ok")>0,"Not Ok",IF(COUNTIF(F5:F10, "Ok But")>0,"Ok But","Ok"))

  3. How can I make a formula to test two different conditions?
    For the first condition there are 8 different outcomes.
    For the second condition there are 4 different outcomes, however for this one the outcomes are in range of numbers.
    ex: 0-1000 = outcome A or 1001-1000=result B

    Suppose I get result X for first condition
    and for second I get result B however this result needs to be defined by a range of numbers that is directly related to result X.

    Ex. Ranges:
    00-10 = A
    11-20 = B
    21-30 = C
    31-40 = D

    and

    there's another variable that changes each level.
    variable x
    so the above ranges are true if X = 1

    however if x=2 then
    ranges change for A-D
    How can I write a formula to that will tell me the range letter (A-D) if the range changes depending on variable x?

    1. I still don't know who to do if for a range
      can you please explain
      Like if 300-400 then 1
      if 200-299 then 2
      if 100- 199 then 3
      if < 100 then 4
      How can I do that, thanks

    2. Update.
      Russes answer in the comments gave me what I needed.

      I was able to nest 32 if statements!
      Does anyone know how to define a function in Excel?

      Ex. I want:

      Weekly = 52
      Biweekly=26
      Monthly=12
      Everything else (literally)=1
      Is this possible in excel?

      1. I mean variable not function.

  4. hi i need to get this: if between -10 and 5= 5 if between -15 and 10= 4, if between -25 and 15=3, if between -35 and 25=2 and if btween -45 and 35= 1 im having trouble i can use IF and AND but i just get 5 and 4.
    please help
    these are the formulas i've use
    =IF(AND(H6>=-10,H6<=5),"5","4")

    =IF(H57=6,"4",IF(H57>15,"3",IF(H57=25,"2","0")))))

    1. Hi Mike,

      The point is that your conditions are not mutually exclusive, and lots of numbers fell into all of the categories. For example, number 1 is between -10 and 5, but it is also between -35 and 25, and so on.

      In nested functions, Excel checks the conditions in the order they appear in a formula, and if a condition is met, subsequent conditions are not checked. So, I'd advise you either reconsider the logic, or input the conditions in the order of importance.

  5. Hi trying to build the following formula:

    If Cell G3 = 1 and Cell I3 = 36 Return "-2"
    If Cell G3 = 1 and Cell I3 = 37 Return "-3"
    If Cell G3 = 1 and Cell I3 = 38 Return "-4"

    The formula I have written is:

    =IF(AND(G3="1"(I3>37,"-4",IF(I3=37,"-3",IF(I3=36,"-2","0")))))

    This doesn't appear to work. Could you help?

    1. Hi Russ,

      Try the following syntax:

      =IF(AND(G3=1,I3>37),-4, IF(AND(G3=1,I3=37),-3, IF(AND(G3=1,I3=36),-2,0)))

      Please note that in Excel formulas, you should not enclose numbers in double quotes unless you want them to be treated as text values.

  6. Hi, I'm trying to build a formula with 3 logic tests as follows:

    If cell E4 is blank then = blank
    If cell G4 plus 29 days is < today’s date then = Compliant
    If cell G4 plus 28 days is < today’s date then if true = Review required if not Overdue

    Below is what I have in Excel
    =IF(ISBLANK(E4),"", IF((G17+29)<TODAY(),"Compliant",IF((G4+28)<TODAY(),"Overdue","Review required"))

    Everything appears to work except for IF((G17+29)<TODAY(),"Compliant"

    What is wrong with my formula?

    1. Hi Claire,

      That's probably because in your formula it's IF((G17+29)<TODAY() while the condition reads "cell G4 plus 29 days". Please check this.

  7. I am trying to create a formula to reference a certain cell's text. What I want it to do is input a value based on what text is in a certain cell. For example, I want it to refer to cell E6 and if it is biannual, I want it to input 2. If it's annual, I want it to input 1, if it's monthly, I want it to input 12, and if it's quarterly, I want it to input 4. Can it do something like that?

    1. Yes. That should be very simple to create. Multiple (If) statements will get that done.

  8. I need an IF formula for:
    IF P20 equals 83-100, Highly Effective
    IF P20 equals 66 – 82, Effective
    IF P20 equals 53 – 65, Needs Improvement
    IF P20 equals 0 - 52, Unsatisfactory

    1. Hi Jackie,

      You need a nested IF formula like this:

      =IF(P20>=83, "Highly Effective", IF(P20>=66, "Effective", IF(P20>=53, "Needs Improvement", "Unsatisfactory")))

  9. Looking for assistance with the following:

    If the date in E2 is prior to today AND L2 is less then 0 then return CHECK if false then return OK

    1. Hi Stacey,

      Here you are:
      =IF(AND(E2<TODAY(), L2<0), "CHECK", "OK")

  10. IF A1= "any value" then A1= sum of B1,B2 or "zero"

    Please advise. Thanks.

    1. Hi Riyaz,

      Here you go:

      =IF(A1<>"", B1+B2, 0)

      1. Thank you so much Svetlana Cheusheva

  11. A1 = RECEIVED / CANCELLED / DECLINED (Dropdown)

    If A1="CANCELLED" then A2, A3, A4... will show CANCELLED
    If A1="DECLINED" then A2, A3, A4... will show DECLINED

    but

    If A1="RECEIVED" then A2, A3, A4... must be blank

    Please advise. Thank you

    1. HI BRENDA,
      TRY THIS
      =IF(A1="CANCELLED","CANCELLED","")&IF(A1="DECLINED","DECLINED","")&IF(A1="RECEIVED","","")

  12. Hi Svetlana,

    I am having a bit trouble with the IF function with ISNUMBER and FIND/SEARCH to match and substitute strings. However, I am not getting the desired outcome. Let say, Cell 1 has string "Kara", Cell 2 has "Karam".This is my formula =IF((ISNUMBER(FIND(A1,A2))),SUBSTITUTE(A1,A1,A2),A2). Now, it is substituting Cell 1 string with Cell 2. However, I want it to substitute on the basis of full name. For instance, if Cell 2 had "Kara & Jones", then it should substitute, not only if the same characters are found. Any comments or directions would be highly appreciated!thx

    1. Guys,

      If you have any suggestions regarding my problem, I would be grateful!

  13. Using Google Sheets.

    I have a formula that is using validated data to display a value in a field:

    =IF(H3="0X0", "$0.00", IF(H3="12 x 2", "$120.00", IF(H3="15 x 3", "$220.00", IF(H3="19 x 4", "$380.00", IF(H3="10 x 7", "$350.00")))))

    This works perfectly, however, I cannot get total of the values returned. I am using the following formula: =Sum(I3,G3,E3,C3)

    1. Don't worry I figured it out: remove the '$' from the formula and use =Sum(I3+G3+E3+C3)

  14. HOW TO DO THIS QUESTION

    CALCULATE THE CHARGE VALUE BY USING IF FUNCTION
    Name units charge
    raja 250 condition
    kumar 150 0 to 200 - 1UNIT - Rs 6
    sasi 800 200 to 500 - 1UNIT - Rs 9
    mala 469 >500 - 1UNIT - Rs 12
    raciga 640
    uthayan 68
    susila 342

    1. CALCULATE THE CHARGE VALUE BY USING IF FUNCTION
      Name units CHARGE
      raja 250
      kumar 150
      sasi 800
      mala 469
      raciga 640
      uthay 68
      susila 342

  15. I need help with a formula for conditional formatting.

    IF cell D12 is blank and the date in cell C12 is within 14 days of today's date, then the text is formatted red.

    1. Hi Carrie,

      Does "within 14 days of today's date" mean 14 days in the past or in the future?

      1. If the target date in C12 is less than 14 days from today.

        So, target date is 11/15/15 it would be highlighted because it is due within 14 days.

        1. Hi Carrie,

          Select the cells (or rows) you want to format red beginning with row 12 and create a conditional formatting rule will the following formulas:

          =AND($D12="", $C12>TODAY(), $C12-TODAY()<14)

          1. Svetlana, it worked perfectly! Thank you for your assitance!

  16. Hello,
    I am needing to integrate a formula into excel to calculate a tiered pricing schedule. The formula needs to assess if the cell value falls into one of 36 different ranges and than multiplies the cell value by a multiplier specific to that range. For example if the cell falls into the range of >5 and 15 and <=30 multiply by 9.33 and so on.

    Schedule Example

    Range Markup
    1 5.01 to 15.00 9.37
    2 15.01 to 30.00 9.33
    3 30.01 to 55.00 9.30

    ............ ...

    34 9505.01 to 9999.99 4.30

    1. Example Correction: For example if the cell falls into the range of > 15 and <=30 multiply by 9.33 and so on.

  17. for some reason the formulas post here show different what the one i write
    =IF(A1>=37,10000,IF(A1>=28,1000),IF(A1>=19,100),IF(A1>=10,10),IF(A1>=1,1))

    1. Hi!

      You were almost there. You just have to close all IF's at the end of the formula, like this:

      =IF(A1>=37,10000,IF(A1>=28,1000,IF(A1>=19,100,IF(A1>=10,10,IF(A1>=1,1)))))

  18. Hello and please help.
    Take Row A through L
    If A1 equals K1 then L1 needs to move to B1
    How to formulate this
    =If(A1=K1;L1=B1) ?????

    1. Hi Luke,

      Here's the formula for B1:

      =If(A1=K1, L1, "")

      The formula will copy a value of L1 to B1 if A1=K1.

  19. Sorry something went wrong ... below the Table again ...

    I need a formula (If I guess) create a new Score from the NOT PASS (less than 50) an individually new PASS Score in another cell. I tried many things but failed ... I think it should be like ... The difference from PASS Score to NOT PASS Score plus some individually value added from the difference.

    Ehm is ... Thank you all for help and understanding my bad English. I can explain it in German, but this is an english writing/reading forum. Smile

    U1 U2 Part Read completed Write Point Score
    79 87 10 10 10 10 10 100 50
    78 99 6 7 8 6 5 57 55
    78 95 5 5 8 6 5 50 58
    76 77 5 3 8 8 5 12 16
    79 89 6 3 8 4 8 23 35
    62 19 6 2 5 5 5 19 26
    71 14 5 4 8 L 5 53 45
    73 49 4 3 8 6 7 32 31
    34 9 5 5 8 5 5 27 23
    77 87 5 7 8 5 5 38 29
    71 17 5 2 5 4 8 9 18

    1. Sorry ... I cant figure it out ... what happen to the table in my post. I formated in Courier ...

  20. Hi Shiraz,

    Do you mean if A1 is equal to or greater than 40, the profit will be 4% or $20 whichever is higher?

    Anyway, you can use a formula similar to this:

    =IF(A1>=40, MAX(A1*0.04, 20), MAX(A1*0.04, 15))

    1. ill repeat again..if A1 is 20 , the profit will be 4% of $15 which ever is higher, and if A1 is 40 the profit is 4% or $20 which ever is higher....these are 2 different condition have to apply on cell..hope you understood.

      1. just read A1 20 as a & A1 40 as b,

        if A1 is (a) the profit will b 4% or $15 and if A1 is (b) the profit will b 4% of $20...

        1. Shiraz,

          I understand your conditions. But what if A1=1, or A1=21 or A1=50. How is the profit calculated? Or can A1 contain only 2 values (20 and 40)?

          1. we have only two products 20 & 40 or you can read as apple & grapes...
            for apple let us assume the amount $400 & grapes $600 , now i have to apply a formula to single cel " if A1 is apple then the profit should be 4% or $15 which ever is higher and if A1 is grapes then profit should b 4% or $20 which ever is higher " i hope you got it.

            1. Assuming that A1 is either "apple" or "grapes" and B1 is amount ($), the formula is as follows:

              =IF(A1="apple",MAX(B1*0.04,15),IF(A1="grapes",MAX(B1*0.04,20), ""))

              1. thanks :)

  21. I need a formula where I say if A2 (which is a persons name) is equal to a name in column B then bring me the information that it shows under this persons name in column D. How may I do this formula?

    1. Hi Lysbeth,

      I think you'd better use the VLOOKUP function for this task. A formula can be similar to this:

      =VLOOKUP(A2,B2:D100, 3, FALSE)

  22. hi svetlana, i'm having a problem figuring out how to put this in formula

    G8 is equal to (46 to 35) is "NI"
    G8 is equal to (34 to 23) is "L"
    G8 is equal to (22 to 11) is "M"
    G8 is equal to (10 to 0) is "H"

  23. Hello.

    I need to formula to evaluate a string, for example:

    AB12345
    CD34567
    EF5678
    G6789

    I have 4 models ( AB CD EF G), and each model has 2 types, Summer and Winter.
    Numbers between 1000 1999, 4000 4999 and 6000 6999, classified as Summer. Numbers between 3000 to 3999, 5000 to 5999 and 9000 to 9999 classified as Winter.
    I'd like a formula to classify them fast, instead of analising them manually or using procvs, because i have a lot of them, and it gets very slow when i use filters, due to procv formulas.

    Like this:

    AB12345 "Summer AB"
    CD34567 "Winter CD"
    EF5678 "Winter EF"
    G6789 "Summer G"

    Maintain the letter/letters ( it's 1 letter or 2, depends ), and classify the numbers.

    It it possible ?

    Can someone help me or point me in the right direction please ?

    Thank you !

    1. Unable to help me ?

  24. Hi. I am making a simple formula for tests results. I have already a list for the percentage of the raw scores. All I wanna do is when I input the raw score (1st column) its equivalent percentage will automatically show in the next column. :)

  25. Hi,
    I am having trouble combining the following rules:
    1. If A:A ="Amy" AND B:B contains "Show", "Show"
    2. If A:A ="Amy" AND B:B contains "Dress", "Dress"
    3. If A:A does not equal "Amy", "other"

    Thanks

    1. Hi rue,

      If you want to return a value from column B if column A contains "Amy", "other" otherwise, use the following formula:
      =IF(A1="Amy", B1, "other")

      If you need a formula for 2 conditions, "amy" in A and "show" / "dress" in B, use the following one:

      =IF(AND(A1="amy",OR(B1="show", B1="dress")), B1,"other")

  26. Hi,

    I need help with a formula.

    I want to return the following values;

    If Cell Value is less than 40 = 10
    If Cell Value is between 40 & 49 = 6
    If Cell Value is between 50 & 59 = 4
    If Cell Value is between 60 & 64 = 2
    If Cell Value is greater than or equal to 65 = 1

    I've tried using the formula below but it only seems to return either the numbers 10 or 6.

    =IF(C2=40,"6",IF(C2>=50,"4",IF(C2>=60,"2",IF(C2>=65,"1")))))

    1. I have the same problem, how did you fix it?

    2. I figured out why the formula wasn't working, i've managed to sort it out.
      Thanks

  27. Hi Svetlana,

    Could you please help me to figure out the formula, which will find identical cells with identical ID, Instrument, Amount, but different Type and will return "hedge"

    ID Instrument Type Amount
    1212 XAUUSD Buy 30
    1234 CL Buy 1000
    1255 XAUUSD Buy 40
    1255 APPLE Buy 150
    1255 XAUUSD Buy 20
    1255 XAUUSD Sell 20

    Thank you ahead!

    1. apologies for the format appeared above.
      However, for the case above the last two rows should appear as "hedge"

  28. I guess non costumers are not welcome...

    1. customers*

  29. I have 3 columns, Lets say A1, B1, and C1. I need to make a concatenate function that:

    If A1="*" and B1=(random text) then C1 = B1
    If B1="" and A1=(random text) then C1 = A1
    If A1"*" and B1"" then =CONCATENATE(A1,".",B1)

    1. Hi Austin,

      Does A1"*" mean any text in A1? And most likely B1"" is supposed to be <>""(non-empty), right?

      Anyway, you can use a nested IF formula similar to the below one, just adjust the criteria according to your needs:

      =IF(AND(ISTEXT(A1), B1="text"), B1, IF(AND(A1="text", B1=""), A1, IF(AND(ISTEXT(A1), B1<>""), CONCATENATE(A1,".",B1), "")))

  30. I was wondering if you could provide assistance with a formula I am trying to figure out.
    I need to look at a range of cells and determine if any of them have been the text "Ready" in them. IF they have the word Ready in them, then I need a different field to have a date in it. Is there a formula that can assist with this?

    1. Hi Jayne,

      You can use a formula similar to this:

      =IF(A1="Ready", TOADY())

      The formula will insert the current date if A1 contains "Ready". And you can replace TODAY() with any other date that you need.

  31. Hello,
    Which formula can I use if I want to return a cell value using a If clause. I am looking for something like " IF A1=OT Print value from B1.

    1. Hello Vinay,

      Here you go: =IF(A1="OT", B1, "")

  32. I need column A to reflect what is in column B. If B1=816114 I need A to say "SMC Main". If B=816164 I need A to say "CBRE". If B=811739 I need A to say "Ballard". If B says ANY other number, I need A to be blank (NOT say false). I am having trouble with that last part.

    1. Hi Brit,

      Try the following nested IF's:

      =IF(B1=816114, "SMC Main", IF(B1=816164, "CBRE", IF(B1=811739, "Ballard", "")))

      1. Thank you so much! worked great!

  33. Hi Svetlana,

    I am trying to obtain the most recent date from four different cells and want the data to return first if the most recent date is in cell AM4, second if the most recent date is in cell BH4, third if the most recent date is in cell CC4 and Fourth if the most recent date is in cell CX4. I am currently using the below formula which doesn't work if the most recent date is in cell CX4 and should return Fourth. Can you assist please?

    =IF(AND(AM4>BH4,AM4>CC4),"FIRST",IF(AND(BH4>AM4,BH4>CC4),"SECOND",IF(AND(CC4>AM4,CC4>BH4),"THIRD",IF(AND(CX4>AM4,CX4>BH4>CC4),"FOURTH",""))))

    1. I have now done this. Thanks

      =IF(AND(AM3>BH3,AM3>CC3,AM3>CX3),"FIRST",IF(AND(BH3>AM3,BH3>CC3,BH3>CX3),"SECOND",IF(AND(CC3>AM3,CC3>BH3,CC3>CX3),"THIRD",IF(AND(CX3>AM3,CX3>BH3,CX3>CC3),"FOURTH",""))))

  34. Hi,
    Below condition is not working
    IF(OR(A1=C1,B1*D1),IF(A1=C2,B2*D2),IF(A1=C3,B3*D3))

    Column A Column B Column C Column D
    B81234 16 B91456 $8,995.00
    B81345 19 B81234 $4,887.50
    B91456 27 B81345 $5,391.00

    can anyone help me
    Manish

    1. Hi Manish,

      I believe the correct syntax is as follows:

      =IF(A1=C1, B1*D1, IF(A1=C2, B2*D2, IF(A1=C3, B3*D3, "")))

  35. how will i do the "if" function for the ledger account, for example CASH

    CASH
    Debit Credit

    8000 5000
    -----------------------------
    3000 Balance

    and what if the "credit" balance exceeds the "debit" balance
    how will i use the "if" function to make the credit balance be written under the credit column.

    Thanks

    1. Hi Josselle,

      Let me check if I understand the task correctly.

      If Debit (A2) is greater than or equal to Credit (B2), we calculate the difference (Debit-Credit). If Credit is greater than Debit, we return the Credit number.

      If so, you can use the following formula:
      =IF(A2>=B2, A2-B2, B2)

      If you are looking for something different, please clarify.

  36. APOLOGIES ... THIS IS THE FULL FORMULA I HAVE:
    =IF(F2>=11,"Late",IF(F2>=-10,"On Time",IF(F2<=-11,"Early")))
    I want to include in the middle part/argument, that if C2 (which is a particular date) to say "CR ON TIME"

    1. Hi Robin,

      If my understanding is correct, you can add one more IF to the formula, like this:

      =IF(C2<>"", "CR ON TIME", IF(F2>=11, "Late", IF(F2>=-10, "On Time", IF(F2<=-11, "Early"))))

  37. Hi Iira,

    You can add another IF function to the formula, like this:
    =IF((F4+F5+F6+F7)='Fee summary'!G5,'Fee summary'!G5, IF((F4+F5+F6+F7)<'Fee summary'!G5, "ERROR > "&'Fee summary'!G5, "")) Please note, the formula returns an empty string if your sum is greater than in 'Fee summary'!G5.

    1. Thank you so much Svetlana for your advice.
      I have changed it slightly to add the opposite text.

      =IF((F4+F5+F6+F7)='Fee Summary'!G5,'Fee Summary'!G5, IF((F4+F5+F6+F7)<'Fee Summary'!G5, "ERROR £"&'Fee Summary'!G5))

      I'd like to ask you one more question. If I don't have any values in F4, F5,F6,F7, I'd like also to write the value of 'Fee Summary'!G5. Where do I need to add that condition in the formula? It's a kind of AND/OR I guess...
      At the moment it says:
      =IF((F4+F5+F6+F7)='Fee Summary'!G5,'Fee Summary'!G5
      It would be another condition into that one...Do you think it's possible?
      Something like (but added to rest of the formula):
      =IF((F4+F5+F6+F7)=0,'Fee Summary'!G5

      Thank you very much for all your help.

      1. Yep, the OR statement is what you need:

        =IF(OR(F4+F5+F6+F7)='Fee Summary'!G5, (F4+F5+F6+F7)=0),'Fee Summary'!G5, IF((F4+F5+F6+F7)<'Fee Summary'!G5, "ERROR £"&'Fee Summary'!G5))

  38. Hi Svetlana,

    i need help on the below.i am working on a formula for calculating diesel use.

    CELL A. Indoor or Outdoor
    Cell B. 20
    Cell C. 31
    i want the following conditions to apply.

    if CELL A is either indoor or outdoor, & CELL C is > Cell B, then return value "high", if if CELL A is outdoor, & CELL B is > Cell C with a value greater than 3 , then return value "Low", if however CELL A is Indoor and 50% greater than CELL C, then return value "too high" otherwise pass

    i hope this is clear enough

    1. Hi Chidike,

      You can use a nested IF formula similar to this:

      = IF(AND(A2="outdoor", C2>B2, B2>3), "low", IF(AND(A2="indoor", (B2-C2)/C2>0.5), "too high", IF(AND(OR(A2="outdoor", A2="indoor"), C2>B2), "high", "pass")))

  39. suppose i have 4 columns like A B C D, and i want to use IF foumula. A column contains xxx & others & B column yyyy & other. then if a columns contains xxx & b columns contains yyy then how should i use if formula

    1. Hi Pirsabm,

      You can include the AND statement in the logical test, like this:
      =IF(AND(A1="xxx", B1="yyy"), value_if_true, value_if_false)

  40. Hi, i just wanted to know that if we had 7 columns containing different numbers and if any of 2 columns had 0 value then should be considered dis qualified.

    1. Hi Hadi,

      You can use a formula similar to this:
      =IF(COUNTIF($A1:$G1, 0)>1, "dis", "")

      1. o thanks svetlana, it was so easy and very very helpful. thanks alot....

  41. Hello, I am trying to get column E to be the result of column C +D. IF columns C+D=2, then "0" IF columns C+D>2,"1" but I am not sure how to do this so the entire column is calculated (row by row). Please tell me I do not have to go through individually with the command (I have over 2000 rows). Thank you!

    1. Hello ELISE,

      You can enter the following formula in cell E1 and then copy it down to other cells in the column by selecting E1 and dragging down the fill handle:
      =IF(C1+D1=2, 0, IF(C1+D1>2, 1, ""))

      Please note that if C1+D1<2, the formula will return an empty string (blank cell).

  42. Hey,

    I have searched everywhere to find the correct conditions for my IF function formula. Please help!

    If the cell value is over $50,000 but not over $75,000, add $7,500 plus 25% of the amount over $50,000. I need the range to be over $50,000 but not over $75,000.

    For Example:
    If the cell value is 60,000, I need the formula to add $7,500 plus (excess amount over $50,000)*25% [7500+(60000-50000)]

    1. Hi!

      If my understanding of the task is correct, the following formula should work a treat:
      =IF(AND(A1>50000, A1<75000), 7500+(A1-50000)*25%, "")

  43. Hi Svetlana,
    What do the quotation marks in the formula below stand for?

    =IF(L23="","",(L23-$L$31)^2)

    Thank you.
    Steve

    1. Hi Stephen,

      "" stands for an empty string, i.e. a blank cell.

      So, the formula reads as follows: if L23 is empty (or contains an empty string returned by some other formula), then return nothing (blank cell), otherwise return the result of the calculation.

      1. IF Column A anything equal to Column B then select the value from column C corresponding to Column B.
        I have two different dates in Column A and Column B and want to match them and after matching select the value from Column C corresponding to Column B

  44. Thank you Svetlana

    I have another question. I have a vlookup that is wrapped around an IFERROR function. So when there is an error the cell returns blank. Now I need to have conditional formatting on that blank cell. What is the rule with reference to that cell. Using the cell value is equal to blank or N/A doesn't seem to work. Can you provide some expertise?

    thanks again

    1. Hi Michelle,

      You can create a rule with the formula like =$B2="" where B2 is the top-most cell with your IFERROR/VLOOKUP formula.

  45. hi is someone able to help with the following:

    I have a date format in one cell (CQ)that appears like this 2015/07/24 and I would like to link an IF formula to that date cell. The intention is if the CQ date cell has a date in it (numeric) return the date in tact. And if the same CQ cell contains nothing (blank) return blank "". I am using the following formula but it isn't working:

    =IF(CQ7="numeric",CQ7,"")

    Unfortunately it doesn't like number, date or numeric. What can I put in the spot of "numeric" that will make this formula work?

    thank you in advance

    1. Hi Michelle,

      Excel has a special function, ISNUMBER, to identify numeric values. So, you can write the formula as follows:
      =IF(ISNUMBER(CQ7), CQ7, "")

      Please note that it returns a serial number representing the date and to force it to appear as a date, you need to apply the Date format to the cell with the above formula.

  46. I am trying to find a relatively simple (I hope) formula for a time sheet.

    Column C is the Start Time

    Column E is the End Time.

    Colmun F would be the total hours worked.

    I have worked out =sum(E3-C3)/100-0.3

    What I need is to equate "OFF" and "LV" to 0 in F3.

    Thanks in advance!

    1. Hi Nicole,

      Sorry, I am not sure I understand the task. Do you enter that formula in F3 and want it to display "OFF" or "LV" when the formula returns 0? Please clarify.

  47. Hi Svetlana

    Could you please help me with the below.

    I would like to have one cell with following two formulas:

    =D43*H20

    But also:

    =IF(E43="","","D43*H20")

    So if E43 is blank then the cell is blank and if it is not blank then it contains the formula D43*H20

    Is this possible?

    Thanks so much

    Luke

    1. Hi Samuel,

      You were almost there :) Just remove the quotation marks enclosing the formula in the last argument:

      =IF(E43="","", D43*H20)

  48. A B (Values) C(Answer)
    MISJ 5000 5000
    DIV 500 500
    MISJ 1500 1500
    JV 1000 0

    Please help me to develop formula for above calculation
    I need " MISJ"and "DIV" values in C column

    1. Hi Nandana,

      Here's the formula for C1:

      =IF(OR(A1="misj", A1="div"), B1, 0)

  49. Hello,
    This formula works: =ROUND(IF($E5="MCP",$V5*Rates!B$3,0),2) and this works: =ROUND(IF($E5="CWA",$V5*Rates!B$4,0),2) but I want them be together in the same formaula...multiple conditions. For some reason, today, I am just drawing a blank and am having trouble combining the two.

    Note: Rates!B$3 = 3% and Rates!B$3 = 4% and $E5 is a dollar amount

    Thank you.

    1. Never mind. I got. :-)

  50. HI,

    I could also use some assistance with another formula. I would like to sum the values from FX:GE to get a total score, but if the total score = 16, I would like it say "-1" instead. Is this possible?

    Thanks!

    1. Hi Courtney,

      Here you go:
      =IF(SUM(FX1:GE100)=16,-1,SUM(FX1:GE100))

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