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

4538 comments

  1. Trying but not succeding....
    Trying to make a calculation based upon a letter in a cell. I have a single calculation figured out, but i want to add another condition.
    Here is my original formula: =IF(M35="Y",F35*70%,F35*65%)

    Now I want to add if M35=D, then multiply by 100% and keep the rest above also.

  2. Hello!

    how can I get the formula for this,

    I want to show the output of this available,discontinued,N/A
    become like this available,Out of Stock,Not available in daily update,

    The discontinued will be Out of Stock and the N/A will be Not available in daily update.

    Thankyou for helping me.

    • I think what you wanna say is to change the way a formula express such as:
      Discontinued-->Out of Stock
      N/A-->Not available in daily update
      You can customize your own expression by using "things that you wanna say" in the formula.
      example were in the content above.

      hope this helps.

  3. Good evening,
    Would you please help me with Formula? i will greatful.
    value A (input number)
    value C (standard value)
    Value B 100.

    if value A is less than 55% of value C then output 0
    if value A is between 55% to 99% of vale C then output (80% of B)
    if value A is 100% or 110% of value C then output (100% of B)
    If value A is 100% to 120% of value C then output (120% of B)

    • Hi!
      The tutorial above contains answers to your question.
      You can use this formula:

      =IF(A1/C1 < 55%,0,IF(A1/C1 < 99%,B1*80%,IF(A1/C1 < 110%,B1,IF(A1/C1 < 120%,B1*120%,""))))

    • Hi, Sir Alexander! I am new to excel. Just wanna ask if how will my IF function go if for example, there is one class session per week. One session costs $100. And there are 5 students at max. In the left row, the teacher will indicate Present or Absent. But there are times in a session where not all 5 students are present. Eg only 3 are present, $100 will be divided to only 3 who are present. The cells on the right (after Present/Absent column) should be in numerical form. Thank you, Sir! Hope you can help me with this case..

  4. Hello thank you for this article and the further examples provided in the comments.

    I have a Bonus with 3 criteria (KPIS) attached. They get full bonus if all three criteria/ KPIs are met. The bonus is reduced if any of the 3 criteria are not met. Criteria 1 and 2 are worth 10% and criteria 3 is worth 5%

    e.g.

    if target is met bonus is £500

    I will enter the data against the 3 KPIs that show if each one is met or not. if all three are met bonus would remain at £500 if KPI 1 is not hit the bonus will reduce by 10% £50 and be £450, if KPI 2 is not hit the bonus will reduce by 10% (of the original £500) so another £50 and KPI 3 would be £25

    Any combination of KPI1, KPI2 and KPI3 is possible, so there are 9 scenarios I think. for example it would be possible for KPI 1 to not be met but 2 and 3 be met.

    I would like the bonus figure to be calculated automatically.

    I cannot work out how to write this, please help!

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

      =B1-B1*((A1=0)*0.1+(A2=0)*0.1)-(A3=0)*25

      where A1 A2 A3 - KPI
      B1 - bonus (500)

  5. Hello Alexander,

    I'm trying to use an IF statement to return the expression "HD" in case the index cell I'm looking for has any of the values: A, B, C, D, E, F, G
    To better explain this I started with the following formula:
    IF(INDEX(January22;N7-13;$C$5)=(????);"HD";" ")
    Where I wrote "????" in the formula is where I tried to use an OR statement to check the values A, B, C, D, E, F, G but it doesn't work.
    Can you help me with this one?

    Thank you in advance!

    • Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets.
      Here is a formula that compares a cell with a set of characters.

      =SUM(--(A1={"A","B","C"}))>0

  6. Sir,
    I have querry where column A has two names and column B has one name but if the name in column is a part of column A the in column C only the non same name should come

    IN SHORT Common Name Part or Full should be removed

    A (COLUMN) B (COLUMN) C (COLUMN)
    SHAILESH SINGH SINGH SINGH
    RAMESH PATEL PATEL PATEL
    SURESH SHAH SURESH SURESH
    ANAND SHAH ANAND ANAND

    Please Guide
    Thanks
    Shailesh

  7. Hi!

    I am trying to create a fairly complex IF formula and am having difficulties, any guidance would be appreciated!

    I want the cells this formula is in to fill in as 1SD, 2SD, or 3SD based on the range of 3 different sets of cells. I am able to get it to work with one and two arguments but get the "Too many arguments" error when I attempt to add the third one in. I wasn't able to get it to work by nesting the IF arguments either but I'm likely doing it incorrectly. I have tried nesting the IFs and just get the "This formula has errors" box.

    Working with two arguments:
    =IF(OR(AND(Q14>=D13),(D13=D13),(D13=D13),(D13=D13),(D13=D13),(D13<=R16)), "1SD", "2SD", "3SD")

    Thanks in advance!

    • Hello!
      I am not sure I fully understand what you mean. What is the meaning of the condition (D13=D13) ? Why repeat it 4 times?
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

      • Apologies, Alexander, that definitely pasted incorrectly!

        Here is the formula I've been trying to get work:
        =IF(OR(AND(Q14>=D13),(D13=D13),(D13=D13),(D13<=R12), "1SD") but am having difficulty getting the IF arguments to nest properly.

        So, if we take the data as follows:
        the value entered into cell D13 is 3.000
        based on the values in these range of cells:
        1SD: Q12 = 0.232, R12 = 1.762
        2SD: Q14 = -0.532, R14 = 2.526
        3SD: Q16 = -1.297, R16 = 3.291
        Then G13 (the cell containing this formula) would fill in as 3SD since the value of 3.000 falls into that range.

        Also, the values in the Q and R columns change based on the values entered into column D; they are not constant.

        Thanks!!

        • Hi!
          Have you tried the ways described in this blog post?

          =IF(AND(D13 > Q12,D13 < R12),"1SD",IF(AND(D13 > Q14,D13 < R14),"2SD",IF(AND(D13 > Q16,D13 < R16),"3SD","")))

          Please note that the value 1 is suitable for all 3 ranges

          • This worked beautifully, thanks!!

  8. how do I write a formula for the following:

    I need a my formula to count as 1 if the following criteria are met in two different columns within a different worksheet.

    so in worksheet2 if the word Apples - Red appears in column A and the date in column G is older than 30 days count as 1.

      • Thanks it returns a blank cell but should return 7, I'm wondering if the Today formula is correct, I need it to count if the date (are all different dates) entered anywhere in column G are 30 or more days old not 30 days from today.

        If my worksheets have names and I need to only include data from A2 to A300 is the below formula still correct:

        =IF(AND('Data Sheet 4 - Fruit!'A2:A300="Apples-Red",'Data Sheet 4 - Fruit!'G2:G300-30>TODAY()),1,"")

        Also if the criteria isn't met could it return a 0.

        Thanks

        • Hi!
          If you need to find the difference between dates, use the second date instead of the TODAY() function. If you want to get 0 in case of a negative result, replace "" with 0 in the formula.
          Please read carefully the guide I linked to.

          • Alexander Thanks I have read the guide so many times but I'm so confused and its still not returning the result it should.

            I don't need a count of dates between each other I need the formula to count how many Apples - Red in column A2:A300 and if any dates in column G2:G300 that are older than 30 days, basically I need to count how many Apples-Red are past their use by dates by more than 30 days??

              • But if I use Today()-30, won't it count the dates that are 30 days less than today? If so I actually need the formula to count how many of the dates listed are out of date by 30 days i.e.

                Column G has:
                Used by dates
                20/3/2021
                31/12/2021
                30/12/2020
                4/5/2021
                1/2/2021
                4/2/2022
                etc

                I need to know of the dates in column G how many are over their used by dates by 30 days and I need the formula to count how many in total.

              • Hi!
                The formula I sent to you was created based on the description you provided in your first request. If you need to consider dates that are 30 days less, use the <= operator

                "< ="&TODAY()-30)

  9. Data is in A1= 100. (this is fix data ).

    B1-B10 = date wise high data will come
    C1-C10=. Date wise low data will come
    D1= Pass/Fail (fix Data)

    1.
    Now I want if D1= Pass then if B1 to B10 >= A1+20. Then Result "CLEAR" BUT C1 to C10 <A1-20

    2.

    If D1=Fail then if C1 to C10< A1-20. Then Result "Fail" That time B1 to B10 <A1+20

    3- this Rule should be applied Row wise. If CLEAR Result come first then CLEAR will be valid Output after that if Fail condition met in next row then Fail is invalid in this condition and Results should not chnge to Fail.

    Same For If Fail came first after that Clear Result is not valid.

    • Hi!
      Unfortunately, I couldn't understand your description of the problem. Write an example of the source data and the result you want to get.

  10. I need help writing =if formulas.
    I have the following'
    A B C D E
    ID age flag
    83125782 63 1 63 Final
    82343216 63 1 63 Final
    82059889 63 1 63 Final
    82843001 56 1 56
    82843001 34 1 56,34 Final
    82843001 15 15
    82843001 13 13
    86835004 61 1 61
    86835004 14 1 61,14 Final
    83326997 58 1 58
    83326997 51 1 58,51
    83326997 19 1 58,51,19
    83326997 17 1 58,51,19,17
    83326997 13 1 58,51,19,17,13 Final
    85120571 32 1 32 Final
    82225798 47 1 47 Final
    82488841 54 1 54
    82488841 48 1 54,48
    82488841 15 1 54,48,15
    82488841 10 1 54,48,15,10 Final

    My formula for D is =IF(AND(A1=A2,C2=1),D2&","&B2,B2)
    and formula for E is =IF(OR(A2A1,AND(C21,A2A1))=TRUE,"Final","")

    I want to essentially concatenate the ages for each series of ID only if they have a value of 1 in C.
    and when I have all the ages for a particular ID write Final to the last concatenated ID in the series.
    There are multiple ID's as well as single ID's.
    Thank you.

    • Hello!
      If I understand your task correctly, the following formulas should work for you:

      =IF(AND(A1=A2,C2=1),E1&","&B2,B2)

      =IF(OR(AND(C1=1,A2<>A1),AND(A2=A1,C2<>C1)),"Final","")

      I hope I answered your question. I

      • Hi Alexander,
        I put the wrong data.

        A B C D E
        ID age flag
        83125782 63 1 63 Final
        82343216 63 1 63 Final
        82059889 63 1 63 Final
        82843001 56 1 56
        82843001 34 1 56,34 Final
        82843001 15 15
        82843001 13 13
        86835004 61 1 61
        86835004 14 1 61,14 Final
        83326997 58 1 58
        83326997 51 1 58,51
        83326997 19 1 58,51,19
        83326997 17 1 58,51,19,17
        83326997 13 1 58,51,19,17,13 Final
        85120571 32 1 32 Final
        82225798 47 1 47 Final
        82488841 54 1 54
        82488841 48 1 54,48
        82488841 15 1 54,48,15
        82488841 10 1 54,48,15,10 Final

        What I need to do is concatenate the ages in Column D, as long as the ID (Column A) are the same and there is a "1" in the Flag (Column C). The first 3 rows have different ID's but starting at row 4 through 7 the ID's are the same but for row 6 and 7 the flag is blank so I don't want to concatenate the ages for those rows.
        In addition when ID's change and all the ages have been concatenated I want to write the word "Final" on column E. I hope this time the explanation is better.
        These are the formulas you sent.
        I believe I need to use more IF's and AND's conditions to test for different scenarios.

        =IF(AND(A1=A2,C2=1),E1&","&B2,B2) This one is working fine
        =IF(OR(AND(C1=1,A2A1),AND(A2=A1,C2C1)),"Final","") This one is not giving me the right results.

        Thank you!

        • Hi!
          Your attentiveness could help save a lot of my and your time.

          =IF(AND(A1=A2,C2=1),E1&","&B2,IF(C2 < > 1,"",B2))
          =IF(OR(AND(C1=1,A2 < > A1),AND(C1=1,A2=A1,C2 < > C1)),"Final","")

          Hope this is what you need.

  11. I have finally got the following formula to work

    =IF(F13=45,F13<=100),"Top Dress","")

    is there a way to do it differently to not have the repeated "topdress" if both if statements are true

    • Hi!
      The formula is written incorrectly and cannot work. You can write your terms like this

      =IF(AND(F13=45,F13<=100),"Top Dress","")

      But the first condition does not make sense, since it is always true if the second condition is true.

      =IF(F13 < = 100,"Top Dress","")

      • Ugg.. thanks so much for the response, but just realized the beginning was missing.

        =IF(F13=45,F13<=100),"Top Dress","")

        could you please take another look? I appreciate the help

  12. Hi there,

    Thanks for this article :).

    I have been trying to add a formula where if the H cell result is greater than 900, it will add 450 as a number, or a 0 if less than 900.

    =IF(H651>900,"450","0") - This is the formula here. It is working but it seems to be formatting the 450 or 0 as text instead of a number. Therefore, other formulas don't add up the total amount correctly.

    Thanks for this, this is for an earnings sheet for commissions.

  13. Ahi, Alexander

    Can you help me with the formula for the following

    If the amount is less than Rupees 21001 to 75001 then 0

    Kindly do the needful

  14. Hi Mr Trifuntov,

    How do i formulate let say if A1 has =55, any value in A1 will stay.

    i.e. A1=55 to 65 then B7 shall have the actual value of A1.
    A1>=66, then B7 will show the actual value of A1

    I cant get around the use of a correct excel formula. I need your advice. Thank you

    • correction:
      A1=65, B7 will show the value of A1 "as-is"

      Again, thank you

  15. i'm facing a dilemma, i'm working at a leasing company, and the official reports that i take from our equivalent of the DMV about our vehicle count and status and plate No. comes in Arabic, i want to transfer the arabic character to the english character, because they're using and english letter against an arabic one, for example A always is the first letter of the arabic alphabet, but using CTRL+f and replace is a time consuming process if you do 3 times a week as this report shows which vehicle do have and don't have a person authorized using the vehicle to avoid making my company take violations and force the actual user to pay it, this report make us avoid these kind of issues, do any knows any formula or another way to speed up the process? as macro isn't the way i tried several times in various ways but with no luck.
    The characters transfer chart as below:
    A=أ
    B=ب
    D=د
    E=ع
    G=ق
    H=ه
    J=ح
    K=ك
    L=ل
    N=ن
    R=ر
    S=س
    T=ط
    U=و
    V=ي
    X=ص
    Z=م

    • Hello!
      To do a mass text replacement, use the formula

      =IFERROR(SUBSTITUTE($A2, LOOKUP(999,FIND($E$2:$E$13,$A2),$E$2:$E$13), LOOKUP(999,FIND($E$2:$E$13,$A2),$F$2:$F$13)), $A2)

      $E$2:$E$13 - characters to be replaced
      $F$2:$F$13 - what should be replaced
      If not all characters are replaced, reuse this formula with the result of the first replacement.
      I hope it’ll be helpful.

  16. I have a data where i Wanted to return “On Track” or “Medium” or “High”
    based on
    if A3 = “Submission” & if Y3 is >4 then Z3 is “On Track”
    if A3 = “Submission” & if Y3 is <3 then Z3 is "Medium"
    if A3 = "Submission" & if Y3 is 2 then Z3 is “Medium”
    A3 = “Implementation” & if Y3 is <=2 then Z3 is "High"
    if A3 = "Testing" & if Y3 is <=1 then Z3 is "High"

  17. Greeting to all of you,
    I have face to bellow formula
    Age Age category
    7 10-14
    2 >5
    3 >5
    6 6-9
    7 10-14
    8 10-14
    11 15-19
    13 15-19
    14 15-19
    19 20-49

    =IF(B45",IF(B4<=6,"6-9",IF(B4<=10,"10-14",IF(B4<=15,"15-19",IF(B4<=20,"20-49",IF(B4<=50,"<5"))))))

    • Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(B4<=5,"<5",IF(B4<=9,"6-9",IF(B4<=14,"10-14",IF(B4<=19,"15-19",IF(B4<=49,"20-49",">50")))))

      • Thank you very much dear Alexander Trifuov (Ablebits Team)

  18. i use DATA VALIDATION

    I have the Data on "Data Sheet" D17 to D90 where E17 to E90 are the corresponding amount... I want to put on separate sheet "SOA" with columns "Description" and "Amount"... all I want to get is that when I select a "Description" the amount on the "Data Sheet" will appear on the "SOA Sheet"...

    Example from "Data Sheet"
    D17 = Apron E17 = 500.00
    on "SOA Sheet"
    D12 = Apron E12 = 500.00 and so on the rest of the cells...

    What formula should I use? I try this one NO GOOD!

    =If(D12=Data!D17,Data!E17) it won't follow to the rest of the cells...

    Hoping you can help me with this... Thank you in advance.

  19. I am looking for a formula that will return a numeric value into another cell. I am stumped ,Here's what I require:
    Columns
    A B C D
    Employee name Shift Attend Absent
    Bill Days 1
    Sara Nights 1
    Bill Days 1
    Bill Nights 1
    Bill Days 1
    Bill afternoons 1
    Jim nights 1

    I typed this formula in Cell M4 and I figured that the formula would be =sumif(a4:a10,"Bill",if(b4:b10,"days",c4:c10)) to which the value in cell M4 will be 2.

    The formula works when I just do =sumif(a4:a10,"Bill",c4:c10)

    I am trying to achieve that anytime "Bill" worked throughout the year on "Days" it returns a sum of those days. Bill Days 2. In another cell (M5) I would do the absent days.

    I would then do the same for him on nights and afternoons in different columns.

    any help would be great. I keep getting stuck on #value.

    Jerry

  20. Hello, I am trying to make a grading formula for an audit I am making. It seems simple but I can not figure it out.

    The values of G47 will be 0,1,2,3,4 or 5
    I need a formula that will return a score.
    0=0
    1=2
    2=4
    3=6
    4=8
    5=10

    If someone can help let me know!

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