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 29. Total comments: 4557

  1. Hi, I am using a "if" and "and" combination but I'm not getting it right please help me with this.

    If(and(A1>0,b1>0),(average(A1,b1)),(A1,b1)

    How do I correct the false section of this statement if there are options to choose from two alternatives for that

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

      =IF(AND(A2>0,B2>0),AVERAGE(A2:B2),MAX(A2:B2))

    2. A1=4 B1=5
      A2=6 B2=0
      A3=0 B3=3

      So basically I want to find the averages of these two columns such that cells containing zeros are not ignored. For example the average for A1 and B1 is 4.5. Ideally the average of A2 and B2 will be 3 but I want Excel to write 6 instead and then 3 for A3 and B3 and not 1.5

  2. Hello,

    I've tried the formula below however the forth IF doesn't give a logical answer. Would you please assist:

    =IF(G4<5,H4,IF(G4=10,H4+4,IF(G4>=15,H4+6,))))

    G4 = years of seniority
    H4 = annual leave entitlement
    My objectif is to increase 2 days of leave entitlement every 5 years

  3. Hi,

    Would like to calculate some valuse.

    Example day counts the person one who resolves the calls in counts per day ranges is like mentioned below

    1. 1 calls =Rs.75

    2. 2 calls=Rs.150

    3. >3 calls =Flat Rs.300

    Blanck should not consider.

  4. Create a column called “Credit”. If the “Score” of the customer is “less than” 40, then give a “Poor” value in the Credit column. If the “Score” of the customer is “equal to” or “higher than” 40 but “equal to” or “less than” 70, then give an “OK” value. If the “Score” of the customer is “higher than” 70 but “less than” 90, then give a “Good” value. If the “Score” of the customer is “equal to” or “higher than” 90, then give an “Excellent” value. You must use a lookup function WITH an ARRAY Form for this column. Be sure that you implement the EXACT cutoff here using a range of scores with no more than 4 rows. No points will be given for using IF function.

      1. can you please help me to find a formula for this question.

        this what the professor said.
        "You must use a lookup function WITH an ARRAY Form for this column. Be sure that you implement the EXACT cutoff here using a range of scores with no more than 4 rows."

        1. Hi!
          Try the following formula:

          =VLOOKUP(B2,{0,"Poor";40,"OK";70,"Good";90,"Excellent"},2,1)

          This is Vlookup for approximate match

  5. I have a list of names and emails, i need to confirm with a yes or no if the person paid some fees

    im using this formula for the names in one cell:
    =IFNA(IF(VLOOKUP(A14,Table1[[#All],[Name of Person]],1,0)=A14,"yes","no"),"not found")

    and this one for the emails in another cell:
    =IFNA(IF(VLOOKUP(B14,Table1[Email],1,0)=B14,"yes","no"),"not found")

    how can i make it just one formula in one cell, instead of 2 formulas in 2 different cells?

    thank you so much!

    1. Hello!
      Try this formula

      =IFNA(IF(VLOOKUP(A14,Table1[[#All],[Name of Person]],1,0)=A14,”yes”,”no”),IFNA(IF(VLOOKUP(B14,Table1[Email],1,0)=B14,”yes”,”no”),”not found”))

      1. thank you so much!!! it works :)

  6. working days lessthan 26day insentive value is "0"
    working days Morethan 26day to 27 days insentive value is "40" per day Total value (26*40=1040),(27*40=1080)
    working days Morethan 27day to 31 days insentive value is "40" per day Total value (28*60=1680),(29*60=1740),(30*60=1800) and (31*60=1860)
    I want Formula

  7. Hello Expert,

    I need a formula with support following terms...

    if i press a digit between 0 to 5000 then we received result "1"
    5001 to 10000 = "2"
    10001 to 15000 = "3"

    200001 to 205000 = "41"
    2000001 to 2005000 = "401"

    5000 difference

    =IF(C6<5000,"1",IF(C6<10000,"2",IF(C6<15000,"3",IF(C6<20000,"4","5".........................infinity))))

    I need Solution of infinity

    kindly help

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

      =INT(A1/5000)+1

      You can learn more about INT function in Excel in this article on our blog.

  8. I have been working on this for a while and I know I am trying to overcomplicate but I cannot get this formula to cover the one oddball instance.
    I have 3 columns B is my deal credit which will be either 50 for a split deal and 100 for a full deal. So if the deal is 50, the max amt to be paid is 100.00, if it is 100, the max paid is 200.00. Column C is the commission amt that has already been paid, so therefore if the deal is a split deal (50) and they have been paid 50.00, they are paid the difference which would be 50.00 which will go in column C. If the deal is a full deal (100) and the comm amt is 100.00 the amt paid would be 100.00 , the difference between the amt paid and the max amount.
    Here is my formula: =IF(C4>=200,0, IF(B4=50,100-C4, IF(B4=100,200-C4))) It seems to work for them all except the one oddball instance where my comm amt for a half deal is 109.10, so it is leaving my column C with a minus amount of -9.10 which I would like to show up as a 0.
    Thanks so much for your help. I hope I am not too confusing.

    1. Hello!
      I didn't quite understand your calculations, but I suggest this formula

      =IF(IF(C4>=200,0, IF(B4=50,100-C4, IF(B4=100,200-C4)))>0,IF(C4>=200,0, IF(B4=50,100-C4, IF(B4=100,200-C4))),0)

      I hope it’ll be helpful.

      1. That worked! Thank you so much!

  9. What is formulas for incentive calculation contains of this condition
    <26day up to 28days rs40perday and 26 days incentive is "0")

  10. Hello Alexander,

    I'm afraid I have a similar problem to my previous SUMIF one further up the page, but with text instead.
    I'd like to count the number of cells that have specific text, but only if a cell on the same row also has specific text.

    In this case, I'd like to count the number of cells in column B that contain "Category M", but ONLY IF a cell in the same row in column G also contains "Yes". Is this clear enough?

    Because it's text, I suspect SUMIF won't be applicable here. Any help is appreciated.

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

      =COUNT(IFERROR(SEARCH("Category M",B1:B10,1),"")*(--(G1:G10)="Yes"))

      This is another way to solve your problem

    2. I think I've solved the problem using COUNTIFS and multiple criteria. Thank you for all the help and advice you've given to everyone!

  11. I have 4 blank cells, every time I add a date in the 1st cell, a specific value should be returned, if not it should check the 2nd cell and if the 2nd cell has a date it should return another specific value and so on. I tried using the ISBLANK function but it does not work correct for me.

    1. Hello!
      Please try the following formula:

      =IF(ISNUMBER(A1),B1,IF(ISNUMBER(A2),B2, IF(ISNUMBER(A3),B3,IF(ISNUMBER(A4),B4,"" ))))

      Hope this is what you need.

  12. I have questions I have employees data I want to calculate commission according to their region wise give some conditions like in North region who r having above 5000 give 5% and 8000 to 10000 give 12% like that and same who r have south region above 5000 give 7% and 8000to 10000 give 12% like that I want how to write conditions in excel using formulas.

  13. Trying to figure out a formula. If "Exceeds" and High Potential" = 1 or "Meets" and High Potential" = 2 or "Low" and High Potential" = 3

  14. Hello!
    My name is shabir
    I'm looking for the formula that can count only 7 lowest grades out of range.
    Example there are 12 subjects which students should sit for in every term. So to rank their positions a teacher needs to pick only 7 out of 12 subjects of which a student has performed better. Regarding that A = 1, B= 2, C= 3, D=4, And F =5
    Please help

  15. How do you put multiple values from different sheets to return those values separated by commas into one cell?

    Sheet #1 is the Summary page with two columns: Column A: Names (A1: A28); Column B: (where I want to have all the attended conference names in the row w/ its respective name.)

    Sheet #2, #3, #4, etc are the conference sheets that contain a table with Column A: Names (A1: A28), and the Conference name in cell C6.

    What formula works to say, "If the name from Sheet #1 is listed in Sheet #2 (column A), then bring the conference name in cell C6 of that specific sheet, and add it to Sheet #1/Column B/row of that particular Name...and continue to concatenate all of these values into the same cell, separated by a comma.

    Thank you for your help.

    Regards,
    D$

    1. Hello!
      I believe the following formula will help you solve your task:

      =CONCATENATE(IF(ISNUMBER(MATCH(A3,Sheet2!A1:A28,0)),Sheet2!C6,""),IF(ISNUMBER(MATCH(A3,Sheet3!A1:A28,0)),Sheet3!C6,""),IF(ISNUMBER(MATCH(A3,Sheet4!A1:A28,0)),Sheet4!C6,""))

      =TEXTJOIN(",",TRUE,IF(ISNUMBER(MATCH(A3,Sheet2!A1:A28,0)),Sheet2!C6,""),IF(ISNUMBER(MATCH(A3,Sheet3!A1:A28,0)),Sheet3!C6,""),IF(ISNUMBER(MATCH(A3,Sheet4!A1:A28,0)),Sheet4!C6,""))

      You can learn more about MATCH function in Excel in this article on our blog.
      Please have a look at this article — TEXTJOIN function in Excel to merge text from multiple cells

  16. Hi,
    Is this formula correct? I need to find a value in another sheet
    =IF(MATCH(A1,Sheet1!A:A,0),"TAGAYTAY", IF(MATCH(A1,Sheet1!B:B,0), "DASMA", IF(MATCH(A1,Sheet1!C:C,0), "NAIC")))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
      Describe in detail what problem you have, and I will try to help you.

    2. on the 1st IF statement the value of A1 is appearing, but when I tried on the 2nd IF statement the value returns #N/A

  17. Hi,

    Please help me in this IF function formula. So far I cannot get it work.

    IF A2=0% OR B2=0% OR C2=0% OR D2=0%, then F2 is equal to (A2+B2+C2+D2)/3,

    IF A2=0% AND B2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF B2=0% AND C2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF C2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF A2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF A2=0% AND B2=0% AND C2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    IF A2=0% AND B2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    IF A2=0% AND C2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    IF B2=0% AND C2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    Thanks!

    1. Hello!
      Please re-check the article above since it covers your task.
      For example,

      =IF(OR(A2=0,B2=0,C2=0,D2=0),A2+B2+C2+D2)/3

  18. Hi Boss,

    Need your support to correct the formula for me.

    =if(RC[-5]=7,RC[-1]>"00<"07:01","Achieved","Not Acheived")

  19. Hi! I need help on this:
    this is for an exam..

    Correct answer: Good
    If your answer is "no good", "hold" or "for reworks" the result is STRICT.

    Correct answer: No good
    If your answer is "good", "hold" or "for reworks" the result is SWEET.

    Correct answer: "Hold"
    If your answer is "good" or "for reworks" the result is SWEET then STRICT if "no good".

    Correct answer is: For reworks
    If your answer is "good" the result is SWEET then STRICT if "no good" or "hold".

    Thank you!

  20. Hello,

    I urgently need help here, how would I format:

    If A1 is yes then B1 cell is color green with text "YES" or if A1 is no then cell B1 is red with text "NO"
    Kind of like an attendance deal.

    Also is there a way to say: if any in A1-A10 is YES then cell A12 is color green (no text) but if there is any NO then A12 is color red (no text).

    I can't figure out the color element so I'd like to see both ways to see which one will be easier to implement.

    Thanks in advance!!
    Linda

  21. here is what I'm trying to solve:

    Cell could have the following data in it: apple, orange, grape, celery

    Looking for an IF statement that would drop into another cell either fruit or veggie.

    IF apple, orange, grape = fruit, if celery = veggie

    hope that makes sense. Thanks for your help.

    1. Happy to report that I figured it out!!! Thanks!

  22. hello I need help here
    If column A2 is less or equal to 70 and the sum for cells D2:AA2 is 50 I want it to say "more effort Please" for else to leave it blank. I have tried this but it is not working
    IF((AND(A2<=70, SUM(D2:Aa2=50)), More effort please", " "))
    Thanks

      1. Thank you so much it worked very well

  23. I want to color a cell based on whether it is a date or not. Eg:if A1 has a date, E1 should be green otherwise, no color.
    The closest I've got is using 'ISNUMBER' function. But this will color E1 even if I randomly put a number in A1.Is there anyway I can specify it to date?

    1. Hello!
      I think that the cell in which the date is written must have a date format. Therefore, I recommend using the CELL function and checking what format the cell has.

      =CELL("format",F3)

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. I got it.. thank you?

  24. Hi,

    I'm trying to setup an if statement needing both the and & or functions.

    The OR statement is =IF(OR(Recruits="Yes",Refresh="Yes"),1,0)
    The AND statement will be if Eng = Yes, and IF(OR(Recruits="Yes",Refresh="Yes")

    I can get both to work individually, but not together, is this possible?

    I have created a matrix to see all possible outcomes, to test (Below)

    Eng Recruits Refresh
    Yes Yes Yes
    Yes Yes
    Yes
    Yes
    Yes
    Yes

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(AND(A2="Yes",OR(B2="Yes",C2="Yes")),1,0)

  25. Related text need to display in 1st table from the 2nd table, If we write a remarks in the 2nd table following and related text in the 1st table -- can any one write the if formula for this

  26. If age is between 18 and 56 and Sex is either "M" or "F", remark is "Qualified"

    2. what is the formula? If age is between 18 and 56 and Sex is either "M" or "F", remark is "Qualified"

  27. What is the formula"if function" if you are getting the valedictorian, salutatorian in a class with the basis of ranking?

  28. Hi, I notice many IF scenarios above, but none that fit my scenario where I am also using a hyperlink. Any chance you can assist with the answer? Below is an example of what I am doing. The only thing I am trying to add is when D12 & D13 = other number combinations, I want that to reference a different hyperlink (image). Is it possible to add multiple IF's and hyperlink's in a single formula?

    =IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),"")

    I know this formula is wrong, but it depicts what I am trying to do with as many conditions as I need:

    =IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),IF(AND(D12=5,D13=2),HYPERLINK("https://test2.jpg","View Layout"),"")...so on and so on

    Thanks,
    Kyle

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

      =HYPERLINK(IF(AND(D12=2,D13=1),"https://test1.jpg", IF(AND(D12=5,D13=2),"https://test2.jpg","")), IF(AND(D12=5,D13=2),"view2", IF(AND(D12=2,D13=1),"view1","")))

      You can learn more about HYPERLINK function in Excel in this article on our blog.

  29. I have used the formula =IF(P2="V13-1X-BLU-1",N2+1457), so when the cell contains the text V13-1X-BLU-1 it adds 1457 days to the date (N2 contains date). Is it possible expand the formula to recognise more text (V9-2X-BLU-1) and make different additions (+804) ?

  30. I hope someone could help me to condition, where I can have a column where it would either say “Complete” and “Incomplete” to reflect overall completion of requirements? The value is either Yes or No. If all answered 'YES' it will show "complete" and if any "No" answered "incomplete

  31. Hello! I'm trying to do a multiple conditional formula and I'm having trouble getting it to work...
    I'm trying to make something like this:
    If A is TRUE and B is TRUE, the formula should be something like AVERAGE(A;B)
    If A is TRUE and B is FALSE, then it should be =A
    If A is FALSE and B is TRUE, then it should be =B
    If A is FALSE and B is FALSE, =0
    I wanted to do it with multiple conditions, like A, B, C...
    How can I make it work? I tried everything I knew.
    Thanks!

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use a formula like this:

      =IF(AND(A1=TRUE,B1=TRUE),AVERAGE(A1:B1), IF(AND(A1=TRUE,B1=FALSE),A1, IF(AND(B1=TRUE,A1=FALSE),B1, IF(AND(A1=FALSE,B1=FALSE),0,""))))

  32. I NEED TO CREATE A FUNCTION:

    =IF (IC25>IA25, "URGENT", "OK")

    BUT I ALSO WANT IT TO LOOK AT COLUMN - ID IS GREATER THAN 1 THAT IT CREATES "ON PO"

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(ID25>1,"ON PO",IF(IC25>IA25,"URGENT","OK"))

  33. Please provide formula :
    IF B4=290,B4=2449,B4=250 C2="",C3+"", if True " Please provide this information" if False the data in E1.

  34. Found really useful thank you!

  35. I need a formula for below conditions (urgent required):

    01. if (a1 or b1 or c1 = "P", 1,0
    02. (i) IF(A1:C1)="P", "THREE",0 (three cells value are "P")

    (ii) IF (A1 AND B1="P" AND C1="") OR (A1 AND C1="P" AND B1="") OR (B1 AND C1="P" AND A1=""), "TWO",0
    (any two cells are "P" from three cell)

    (iii) IF (a1="p" and b1 or c1="") OR a1 and b1 ="" and c1="p") OR (a1 and c1="" and b1="p"), "ONE",0
    (only any one cell is "P" from three cells)

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to all your questions.

  36. =TEXTJOIN("/",TRUE,IF(K2>=45,45,IF(K2=40,"35/5",IF(K2=30,"25/5",IF(K2=20,"10/10",IF(K2=15,"10/5",IF(K2=0,"",K2)))))

    Could someone explain what is wrong with formula or if I'm using the wrong mechanism.

    This is the error I am receiving "Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 7 arguments."

    Any help is appreciated, thanks.

    1. Hello!
      Please try the following formula:

      =TEXTJOIN("/",TRUE,IF(K2>=45,45,IF(K2=40,"35/5",IF(K2=30,"25/5",IF(K2=20,"10/10",IF(K2=15,"10/5",IF(K2=0,"",K2)))))))

      Hope this is what you need.

  37. i am using formula which is below mentioned but the result is for "yes" ok but for "#n/a" result is same #n/a but i need to show the result is "NO" which is base on formula.

    example: =IF(I1="#N/A","NO","YES")

    regards,
    ABAS

  38. Hello! I need a formula to display a scholarship.

    These are the conditions:

    if final_grade < 8 print “No receive”;

    • if 8 ≤ final_grade < 9 print 300 ;

    • if 9 ≤ final_grade < 9,5 print 400 ;

    • if final_grade ≥ 9,5 print 600 .

    of course, I have a column where final grades are placed.

    I wish I was clear with my commnet... I can't wait for an answer!

    Thank you, All the best!

    2. my difficulty is to use more conditions in "IF" syntax... I don't know how to do that...

      1. C6=GRADE
        =IF(C6<8,"NO RECEIVE", IF(C6<9, 300,IF(C6<9.5, 400, 600)))

      2. I tried to use more '' IF '' in Formula Bar ( like in Progamming for example - elseif ... ... ...) but it doesn't work and I don't think it's possible something like that.

  39. If cell C2 is greater than 50000, then give 20% discount, if less than 50000 give nil discount. Please assist me with the correct IF Function

  40. Thank you for this page. I used to work in an office and now use Excel to calculate my marks as a teacher but it's been a while since I've used the IF formula. So grateful to have found your summary!

  41. Hello, Can you please help me with the below formula , I want to set an Active, Non Active status relating an end date of a contract based on today date.
    Thank you,

  42. Hello,
    Column A has names of people ( repititions are also present )
    Column B has multiple codes names with which they are called
    example -
    column A(names) column B( code name) Column (pocketmoney) --------- sheet 1
    James Jamie ?
    James Jam ?
    nick Nicky ?
    john jonny ?
    derek D ?
    thomas Tom ?
    derek D dog ?
    thomas TIM ?

    a different sheet is same excel has data as below -- sheet 2

    Column A(fathers name) column B(mothers name) Column C ( pocket money ) Column D(code nam)
    ABC AAA 1$ Jamie
    XYZ BBB 2$ Jam
    XXX CCC 3 Nicky
    YYY DDD 4 jonny
    ZZZ EEE 5 D
    ZXZ FFF 6 TOM
    ZZY GGG 7 D Doog
    AZZ HHH 8 TIM

    My condition is as below
    If same names have multiple code names in sheet 1, then allocate the pocket money for children( column A in sheet ) based on their codes names in Column D of sheet 2

    example -
    James has 2 code names ( jamie and jam) , so the one with code name jam should get 2 $ as pocket money and jamie should get one

    i need a formula to write in column C of sheet 1 to retrieve the pocket money of the children having same name but different nick name

  43. I am creating a spreadsheet for a darts score card. I want to display "winner" in a cell when a player's score reaches zero and Loser in another cell at the same time for the opposing player. I also want to display Game on in both cells whilst the game is in progress, i.e. when both players score is greater than zero,
    I have managed to display winner when either player reaches zero, and Game on when they have a score greater than zero, but I am struggling to achieve displaying loser in the opposing cell which will still display Game on.

    1. Hi,
      Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  44. I required a formula

    Name factory Name invoice type Qty
    A Rawal Plant GST 478
    B AJK Plant without GST 325
    C Rawal Plant without GST 843
    D AJK Plant GST 275
    E AJK Plant GST 564
    F Rawal Plant without GST 528
    G AJK Plant without GST 362
    H AJK Plant GST 358

    Required data separately Qty
    AJK Plant GST ?
    AJK Plant without GST ?
    Rawal Plant GST ?
    Rawal Plant without GST ?

  45. I have to measure the height of the balls in a a ball mill to determine how many balls to feed example

    Say I measure 100 cm from the height in the ball mill , there is another table that I have that gives ranges that 95 cm to 100 cm you add 1 kg of balls into the mill ,

    My question is how do I add my table that gives the ranges so that when I do the measurements and put them into my excel sheet it automatically looks at the ranges and automatically tells me add 1kg or 2kg etc... not sure if you get me but if you do I just need a formula to use

  46. I know how to reference cells on other sheets, but I'd trying to point to a cell on another sheet IF the date at the beginning matches the date I input.

    For example: I have dates running down the A column on Sheet1, and total sales running down the B column also on Sheet1.

    On Sheet2, I'd like to enter a date into A1 and, on A2, I'd like it to tell me the total for that day, as already listed on Sheet1.

    ---

    Sheet1:
    Dates:
    A1 - 01/04/21
    A2 - 02/04/21
    A3 - 03/04/21
    etc.

    Total sales:
    B1 - 7
    B2 - 4
    B3 - 8
    etc.

    ---

    Sheet 1:
    B:B - I can input the total sales for the day in A:A

    Sheet 2:
    A1 - I can input any date.
    A2 - show me the total sales for that date.

    ---

    I've tried using =IF on Sheet2 and manually nesting formulas for each cell in each row I want to reference on Sheet1, instance by instance.
    Eventually, I'd end up nesting over 100 formulas in a single cell on Sheet2.

    Is there a way to do this more generally?

    1. Hello!
      If I understood the problem correctly, the SUMIF function can be used in cell A2. You can learn more about SUMIF in Excel in this article on our blog.
      I hope my advice will help you solve your task.

      1. Hi Alexander,

        That's exactly what I needed. Perfect! Thanks for that :)

  47. Hi

    I hope you can help.

    I am looking to automate a cash flow forecast using figures from a separate 'Budget' tab, so that payments are listed on the specific day that they are due.

    I need to cater for various different suppliers, some of which are paid at the end of the month and others at various days during the month.

    I have a separate column which displays the payment due date, which will be listed as '31' for end of month (to help identify those that need adjusting for shorter months), and the specific day of month for all others.

    In short, I need the budget figure 'Budget K16' to only show on the specific day confirmed on each line ('B24') for that supplier, and if not due, to show as zero/empty.

    Budget K16 = Payment amount from other tab
    B24 = Day of month that payment is due
    D3 = Date

    I have come up with the following formula to ensure that the payments are displayed on the day that they are due to be paid, but the IF statement is not working.

    =IF(AND(OR(B24="31"), AND(D3=EOMONTH),'Budget '!$K$16,0),OR(DAY(D3)=$B$24,'Budget '!$K$16,0)

    Are there any obvious errors with this? Any help would be much appreciated.

    Kind regards

    Dave

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Correct me if I’m wrong, but I believe the formula below will help:

      =IF(OR(AND(B24=31, D3=EOMONTH(MONTH(D3),0)),DAY(D3)=$B$24),$K$16,0)

      Use external links to other sheets in your workbook. I cannot do this.

  48. I have to use single cell as a condition for the whole column.. How can i use it?

    For example in the below formula i have to use cell Formula!A2 for all the cells in the column W in the workings spreadsheet.

    =IF(Workings!W2=Formula!A2,CONCATENATE("V2",".",A3),CONCATENATE("I2",".",A3))

  49. Hello,

    Needing some help with a formula that will produce my values that might have a letter at the end of it. IE.

    =IFERROR(IF(OR(VALUE(B90)7000),"",VALUE(B90)),"") this works perfectly fine when I am doing only numbers like 1000 or 4000, but when I have a 3429G, this formula will obviously leave my cell blank. I need to get my cells that might have inventory letters and numbers together to post in that cell. Is there a formula to help identify if there is text within numbers?

    1. Hi,
      If there are no letters in a cell, it can be converted to a number. If this is not possible, then there are letters in the cell.
      For example:

      =IF(ISNUMBER(--(B1)),"Number","Text")

      I hope my advice will help you solve your task.

    2. Sorry it messed the formula up for some reason. Here is my current formula:

      =IFERROR(IF(OR(VALUE(B86)7000),"",VALUE(B86)),"")

  50. I'm looking to do a mail merge from an excel sheet to a word document/contract. I need a formula that will basically say any time you see "One (1)" in the document then "First (1st)" will appear throughout where needed. If "Two (2)" is written, then "Second (2nd)" would appear when needed. I would like to have this up to "Ten (10)" if possible.

    Any ideas for a formula?

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