Excel IF statement with multiple conditions

The tutorial shows how to create multiple IF statements in Excel with AND as well as OR logic. Also, you will learn how to use IF together with other Excel functions.

In the first part of our Excel IF tutorial, we looked at how to construct a simple IF statement with one condition for text, numbers, dates, blanks and non-blanks. For powerful data analysis, however, you may often need to evaluate multiple conditions at a time. The below formula examples will show you the most effective ways to do this.

How to use IF function with multiple conditions

In essence, there are two types of the IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of your IF formula, you should use one of these functions:

  • AND function - returns TRUE if all the conditions are met; FALSE otherwise.
  • OR function - returns TRUE if any single condition is met; FALSE otherwise.

To better illustrate the point, let's investigate some real-life formulas examples.

Excel IF statement with multiple conditions (AND logic)

The generic formula of Excel IF with two or more conditions is this:

IF(AND(condition1, condition2, …), value_if_true, value_if_false)

Translated into a human language, the formula says: If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false.

Suppose you have a table listing the scores of two tests in columns B and C. To pass the final exam, a student must have both scores greater than 50.

For the logical test, you use the following AND statement: AND(B2>50, C2>50)

If both conditions are true, the formula will return "Pass"; if any condition is false - "Fail".

=IF(AND(B2>50, B2>50), "Pass", "Fail")

Easy, isn't it? The screenshot below proves that our Excel IF /AND formula works right: Excel IF statement with multiple AND conditions

In a similar manner, you can use the Excel IF function with multiple text conditions.

For instance, to output "Good" if both B2 and C2 are greater than 50, "Bad" otherwise, the formula is:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad") Excel IF function with multiple text conditions

Important note! The AND function checks all the conditions, even if the already tested one(s) evaluated to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.

In practice, a seemingly correct IF statement may result in an error because of this specificity. For example, the below formula would return #DIV/0! ("divide by zero" error) if cell A2 is equal to 0:

=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")

The avoid this, you should use a nested IF function:

=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")

For more information, please see IF AND formula in Excel.

Excel IF function with multiple conditions (OR logic)

To do one thing if any condition is met, otherwise do something else, use this combination of the IF and OR functions:

IF(OR(condition1, condition2, …), value_if_true, value_if_false)

The difference from the IF / AND formula discussed above is that Excel returns TRUE if any of the specified conditions is true.

So, if in the previous formula, we use OR instead of AND:

=IF(OR(B2>50, B2>50), "Pass", "Fail")

Then anyone who has more than 50 points in either exam will get "Pass" in column D. With such conditions, our students have a better chance to pass the final exam (Yvette being particularly unlucky failing by just 1 point :) Excel IF function with multiple OR conditions

Tip. In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i.e. a cell can be "this" or "that"), then you can build a more compact formula using an array constant.

For example, to mark a sale as "closed" if cell B2 is either "delivered" or "paid", the formula is:

=IF(OR(B2={"delivered", "paid"}), "Closed", "")

More formula examples can be found in Excel IF OR function.

IF with multiple AND & OR statements

If your task requires evaluating several sets of multiple conditions, you will have to utilize both AND & OR functions at a time.

In our sample table, suppose you have the following criteria for checking the exam results:

  • Condition 1: exam1>50 and exam2>50
  • Condition 2: exam1>40 and exam2>60

If either of the conditions is met, the final exam is deemed passed.

At first sight, the formula seems a little tricky, but in fact it is not! You just express each of the above conditions as an AND statement and nest them in the OR function (since it's not necessary to meet both conditions, either will suffice):

OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Then, use the OR function for the logical test of IF and supply the desired value_if_true and value_if_false values. As the result, you get the following IF formula with multiple AND / OR conditions:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

The screenshot below indicates that we've done the formula right: IF with multiple AND & OR statements

Naturally, you are not limited to using only two AND/OR functions in your IF formulas. You can use as many of them as your business logic requires, provided that:

  • In Excel 2007 and higher, you have no more than 255 arguments, and the total length of the IF formula does not exceed 8,192 characters.
  • In Excel 2003 and lower, there are no more than 30 arguments, and the total length of your IF formula does not exceed 1,024 characters.

Nested IF statement to check multiple logical tests

If you want to evaluate multiple logical tests within a single formula, then you can nest several functions one into another. Such functions are called nested IF functions. They prove particularly useful when you wish to return different values depending on the logical tests' results.

Here's a typical example: suppose you want to qualify the students' achievements as "Good", "Satisfactory" and "Poor" based on the following scores:

  • Good: 60 or more (>=60)
  • Satisfactory: between 40 and 60 (>40 and <60)
  • Poor: 40 or less (<=40)

Before writing a formula, consider the order of functions you are going to nest. Excel will evaluate the logical tests in the order they appear in the formula. Once a condition evaluates to TRUE, the subsequent conditions are not tested, meaning the formula stops after the first TRUE result.

In our case, the functions are arranged from largest to smallest:

=IF(B2>=60, "Good", IF(B2>40, "Satisfactory", "Poor"))

Naturally, you can nest more functions if needed (up to 64 in modern versions). Nested IF statement in Excel

For more information, please see How to use multiple nested IF statements in Excel.

Excel IF array formula with multiple conditions

Another way to get an Excel IF to test multiple conditions is by using an array formula.

To evaluate conditions with the AND logic, use the asterisk:

IF(condition1) * (condition2) * …, value_if_true, value_if_false)

To test conditions with the OR logic, use the plus sign:

IF(condition1) + (condition2) + …, value_if_true, value_if_false)

To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

For example, to get "Pass" if both B2 and C2 are greater than 50, the formula is:

=IF((B2>50) * (C2>50), "Pass", "Fail") IF array formula with multiple AND conditions

In my Excel 365, a normal formula works just fine (as you can see in the screenshots above). In Excel 2019 and lower, remember to make it an array formula by using the Ctrl + Shift + Enter shortcut.

To evaluate multiple conditions with the OR logic, the formula is:

=IF((B2>50) + (C2>50), "Pass", "Fail") IF array formula with multiple OR conditions

Using IF together with other functions

This section explains how to use IF in combination with other Excel functions and what benefits this gives to you.

Example 1. If #N/A error in VLOOKUP

When VLOOKUP or other lookup function cannot find something, it returns a #N/A error. To make your tables look nicer, you can return zero, blank, or specific text if #N/A. For this, use this generic formula:

IF(ISNA(VLOOKUP(…)), value_if_na, VLOOKUP(…))

For example:

If #N/A return 0:

If the lookup value in E1 is not found, the formula returns zero.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return blank:

If the lookup value is not found, the formula returns nothing (an empty string).

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return certain text:

If the lookup value is not found, the formula returns specific text.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE)) If #N/A error in VLOOKUP

For more formula examples, please see VLOOKUP with IF statement in Excel.

Example 2. IF with SUM, AVERAGE, MIN and MAX functions

To sum cell values based on certain criteria, Excel provides the SUMIF and SUMIFS functions.

In some situations, your business logic may require including the SUM function in the logical test of IF. For example, to return different text labels depending on the sum of the values in B2 and C2, the formula is:

=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))

If the sum is greater than 130, the result is "good"; if greater than 110 – "satisfactory', if 110 or lower – "poor". Using the IF function with SUM

In a similar fashion, you can embed the AVERAGE function in the logical test of IF and return different labels based on the average score:

=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))

Assuming the total score is in column D, you can identify the highest and lowest values with the help of the MAX and MIN functions:

=IF(D2=MAX($D$2:$D$10), "Best result", "")

=IF(D2=MAX($D$2:$D$10), "Best result", "")

To have both labels in one column, nest the above functions one into another:

=IF(D2=MAX($D$2:$D$10), "Best result", IF(D2=MIN($D$2:$D$10), "Worst result", "")) Using IF together with the MIN and MAX functions

Likewise, you can use IF together with your custom functions. For example, you can combine it with GetCellColor or GetCellFontColor to return different results based on a cell color.

In addition, Excel provides a number of functions to calculate data based on conditions. For detailed formula examples, please check out the following tutorials:

  • COUNTIF - count cells that meet a condition
  • COUNTIFS - count cells with multiple criteria
  • SUMIF - conditionally sum cells
  • SUMIFS - sum cells with multiple criteria

Example 3. IF with ISNUMBER, ISTEXT and ISBLANK

To identify text, numbers and blank cells, Microsoft Excel provides special functions such as ISTEXT, ISNUMBER and ISBLANK. By placing them in the logical tests of three nested IF statements, you can identify all different data types in one go:

=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", ""))) IF with ISNUMBER, ISTEXT and ISBLANK

Example 4. IF and CONCATENATE

To output the result of IF and some text into one cell, use the CONCATENATE or CONCAT (in Excel 2016 - 365) and IF functions together. For example:

=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

=CONCAT("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

Looking at the screenshot below, you'll hardly need any explanation of what the formula does: Using IF and CONCATENATE

IF ISERROR / ISNA formula in Excel

The modern versions of Excel have special functions to trap errors and replace them with another calculation or predefined value - IFERROR (in Excel 2007 and later) and IFNA (in Excel 2013 and later). In earlier Excel versions, you can use the IF ISERROR and IF ISNA combinations instead.

The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA specialize solely in #N/A errors.

For example, to replace the "divide by zero" error (#DIV/0!) with your custom text, you can use the following formula:

=IF(ISERROR(A2/B2), "N/A", A2/B2) Using IF together with ISERROR

And that's all I have to say about using the IF function in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel IF multiple criteria - examples (.xlsx file)

Table of contents

4530 comments

  1. Hi Svetlana,

    I wondered if you could help me with the following problem!

    I have a spreadsheet which shows expenditure, restricted funds and designated funds.

    If the expenditure is greater than the restricted funds, and the restricted funds fall below zero, I want to use the designated funds instead.

    So if expenditure minus restricted funds is less than zero, use the number in the designated column and subtract the difference.

    I hope that makes sense! Sorry if I'm not explaining it very well! Any help is much appreciated.

    Thanks
    Ciara

    • Hi Ciara,

      Yep, it makes a perfect sense except exactly what difference to subtract:

      >use the number in the designated column and subtract the difference.

      Also, please specify the column letters for expenditure, restricted funds and designated funds.

      • Hi Svetlana thanks so much for your help

        Column letters
        Expenditure E
        Restricted F
        Designated G

        Thanks!

        • Ciara,

          Also, please clarify about the difference:

          >use the number in the designated column and subtract the difference.

          The difference between which numbers shall we subtract?

  2. Hi,

    I need some help creating a formula please. I need to create a formula based on the amount of trucks that passes a certain point. The formula should be based on the following :

    1 - 25 trucks = 50 + that figure
    26 - 49 trucks = 100 - that figure
    50 - 99 trucks that figure
    100+ trucks = 100

    Can anyone please assist?

    • Hi Juan,

      Supposing that "that figure" means the number of trucks and it is in cell A1, you can use the following formula:

      =IF(A1>=100, 100, IF(A1>49, A1, IF(A1>25, 100-A1, IF(AND(A1>0, A1<26), A1+50, ""))))

  3. i would like crate formula for if time crossed more than 16:00:00 if after 4:00 Pm of an activity using if function the text should say " Activity delayed", " Actvity on time"...

    Please create a form for the same

    MM Rajesh

    • Hello Rajesh,

      You can do this with the following formula:

      =IF($A1>TIMEVALUE("4:00:00 PM"), "Activity delayed", "Actvity on time")

  4. Hi,

    I hope that this does not confuse anyone and I will attempt to explain what I am trying to do as best as I can.

    I currently have the following formula located in cell P6:

    =IF(O6-TODAY()>0,O6-TODAY(),0)

    This is essentially a countdown cell that stops when the date that appears in cell O6 equals Zero days remaining.

    My question relates to attempting to hide the initial Zero that appears in cell P6 without turning the font white and having to use conditional formatting. I am hopeful that there is a way to write the formula so that cell P6 is blank until a date is entered in O6, then, when the date is entered and the countdown reaches Zero, that Zero remains showing.

    Thanks,

    Dan

    • Hi Dan,

      Your explanation is perfect :) Hopefully, this is what you are looking for:

      =IF(O6-TODAY()>0, O6-TODAY(), IF(O6<>"", 0, ""))

  5. I would like to create a formula that says if a1*.8>10000, return 10000 and if a1*.8<10000, return the value of a1*.8. Can you help??

    • Hello Brandy,

      =IF(A1*.8>10000, 10000, A1*.8)

      Please pay attention that the formula will also return a1*.8 if a1*.8=10000

  6. I am trying to complete a payroll workbook. Sheet 1 is the summary page with columns reading "Hours Worked", "Overtime", "PTO", etc. When I enter in an employees time on their individual timesheet I would like the column for "Hours Worked" to cap at 80 and reflect the Overtime on it's own. I have the Overtime showing on it's but I can't find the formula to cap the hours worked at 80. Hope this makes sense.. :( Please help!

  7. want calculate the count of students have 60 and above, 65 and above and 70 and above in their Subject 1 and subject 2 both. Having trouble as i need to do this for 2000+ students. Please help

  8. sir i used to manage if condition i excel but according to my new task is to get (in bold ) if the answer of a sum got greater than the required number it should become bold
    ex: the answer we got in a sum is greater then or equal to 100 it should become bold if it is below 100 it should remain as same as it's older font
    any one of you know about this please give me a suggestion

    hoping that it can be resolved as soon as posible

    • Hello Pavan,

      Select the cell(s) with the sum and create an conditional formatting rule with this formula:
      =$A2>=100 where A2 is the cell with the sum.

      In the "New Formatting rule" window, click the Format button, switch to the Font tab and select Bold under "Format Style".

      • Kindly provide solution to me also

      • sorry mam the resolution you have given is not about the subject what i had asked and the subject is for example i'll show you bellow
        23 20 43
        0 46 46
        48 24 72
        15 36 51
        52 25 77
        2 45 47
        216 632 848
        as in the above consider rows indicates the sum of two numbers in the adjacent columns and here the third column indicates the sum or answer and if the answer is greater than the required value it should become bold in the same cell itself with its own value (answer required)

        any one of you know about this please give me a suggestion

        hoping that it can be resolved as soon as posible

        • Hi Pavan,

          The solution I suggested is exactly for the following case:

          >if the answer is greater than the required value it should become bold in the same cell itself with its own value

          For more information about Excel conditional formatting, please read this tutorial:
          https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

          When creating a rule, you simply have to replace A in the formula I suggested with the letter of your SUM column.

          • thank you mam
            thank you for your suggestion
            i'll be thank full to you

  9. Hi,

    I have cell X6 with a simple calculation of =K6*W6. I am trying to have this same cell calculate =Q6*W6. K6 & Q6 will never have data in them at the same time, so I am just trying to have this cell show the correct result if one of K6 or Q6 has data.

    Any help would be much appreciated!

    Dan

    • Hi Dan,

      I believe the following formula is what you are looking for:
      =IF(K6<>"", K6*W6, Q6*W6)

      • Hi Svetlana,

        You're an amazing resource and I thank you for your time!

        This formula worked perfectly, could you tell me how I would hide the Initial Zero value that is located in Cell X6?

        Thanks,

        Dan

        • Hi Dan,

          Try this one:
          =IF(AND(K6<>"", W6<>""), K6*W6, IF(AND(Q6<>"", W6<>""), Q6*W6, ""))

          It will multiply the specified cells only of both are not empty, otherwise it will return an empty string (blank cell).

          • Hi Svetlana,

            Thank you!

            Hopefully my final question:

            I have cell AA6 with a formula of =X6-Y6-Z6-U6. Cells Y6 & Z6 do not have a formula associated with them, however, cell X6 has your previous formula =IF(AND(K6"",W6""),K6*W6,IF(AND(Q6"",W6""),Q6*W6,"")) and cell U6 has this formula =IF(AND(K6"",W6"",T6""),K6*W6*T6,IF(AND(Q6"",W6"",T6""),Q6*W6*T6,"")).

            Right now, with no value in any of the cells, cell AA6 shows an error of #VALUE!

            My end goal is to have cell AA6 show a blank cell until any of X6, Y6, Z6 or U6 has a value. X6 will consistently show a value (after it's own formula has been met), but sometimes none of the other cells will show a value and AA6 will simply equal the value in X6. In other instances, sometimes only U6 will have a value, making the end result X6-U6.

            Again, I hope that I have been clear with regards to my question and I appreciate any time that you are able to dedicate to this.

            Thanks,

            Dan

            • Hi Svetlana,

              I have re-written the formula in cell AA6 to now be this: =IF(X6"",X6-Y6-Z6)-SUM(U6). This looks to work how I want it to, with the exception of hiding the Zero value.

              Thoughts?

              • Hi Dan,

                If my understanding of your end goal is correct, the following formula should work a treat:

                =IF(X6<>"", X6-Y6-Z6-U6, "")

  10. My formula =CONCATENATE(D20-C20," (",+ROUND(((D20/C20)*100)-100,0),"%)")

    B C D E
    4203 4276 3916 -360 (-8%)

    returns the value -360 (-8%) which is correct. However, I would like the first part of the returned value, in this case -360 to show in brackets and not with a minus sign.

    I don’t know whether I need to nest an IF Function in there, but if so how please?

    Many thanks.

    Tracco

    • Hi Tracco,

      There is no need for nested IF in this case. You can use the ABS function to return the absolute value of a number, and add another brackets in your CONCATENATE formula:

      =CONCATENATE("(", ABS(D20-C20), ")"," (",+ROUND(((D20/C20)*100)-100,0),"%)")

  11. I want calculate the count of students have 60 and above, 65 and above and 70 and above in their Subject 1 and subject 2 both. Having trouble as i need to do this for 2000+ students. Please help

  12. Hi,

    I have a doubt that if one column for ex. % varies from 0 -100% but i want less than 60%- zero and more than 80% -100 marks and between 60%-80% should get linear score from 0-100 can you tell me??/

  13. HI,

    I need date wise value of the A and b, manual entry date 1 value for A is 10 b is 20, manual entry date 2 value for A is 18 and b is 42, manual entry date 3 value for A is 12 and b is 45,,,,next sheet I need this date wise value for A and B

  14. I have a spreadsheet in which I moderate marks assigned to tests. The if formula I'd like to use should be able to do the following. If say cell C1 is the original mark and B1 is the revised moderated mark, I need excel to give me a mark in A1. If the revised mark in B1 differs more than 5 marks up or down from the orignal it should give me the revised mark if the difference is less than 5 marks it need to give me the original mark. I have entered this formula { =IF(C39=5>C40;C39; IF(C39=5<C40;C39;C40)) and also tried this one =IF((OR(G40=5G39));G39;G40) } but Excel doesn't seem to give me the false value when it should be false and only gives me the true values each time. What formula should I use or how should I change the present one I have?

    Thank you.

    • Just to clarify it more, I changed the cell values in this reply.

      I have a spreadsheet in which I moderate marks assigned to tests. The if formula I'd like to use should be able to do the following. If say cell C1 is the original mark and B1 is the revised moderated mark, I need excel to give me a mark in A1. If the revised mark in B1 differs more than 5 marks up or down from the orignal it should give me the revised mark if the difference is less than 5 marks it need to give me the original mark. I have entered this formula { =IF(C2=5>C3;C2; IF(C2=5C2;C3=5<C2));C2;C3) } but Excel doesn't seem to give me the false value when it should be false and only gives me the true values each time. What formula should I use or how should I change the present one I have?

  15. pls i need correction trying using the less than(<) or equal to (=) sign to calculate a student result using the the following grading system format
    70 and above A 4.00
    60-69 B 3.00
    50-59 BC 2.5
    40-49 BC 2.5
    30-39 CD 1.0
    20-29 F 0.1

    =IF(A1<=100,"A","4.00" IF(A2<=69,"B","3.00" IF(A3<=59,"BC","2.5" IF(A4<=49,"C","2.00" IF(A5<=39,"CD","1.0"IF(A6<=29,"F","0.1"))))))

  16. helo i want to ask that i have four value in four cells like as in a1,b1,c1,d1 , now i want to apply a farmula sepratly two value , and two other value so tell me if condition???

  17. Hi
    I would like to set up a single cell to display the highest value in a column
    Any advice would be much appreciated thank you.

    • Hi Jackie,

      You can use the MAX function, for example =MAX(A:A)
      If a cell with the formula is in the same column, then you need to specify a range, e.g. =MAX(A1:A100)

  18. Hi
    i have a doubt in ms excel can anyone help me . In a column there are different texts
    in that texts there are some same texts. i want to change that same texts as per the end of texts 01,02,03........ like this
    eg play
    play
    pray
    power
    power

    from these columns i want to change as per the end of text 01,02,03............... like this

  19. Hi,

    I am facing a challenge in excel. The problem is I have 5 different values which are an outcome of multiplication of 2 variables. So these 5 different values changes as anyone changes the input variables. Now with those 5 values, I have 5 ratings associated A, B, C, D and E. A should always come next to highest of the 5 values and E should be with the lowest of 5 values. I would want the ratings to reflect it. How can this be done?

  20. Hello....In one column i have 3 text values, how can i get the total of these 3 values

    • Hi Arshi,

      I am not sure what exactly the total of 3 text values is. If, for example you have the following values: A1="a", A2="b" and A3="c", you can concatenate them into "abc" by using the concatenation operator =A1&A2&A3

      If you are looking for something different, please clarify.

  21. need help with multiple arguments and conditional formatting
    I have 03 fields (Cell 1, 2 & 3) (data validated) with a list.
    I want this Cel1 3 to be highlighted in red

    if Cell 1 is "None" and
    Cell 2 is "Non Staff" and
    Cell 3 is ">12 Months"

    The I want Cell 3 to be highlighted in Red

    • You don't need the IF function in Excel conditional formatting. Just use a usual AND as follows:
      =AND($A$1="None", $A$2="Non Staff", $A$3=">12 Months")

  22. I have used a drop down box for column I. Each cell will have a different value based on the drop down box. I need column J to perform a different formula based on what was selected in the drop down box.

    If I8 = "bale", Result J8=H8
    If I8 = "cwt feed", Result J8=sum(C8*H8)/100
    If I8 = "hd/day", Result J8=sum(G8*H8*3)
    If I8 = "blk", Result J8=H8
    If I8 = "cwt mixing", Result J8=sum(G8*H8/100
    If I8 = "lbs", Result J8=sum(G8*H8)/100

    Thank you for your help

  23. Hi,
    I need a help in excel. Suppose in column A, user will be entering the date and in B column month will get updated automatically. formula would be in B column. Condition If cell in column A is blank, then it will return "Text", else it will return the month of the date entered in A column.

    Earliest reply would be highly appreciated..

  24. Hi, I am looking for a formula that will calculate the number of months for a specific time frame within the financial year, 01/04/2015-31/03/2016, and subsequent financial years for the next five years. The specific time frame can change based on two dates, start date and end date so the formula must be able to figure out how many months are specifically within that financial year. So for example cell B3 contains the start date and C3 contains the end date. B4 to B8 would contain the start of each financial year (01/04/2015 and so on). And A5 to A9 would contain the end of each financial year. The answer/formula would be present in cell C4 to C8. Any help would be much appreciated

  25. I need a formula that puts a value in Column C, based on the data in Column A AND B.

    Stock #LOA LOCATION
    1 26 C53
    2 46 D3
    3 30 C23

    Location D
    31-50' 0.5
    51-89' 0.55

    Location C
    21-25' 0.15
    25-30' 0.2

    So for Stock # 1, I would need 0.20 in column C.

    • =IF(ISNUMBER(SEARCH("C",G2)),IF(AND(B2>=1,B2=21,B2=26,B2=31,B2=36,B2<=40),0.35))))))

      This works, but only if it's Location C... not sure if I can have a complete different set for Location D in the same formula?

  26. I need a formula that puts a value in Column C, based on the data in Column A AND B.

    Stock #LOA LOCATION
    26 C53
    46 D3
    30 C23

    Location D
    31-50' 0.5
    51-89' 0.55

    Location C
    21-25' 0.15
    25-30' 0.2

    So

  27. Hi Svetlana !!
    I am trying to highlight a cell if the part of a any text is repeated anywhere in the sheet. Like if I have a data like John 64, if the text 64 is repeated anywhere in the sheet that cell gets highlighted. Is it possible ???

  28. how to extract Dates from text? Example mentioned below:
    Support-to -TARR-APP-1/01/15-31/12/15.
    There are two Dates mentioned in one text string then how to extract both dates in different columns?

  29. Hello

    I have a due date column that is calculated at 30 days after a transfer date column. However, when I put the formula "=A1+30" in the due date column it returns a date of 01/05/1900 for rows that don't have a date entered in the transfer date column. How do I get this to stay blank unless there's a date entered in the transfer column?

    Thank you.

    • Hi Jordan,

      If my understanding is correct, column A is the Transfer column. If so, you can use the following formula:

      =IF(A1<>"", A1+30, "")

  30. I am trying to make an if then that if a cell from another sheet and cell has a time on it i want it printed and if there is no time then to have a blank cell is this possible and if so how.

  31. Hi I would like to get help with nested if
    I would like to calculate performance of employee tasks based on 2 tasks types.conditions.
    1. if task type is "a" then if task finsih in 1 day the grade is "100" if 2 days then the grade "80" and so forth.
    ELSE if task type is = "b" then if 2 days then is "100" and if 3 then "80" and so forth.

    the first half of the formula works, but not the second.

    =IF(A1="task a",IF(B2<0,"0",IF(B2<=2,"100",IF(B2<=4,"80",IF(B26,"50",IF(A1="task b",IF(B2<0,"0",IF(B2<=3,"100",IF(B2<=5,"80",IF(B27,"50"))))))))))))

  32. I'm trying to run a formula for the following but I know I'm missing something. If you could help I would greatly appreciate it.

    In the same cell I would like to do the following arguments, I'm just not sure how to place them.

    IF H214 & D2>100 "PUSH OUT"
    "OK" for everything else that does not fall within this criteria

    Thanks for your help,
    Mindie

  33. Hi there

    Looking for help in writing efficient formula to calculate Forecast attainment in Col E. I have the Reps Target in Col D and I have a column for each of the 4 weeks of the month (Cols F-I). I'd like to use formula to calculate the Forecast attainment of the most recent entered week so probably using if I2 is blank, use H2, if H2 Blank use G2, if G2 blank use F2
    Much appreciated

  34. Hi,
    I am working on Payroll worksheet. In row 1 has 28 consecutive days. In row 2 there are 28 Option buttons under each date and the Cell Link to these button is C1( Form Control as I cannot use macros for security reasons ). So C1 will have any number between 1 and 28 representing button I selected.

    I will use the option buttons to select only 1 date at a time so later I can adjust, using a scroll bar, what date I did overtime and what time IN and OUT. One can only choose 1 option button at a time (i.e.1 date at a time).

    I can use IF(C1=1,A1,IF(C1=2,B1,IF(C1=3,C1,IF(C1=4,D1.....etc, which will be too long as I am planning to edit the worksheet later on for multiple 4 week roasters.

    Can you please help me with a shorter formula to pick the date in Row1 depending on the value in C1.That is if C1=1=date1, 2= date2 etc.

    Thanks Sam

    • Hi,
      Sorry for wasting any of your time.
      Was thinking of IF function all the time when the formula was much easier
      =A1+(C1-1)
      Thanks and Apologies
      Sam

  35. Hello,
    I had a follow up question in posting 59 that I'm hoping you can answer for me. Thank you.

    Jordan

    • Hi Jordan,

      If my understanding is correct, you want the formula to return those texts only if there is some value in the "transfer date" column.

      If so, then you need to add one more condition to the formula. For example, if the transfer column is D, then you add D2<>"" which means D2 is not empty. The entire formula may look like this:

      =IF(C2<>"", "complete", IF(AND(A2<TODAY(), B2="", C2="", D2<>""), "invoice required", IF(AND(A2>=TODAY(), B2="", C2="", D2<>""), "upcoming", "")))

      Where A2 is the "Due date" column, B2 is "Sold date", C2 is "Invoice number" and D2 is "Transfer date".

  36. I am putting together a questionnaire in Excel which is meant to "test" the level of users. The questionnaire automatically provides scores for the users which I've translated into percentages. I need a formula which would give me tell the user what his score (level) is ranging from -1 to 3. Yet, I cannot use MAX because if -for example, the user has 100% in levels 1 and 2, I need excel to recognize that the user is level 3. (See table below). Is there a formula that can help out here?

    Score Points Percentage
    -1 0 0%
    0 5 19%
    1 2 50%
    2 11 92%
    3 9 100%

  37. I have struggled for 2-days with no avail.
    If A1>87508750<=12000,(A2=1)*("8000"))

    Your assistance would be greatly appreciated

    • Hi!

      Sorry, I am now sure I can follow you. This condition (A1 is greater than 87508750 and less than or equal to 12000) can never be met, probably you mean the opposite?

      Anyway, I think it should be something like this:

      =IF(AND(condition1, condition2), value_if_true, falue_if_false)

      If you can clarify the conditions and values to return, I believe we will get it to work.

  38. I tried using the following, but it returns the value if the columns don't match; I just want the missing data piece to return:
    =IF(F10:F14=$E$10:E16," ",E10:$E$16)

  39. I am needing help with what is probably a very simple formula.
    I need a formula that will return missing days of week (ex trying to show days off).
    Example, if I had the days below, what formula could be used to return Sun & Tue
    Mon
    Wed
    Thu
    Fri
    Sat

  40. I am giving students a baseline test and a post-test, seeing the difference within the 2. After the baseline test we come up with a growth target. It's the percentage increase we would like to see, and there are 4 tiers.
    So if the student scores:

    6%-18% should increase 40%
    19%-31% should increase 30%
    32%-45% should increase 20%
    46%-60% should increase 10%

    I would want a formula to take a baseline score between 6-60 and know what percent increase it should have.
    I tried =IF(B10>=46,"10",IF(B10>=32,"20",IF(B10>=19,"30","40")))
    It comes up Err:509

    Seems like the pieces are there, but still not working.
    Thanks in advance.

    • Hi Tim,

      The formula looks correct to me and works just fine in my Excel, except that it returns text strings rather than numbers because you enclosed them in double quotes. If this is okay for you, then it's okay : )

      A quick search in google revealed that error 509 occurs when referencing cells in external Excel books. Is this the case?

      • Thank you for the speedy response. I got rid of the " and it now says Err: 509. I am running openoffice instead of Office. Would that be an issue? Ideally, I would like to share with my fellow teachers in Google Drive. Gonna see if it works in that program. Are there spaces at all in the formula?

        • Sorry, I have no experience with OpenOffice and very little with google sheets, and have no idea how formulas work there.

          • I figured it out, Here is the formula:

            =IF(C2<=18,40,IF(C2<=31,30,IF(C2<=45,20,10)))

            It works for Excel, OpenOffice, Google Sheets.

      • The formula doesn't work in Excel online, OpenOffice, or Google Sheets.

        Here is the formula: (my reference cell is cell B10)

        =IF(IF(B10>=46,10,(B10>=32,20,IF(B10>=19,30,40))))

        I did manage to use another function for a yes/no IF function:

        =IF(SUM(D10-B10)>=C10,"YES","NO")

        • Tim,

          This IF formula is different from what you originally posted and incorrect. Please notice double IF in the beginning and missing IF before (B10>=32,...

          Copying/pasting makes a mess of formulas sometimes. Anyway, I've just tried the fixed formula in Excel Online and it worked fine:

          =IF(B10>=46, 10, IF(B10>=32,20, IF(B10>=19,30,40)))

  41. What forumla do I use if I want it to show:
    If Col A has a date entered, then Col B must show "Send email"?

      • Thank you so much!

      • I thought it was working, but it's making all the cells say "Send email" regardless of whether a date is input in Col A

        • Not to worry, got it sorted using =IF(A2<TODAY(),IF(A2="","Check value","Send Email"),"Check value")

  42. Hello. here's a quick summary of what I have and am trying to do. Have a "Due date" column, "Sold date" column, "Invoice number" column. Need another column that lets me know the status of each line.

    I would like to know the formula needed to return the following 3 options: say "complete" when there is a value in the invoice column (regardless of what other columns say); say "invoice required" when the due date has passed, there is no sold date entered and no invoice number entered; say "upcoming" when due date has not passed, no sold date entered and no invoice number entered. Thanks for the help.

    • Hi Jordan,

      I think the following formula should work a treat:

      =IF(C2<>"", "complete", IF(AND(A2<TODAY(), B2="", C2=""), "invoice required", IF(AND(A2>=TODAY(), B2="", C2=""), "upcoming", "")))

      Where A2 is the "Due date" column, B2 is "Sold date" and C2 is "Invoice number".

      • Hi,

        Thank you, the formula worked great. However, I need some additional help now. I have a fourth column that drives the original columns (Due date, Sold date, Invoice number) called "transfer date". This is the column that starts my whole sheet. Right now if this transfer date column is blank it causes the column I entered your equation into to read "Invoice Required". I'd like it to be blank until data starts getting entered into it's row. Can you let me know what I need to add to your original formula to make this happen? I would like to add this concept to other columns as well. For instance, my Due date column is calculated off the Transfer date column as well and would like it to only calculate a value if something is entered.

        Thanks very much, you've been very helpful.

        Jordan

        • remove the "Invoice required" but leave the "".

  43. if I have two condition and I want a column parallel to that to be picked up in case both condition match what formula should I use. for eg if person "A" with id "1" as done several "training" in different "dates" so I want a formula to catch the date if the id and training name match

  44. A Microsoft Excel sheet contains students roll, name and marks in 5 subjects in column A,B,C,D,E,F and G columns respectively. (Row 1 contains headings and actual data starts from row 2 and onwards)
    i) Using IF and AND operators write the syntax to display PASS or FAIL in H column (assumed pass mark is 45 in each subject)
    ii) Write formula to find percentage of marks obtained in I column
    iii) Write the formula for remarks of students in J column (assumed 80% and above “Excellent”, above 60% “First” otherwise second)
    iv) Write the formula for highest marks (C9) and total passed (K9)
    can anyone teach this???

  45. Hi I would like to get help with nested if
    I would like to calculate performance of employee tasks based on 2 tasks types.conditions.
    1. if task type is "a" then if task finsih in 1 day the grade is "100" if 2 days then the grade "80" and so forth.
    ELSE if task type is = "b" then if 2 days then is "100" and if 3 then "80" and so forth.

    the first half of the formula works, but not the second.

    =IF(A1="task a",IF(B2<0,"0",IF(B2<=2,"100",IF(B2<=4,"80",IF(B26,"50",IF(A1="task b",IF(B2<0,"0",IF(B2<=3,"100",IF(B2<=5,"80",IF(B27,"50"))))))))))))

    Reply

  46. hi, I am new to Excel and finding it rather difficult to understand. I would like a column in the my spreadsheet to show that if the total is cell D is 0 then it to say paid. If the total in cell D is more than 0 than it say unpaid. Is there a formula that would enable this? Is there also any way to change the colour of the cell to green if it has been paid and red if it still unpaid. Would this need to be added into the original formula and if so then how?

    Thanks
    Gillian

    • =IF(D1<=0,"Paid","Unpaid")

      To then change the colour of the cell, you would use conditional formatting by creating a new rule and then selecting "format only cells that contain" and changing the rules and conditions in the box below this.

      Hope this makes sense.

  47. I am using the below array formula to count how many unique values there are amongst a list of data based on certain arguments. It works when referencing a cell in this case it's A4 and A4 contains the text Proactive Contact. The problem I have is we now have new fields in the list which could be Proactive Contact 2, Proactive Contact 3, all the way up to 15. I therefore instead of referencing A4 want the argument to be "contains the text proactive contact" is this possible?

    Current Formula Used

    =SUM(IF(FREQUENCY(IF('BG Proactive Programme 11'!$C:$C"",IF('BG Proactive Programme 11'!$G:$G=A4,IF('BG Proactive Programme 11'!$H:$H=A2,MATCH("~"&'BG Proactive Programme 11'!$C:$C,'BG Proactive Programme 11'!$C:$C&"",0)))),ROW('BG Proactive Programme 11'!$C:$C)-ROW('BG Proactive Programme 11'!$C$2)+1),1))

  48. Hi I want to calculate if a delivery was overdue, we currently have delivery times of 1 - 3 days, can I do this and then highlight in different colours depending on how late the delivery was!!! for e.g. if 3 days late highlighted in green, if 5 days late highlighted in orange

  49. HI!!

    I have been searching for hours how to find the IF function for:

    IF B4 is less than 40, 0. If B4 is greater than 40, the number OVER 40.

    So if the number is 47, how can i get the formula to put 7? Being that 47 is 7 more than 40.

    • Hi Alan,

      You simply put the mathematical expression that calculates the difference in the value_if_false argument, like this:

      =IF(B4<40, 0, B4-40)

      • TYTYTYTYTYTYY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

      • Svetlana could you help with this please?

        I am using the below array formula to count how many unique values there are amongst a list of data based on certain arguments. It works when referencing a cell in this case it's A4 and A4 contains the text Proactive Contact. The problem I have is we now have new fields in the list which could be Proactive Contact 2, Proactive Contact 3, all the way up to 15. I therefore instead of referencing A4 want the argument to be "contains the text proactive contact" is this possible?

        Current Formula Used

        =SUM(IF(FREQUENCY(IF('BG Proactive Programme 11'!$C:$C"",IF('BG Proactive Programme 11'!$G:$G=A4,IF('BG Proactive Programme 11'!$H:$H=A2,MATCH("~"&'BG Proactive Programme 11'!$C:$C,'BG Proactive Programme 11'!$C:$C&"",0)))),ROW('BG Proactive Programme 11'!$C:$C)-ROW('BG Proactive Programme 11'!$C$2)+1),1))

  50. Hi I want to make an IF statement where two factors will decide on the outcome.

    I have a dropbox with 15 points i.e. point 1, point 2, etc...
    Take for example point 1 = 57063.

    I want to then subdivide this figure into the two options of =IF(H8=0.5,28532,IF(H8=1,57063,))

    But how do I link the original point 1 with the formula =IF(H8=0.5,28532,IF(H8=1,57063,)) into the one cell?

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