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. I want to use if function for below details. Kindly assist:

    Slab Commission
    50000 200
    75000 500
    100000 700
    125000 900
    150000 1100

    I want to mark commission upon completing mentioned sales slabs, getting difficulty on doing so. your assistance required.

  2. Hi,
    I am looking to input an If function formula that gives me the sum of a range if the range does not contain text or a negative number, and if the range does contain either a text or a negative number, I need it to say Invalid.

    • Hi Christian,

      You can use a formula similar to this:

      =IF(OR(SUMPRODUCT(--ISTEXT(A2:A6))>0,COUNTIF(A2:A6,"<0")>0),"invalid", SUM(A2:A6))

      • Thanks, this worked out perfectly!

  3. Hi. I am new to excel so my query is probably straight forward. I have a list of numbers which when added together need to return zero if less than an amount, but the calculated number if it is more. I cannot figure out the second part of the calculation. =IF(D3+D7+V4<1801,0 what comes next to provide a sum more than 1801 when it is. Thank you

    • Hi Ellie,

      You were almost there :)

      =IF(D3+D7+V4<1801, 0, D3+D7+V4)

      Just pay attention that the formula returns the sum of values in D3, D7 and V4, if it is equal to or greater than 1801.

  4. hi..i am trying to get the result for name whose res1 and res2 is not "N/A"
    Name res1 res2
    back 1 2
    shift N/A 1
    avast 2 1
    now 3 2
    book1 4 1
    apple 5 N/A
    back 2 N/A
    Avast 3 1
    Shift 7 N/A

    • Hi Alex,

      If you are trying to count names whose res1 and res2 is not "N/A", use this formula:

      =COUNTIFS(B1:B6,"<>"&"n/a", C1:C6,"<>"&"n/a")

      If you are looking for something different, please clarify.

  5. I have a cell that I am importing Numbers or Words into.

    I attempted to use the following equation, which works but I was trying to get it to display an equation in the Number format.

    =IF(ISTEXT(M17), "Did Not Play", IF(ISNUMBER(M17), "Number", IF(ISBLANK(M17), "Did Not Play", "")))

    =IF(ISTEXT(M17), "Did Not Play", IF(ISNUMBER(M17), "=M17-36", IF(ISBLANK(M17), "Did Not Play", "")))

    The equation above does work, but it doesnt display the solution to the equation M17-36, it just displays the words.

    Any help is appreciated.

  6. Hi!

    I have written a formula as below.... it should give a result if I go & check manually in the data for an example, but it is showing as FALSE... please help....

    =(IF(Education_Experience_Matrix!G:G=Table134[[#This Row],[Column2]],Education_Experience_Matrix!F:F,IF(Education_Experience_Matrix!I:I=Table134[[#This Row],[Column2]],Education_Experience_Matrix!H:H,IF(Education_Experience_Matrix!K:K=Table134[[#This Row],[Column2]],Education_Experience_Matrix!J:J,IF(Education_Experience_Matrix!M:M=Table134[[#This Row],[Column2]],Education_Experience_Matrix!L:L,IF(Education_Experience_Matrix!O:O=Table134[[#This Row],[Column2]],Education_Experience_Matrix!N:N,IF(Education_Experience_Matrix!Q:Q=Table134[[#This Row],[Column2]],Education_Experience_Matrix!P:P,IF(Education_Experience_Matrix!S:S=Table134[[#This Row],[Column2]],Education_Experience_Matrix!R:R,IF(Education_Experience_Matrix!U:U=Table134[[#This Row],[Column2]],Education_Experience_Matrix!T:T,IF(Education_Experience_Matrix!W:W=Table134[[#This Row],[Column2]],Education_Experience_Matrix!V:V,IF(Education_Experience_Matrix!Y:Y=Table134[[#This Row],[Column2]],Education_Experience_Matrix!X:X,IF(Education_Experience_Matrix!AA:AA=Table134[[#This Row],[Column2]],Education_Experience_Matrix!Z:Z,IF(Education_Experience_Matrix!AC:AC=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AB:AB,IF(Education_Experience_Matrix!AE:AE=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AD:AD,IF(Education_Experience_Matrix!AG:AG=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AF:AF,IF(Education_Experience_Matrix!AI:AI=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AH:AH,IF(Education_Experience_Matrix!AK:AK=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AJ:AJ,IF(Education_Experience_Matrix!AM:AM=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AL:AL,IF(Education_Experience_Matrix!AO:AO=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AN:AN,IF(Education_Experience_Matrix!AQ:AQ=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AP:AP))))))))))))))))))))

  7. Hi,

    I am trying to make a status condition. Below is are the cells that I want to have a status with. I have 2 columns, Case Status and Bug Status

    Sheet1
    cell# Case Status Bug Status
    G8 Passed
    G9 Failed Fixed
    G10 Failed Pending
    G11 New Bug
    G12 New Bug Fixed

    On the other (Sheet2) I want to know the general status of sheet1, if it is still "Open" or "Closed". My parameters would be, all "Failed" and "New Bug" case status should a corresponding "Fixed" status under Bug status column inorder to have a "Closed" status under sheet2 general status.

    Any help will greatly appreciated. Thank you very much.

    -Jade

    • I've solved the problem using this statement:

      =IF(COUNTIFS(G8:G12,"=Failed",H8:H12,"Fixed")+COUNTIFS(G8:G12,"=New Bug",H8:H12,"Fixed"),"Open","Closed")

  8. I am working on the calculations of moon on excel,,I have download spreadsheets but they use macros,,,can you plz tell me that how can I extract real formulae from the codes???

  9. I need to find the exact data for using index and indirect formula

    A 50
    a 40
    B 30
    b 20

    Using Index and match funtion could you help me

    =MATCH(F$39,INDIRECT("'"&$A$1&"'!A"&F$41&":IV"&F$41&""),0)
    getting Error (#Name?)

    =INDEX(INDIRECT("'"&$A$1&"'!A"&$F$41&":Z"&$F$42&""),MATCH(F$39,TRUE,EXACT(INDIRECT("'"&$A$1&"'!"&F$41&":IV"&F$41&""),0)))
    Ref error

  10. It is exactly working in the same way I want.

    I am really sorry for too many basic questions...I am just trying to learn excel.

    with the above formula, I am getting the output in the way I want..but need a small extension to it.

    Say for example I have this formula in “AA” for SL (sick leave) and “AB” for VL (Vacation leave) “AC” for CF (Compoff)

    Now as per your formula when I give SL it is getting reduced but if I give VL it is not working.

    Also if I enter CF in the default 0 value it should increase the count, for example if enter CF anywhere from A1 to Z1 the count should get increased in “AC”

    • Subbu,

      I am sorry, not sure that I can follow you. If you can post this question on our forum and attach a sample workbook for better understanding, our support team will try to help.

  11. =IF(OR(A1="A"), 20-COUNTA(A1:F1),"20") this worked well for a single cell.

    Now I want to use the same formula reflecting on multiple cells like:

    Targeted cells that I want the result is in from A1 to Z1 and I applied my formula on AA cell

    Now whenever I enter a value “A” in the cells anywhere from A1 – Z1 the count in the AA cell should get reduced.

    AA = contains my actual leaves per year
    A1-A20 contains the team member names
    A1 to Z1 cells contains the number of days in a month

    If anybody is on leave on that particular day, the count in the AA should get reduced.

    • Got it :)

      In this case, you need a different formula, like this:

      =IF(COUNTIF(A1:Z1, "A")>0, 20-COUNTA(A1:F1), 20)

      The COUNTIF function counts the number of "A" in cells A1:Z1, and if it's greater than 0 (i.e. if there is at least one "A"), the count will be reduced.

  12. It is working for one cell that is A1. What if i want to apply the same formula from A1:Z1.

    I tried but it is giving an error

    please help

    • Do you want to copy the formula to other cells? If so, you will need to use absolute or mixed cell references depending on your data structure. To be able to advise something more concrete, I need to know the exact formula you are using and what exactly result you expect it to return in other cells.

  13. sorry for coming back again...

    But in this formula no matter wheather i give "A" or "B" the count is getting reduced.

    =IF(OR(A1="A", A1="B"), 20-COUNTA(B1:F1), "A")

    i want the count to be reduced only if i give "A"...please help...

    • Subbu,

      Yes, the formula reduces the count if A1 is filled with either"A" or "B" because that was the requirement: "if the empty cell is filled with “A” or “B” it should reduce the count".

      If you want the count to be reduced only if you input "A" in A1, then remove the OR statement from the logical test:

      =IF(A1="A", 20-COUNTA(B1:F1), "")

  14. It worked well thanks again

  15. That was a very speedy reply.. Your are great…appreciate it and thanks you very much for your help.

  16. it is a very small doubt..

    I am using this formula as below and want to add an “IF clause” to it saying that only if the empty cell is filled with “A” or “B” it should reduce the count...how can I get it...please help

    =20-COUNTA(C5:AF5)

    ignore the above post.....

    • Assuming that the empty cell is A1, you can use the following formula:

      =IF(OR(A1="A", A1="B"), 20-COUNTA(C5:AF5), "")

      Instead of "" you can supply any value you want the formula to return if A1 is neither A nor B.

  17. it is a very small doubt..

    I am using this formula and want to add an IF to it saying that only if the empty cell is filled with A it should reduce the count...how can i do it...

    • Hi Subbu,

      It could be something like this:

      =IF(A1="A", value_if_true, value_if_false)

  18. Hi

    Just wondering whether the following can be shortened
    i.e. if B58 shows either TX or SR it will generate
    6% in cell C58

    =IF(B58="TX",6%,IF(B58="SR",6%,))

    Thanks

    • Hi!

      You can use the OR statement like this:

      =IF(OR(B58="TX", B58="SR"), 6%,)

      • Thank you so much

  19. Hi

    Need an help in writing formula for attendance time tracking

    If the In Time is 09:00 and Out time is 17:30 - PRESENT
    If the In Time is 10:30 and Out time is 17:30 - HALF DAY
    If the In Time is 09:00 and Out Time is 15:00 - HALF DAY
    If the In Time is 09:10 - LATE
    If absent - ABSENT
    If Sick Leave - SL
    IF Comp Off- COFF

  20. I am trying to devise a spreadsheet to calculate wages using IF formula - can you help? If I enter house worked in column A. I want to calculate daily pay based on first 8 hours at one rate and anything above that at a higher rate. Thanks

  21. Can get help writing a formula that:

    if the number in cell F8 ends in .5 then G8 is $1.00
    if the number in cell F8 ends in .25 then G8 is $2.00
    if the number in cell f8 ends in 1 then G8 is 0
    if the cell f8 is left blank then G8 is 0

    This is an order form. For further clarification: if a customer orders a whole box (1) there is no charge. If they order a 1/2 box (.5) there is a split free of $1.00 for us to split the box. If they order a 1/4 box (.25), there is a $2.00 split fee. I need that cell to recognize that an order of 1.5 will need the $1.00 charge. Does that make sense? Help!

  22. Hi, I was wondering if you could help me on this.

    example:

    If A1 and B1 both equal 2, then say yes, elso say no.

    Thanks :)

    • Nvm, I got it, I was reading your blog and found it, thanks :)

  23. I have also tried the following formula. But no matter if I get a number less than 60 I am still getting a "D" when i should be getting a "C","B","A".
    =IF(E20>100,"F",IF(E20<100,"D",IF(E20<80,"C",IF(E20<60,"B",IF(E20<40,"A")))))

    • Hi Jessica,

      Because you put E20<100 before others "less than" conditions, Excel checks it first and if the condition is met it simply does not evaluate any other logical tests. And because any value that is less than 40 is definitely less than 100, your formula returns "D".

      As soon as you change the order of IF's, everything works as it should :)

      =IF(E20<40,"A",IF(E20<60,"B",IF(E20<80,"C",IF(E20<100,"D", "F"))))

  24. Please help :)
    I am trying to do multiple IF functions so i can get different grades according to a score. Can you please check my formula and tell me what am I doing wrong ? I have tried using the AND and the OR but it is not working. I think it could be the comas and parenthesis but i am not sure.

    Thank you,

    =IF(SUM(E11,E20)>100, "F",IF(OR(E20<80, "D"),IF(OR(E20<60,"C"),IF(OR(E20<40,"B"),IF( OR(E20<20, "A"))))))

  25. Hi

    I need to combine the following two IF Statements into one, I am thinking of using the OR function along it

    Function 1
    =IF(Q4="E",IF(T4>=12%,IF(T4<=19.99%,"0.5%",IF(T4<=24.99%,"1%",IF(T430%,"3%",0)))),"0%"))

    Function 2
    =IF(Q4="N", IF(T4>=10%,IF(T4<=19.99%,"1.25%",IF(T4<=24.99%,"1.75%",IF(T430%,"3.75%",0)))),"0%"))

    I tried using the IF OR function but got stuck.

    Any help would be appreciated combining the two functions into one

    • Some help would be appreciated on this

  26. Please solve it

    I want to put up two conditions.

    Value - 1 :225
    Value - 2 :0.075%
    if ((225*0.075%=0.05,"Actual Value")).

    if the product of the both value is greater then 0.05 then i want the actual value means the product of the both value.
    Is it possible and if possible please explain.

    • Hi Ankit,

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

      =IF(A1*B1>0.05, A1*B1, "")

      If the product of values in cells A1 and B1 is greater than 0.05, it returns that product, an empty string otherwise.

  27. Hi Svetlana,
    I need help with this formula:
    Quantity B1=500
    Frequency B2=4
    Cost B3=1000
    Shipment# B5="Shipment# 1"

    COGS B4=(B1*B3)/(12/B2) and then spread that B4 into the next 3 columns because (12/B2=3)

    The Quantity, Frequency, Cost will change so i need to write a formula for COGS B4 to capture this change. For example:
    Quantity B1=600
    Frequency B2=3
    Cost B3=500

    COGS B4=(B1*B3)/(12/B2) and then spread that B4 into the next 4 columns because (12/B2=4)

    I also need to write another formula to spread these info to the next columns. For example, if Frequency B2=3, then 4 months later in column F1=600, F2=3, F3=500,F5="Shipment# 2" and so on..

    Thank you for your help!

  28. Hi Svetlana, you're right the IFs are driving me crazy :s
    I can't seem to figure out what I'm doing wrong.

    I have a sheet that contains owner name, product name, number of products.
    I want to create a formula where I calculate the amount of products per owner.

    For example
    A: Pete - B: Apples - C: 40
    A: Steve - B: Lemons - C: 20
    A: Pete - B Apples - C: 30

    Pete is listed under the product "Apple" twice and in total he bought 70 apples. How do I calculate that number?

    I have tried the following:
    =IF((AND(A:A="Pete",B:B="Apples")),C:C)

    What I'm trying to say here is IF column A contains the name Pete AND column B contains the product Apples, THAN all the values in column C that contain the conditions from column A and B should be added up.

    I think I'm close with my formula but I can't seem to figure it out. Could you help me and let me know what I'm doing wrong?

    PS: sorry if I'm posting this multiple times but something seems to be going wrong when I press the send button.

    • Hi Rachel,

      Excel has special functions SUMIF and SUMIFS to add up values based on one or several conditions, respectively. And they make things really easy :)

      Since you have 2 criteria, the SUMIFS function is the right choice:

      =SUMIFS(C2:C100, A2:A100, "pete", B2:B100, "apples")

      You can find the detailed explanation of the SUMIFS arguments in this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/

      PS No worries about the duplicate post, I've deleted it.

  29. Hey Svetlana,
    Can you please tell me if we can apply condition if formula in multiple cell at one time.

  30. THANKS

  31. Please advice the formula for the following condition
    1 ) If staff has taken leaves between 0 – 7 days will get 3 day paid leave rest all deduction
    2) If staff has taken leaves between 8 – 14 days will get 2 day paid leave rest all deduction
    3) If staff has taken leaves between 15 – 20 days will get 1 day paid leave rest all deduction
    4) more than 20 days 0 paid leave

  32. Hi Svetlana - i have read through all the posts as best i can. I'm sorry if you've answered this already:

    I have two cells (A1 and B1) that have numbers in them. Sometimes A1 is blank. When there are numbers in both, i want C1 to bring them together with a dash.

    For instance, if A1=3 and B1=4 then C1 should say "3-4".

    If A1=(blank) and B1=4 then C1 should say "4".

    Here is the forumula i'm trying to use:

    =If(A1>0,A1&'-'&B1,B1)

    However, excel does not recognize cell B1 in the formula.

    Thanks!

    • Sorry - i was using the wrong "quotes" in the formula.

      • Spot on :) Your formula with double quotes works perfectly!

  33. I am trying to produce a working formula for a sum-if-or statement and can't come up with something that works.
    I have numbers to sum in columns E11-E47. If Column G11-G47 shows a "T" or "S", I would like those numbers correlating in E11-E47 to sum, but if G11-G47 shows an "R" then the value in G11-G47 should result a 0.

    Make sense?

    I have =SUM(IF(OR(G11:G47="S",G11:G47="T"),E11:E47,0)) but it's not working properly and summing all of the values in E11-E47.

    Thanks!

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

  35. Hello, I am trying to categorized a list of text values and created the following formula but it is not working, the search and if formulas can not be nested?

    =IF(SEARCH("License",$A11,1)>1,"License", IF(SEARCH("SnS",$A11,1)>1,"SnS", IF(SEARCH("Consulting",$A11,1)>1, "PSO", IF(SEARCH("Training",$A11,1)>1, "PSO","CHECK"))))

    Thanks

  36. I am trying to come up with a formula for the following,
    If H3, I3, M3 or N3>1,"Pass","Fails"
    this works for one cell or any array but not different cells. New to Excel formulas so any help would be appreciated. Thanks!

    • Hi Michelle,

      Just use the following OR statement:

      =IF(OR(H3>1, I3>1, M3>1, N3>1),"Pass","Fails")

      • When I write it like this it will show a pass for the column when all are above 1 but when the cell shows a 1 it will not prompt a Fail it only continues to prompt pass. What am I doing wrong?

        • Will it matter if I have other formulas populated in the cells that are in this formula?

          • Michelle,

            This should not really matter. If you can send your sample worksheet along with the expected results to our support team (support@ablebits.com) I think we will figure it out faster.

            • telle me formula

              Excel - If "pass","fail" - How do I set the formula

            • Hi! Can u help me please..
              I would like to enter a value in any cell from a1 to d1 then it will display the same value in cell e1. What is the formula i should write? Thanks

  37. hey, i'm trying to come up with a function to display Yes if value is more than 22.41 for female athlete OR if it is more than 22.47 for male athlete: if not it should display No.

    • Hi Dee,

      Asumming that you have values in column A and male/female in column B, you can use the following formula:

      =IF(OR(AND(A2>22.41, B2="female"), AND(A2>22.47, B2="male")),"yes", "no")

  38. Hi,
    Am trying to do the following:
    If G16 >=1 and G16=1, G16<=3),OR(C4="NA")),"", "Low") but it does not work.

    Any help much appreciated.

    • Hi Stuart,

      You probably mean:
      =IF(OR(AND(G16 >=1, G16<=3), C4="NA"), "", "Low")

  39. Hi! Trying to do the following:

    If G30 is greater than or equal to 100 but less than or equal to 106.99, multiply G25 by 2
    If G30 is greater than or equal to 107 but less than or equal to 118.99, multiple G25 by 3.5
    If G30 is greater than or equal to 119, multiple G25 by 5

    Any help is appreciated!

    • Hi!

      Here you are:
      =IF(G30>=119, G25*5, IF(G30>=107, G25*3.5, IF(G30>=100, G25*2, "")))

  40. first of all ,thanks in advance for the people whom create these very nice tutorials.I wanted to do math operation on the results of nested IFs.how could it possible with one formula??? my formula is as this:
    =IF(HOUR(D26)>0; HOUR(D26)*45357; IF(MINUTE(D26)>0; MINUTE(D26)*755.95; IF(SECOND(D26)>0; SECOND(D26)*12.6;0)))
    ---------I wana add a statement to above formula in order to sum the true values , it calculate the first but ignores others!!!! I wanna sum hour+minute+second

  41. I'm trying to return different values, based on multiple ranges of cells.
    e.g.
    if any cell in the range a1:a4 contains a number value, then true=1,false=""
    and if any cell in the range a5:a9 contains a number value, then true=2,false=""
    and if any cell in the range a10:a20 contains a number value, then true=3,false=""
    etc.

    Have tried several formulas and keep getting error message.

    Are you able to help me with this?

  42. Hi! I am working in excel and need help with an IF formula for the following statements:

    If the cell value is =8 then 30 points are awarded
    If the cell value is >=10 then 60 points are awarded
    If the cell value is >16 then 30 points are awarded
    If the cell value is >17.5 then 0 points are awarded

    This IF statement is meant to calculate whether our wholesaler is stocking the appropriate # of days of inventory. If they stock 8-10 days of inventory they are awarded the maximum points. They are penalized for dipping below or above the targeted range per the schedule above.

    Any help you can provide would be greatly appreciated!

    • Hi Kristen,

      Here you go:
      =IF(A2>17.5, 0, IF(A2>16, 30, IF(A2>=10, 60, IF(A2>=8, 30, ""))))

      Please note that I use >=8 in the last logical test, because =8 would return nothing for 9 days. Anyway, you can always adjust the conditions the way you want :)

      • Thank you!!!

      • Good Day,
        Can you kindly help me with the following.
        I want to use a function for the following.
        There are codes which starts at 1000-000 till 7400-000, what I want to do is that if a certain code is typed in, there must automatically display 15 or 0 in another column in which I'm typing in the function, 15 if true 0 if false.
        Kind Regards,

  43. =IF(F14=D14,C14,IF(F14=0.7,C15, IF(F14=0.72, C16, IF(F14=0.74,C17, IF(F14=0.76, C18, IF(F14=0.78, C19, IF(F14=0.8, C20, IF(F14=0.7, C21, IF(F14=0.84, C22, IF(F14=0.86, C23,IF(F14=0.88, C24, IF(F14=0.9 ,C25, 0)))))))))))).. I TYPE THIS FORMUALLA MANUAL. IS THERE ANY OPTIONS TO DO SHRT WITHOUT TYPING THE NEXT CONSECUTIVE CELLS.

  44. HI!
    I am working on a spreadsheet to keep track of real estate commissions and need a formula to help me. I thought it was the if function, but it does not seem to be working.

    I need it to do:

    If C3="yes" and D3>500000 then K3= H3*.5 or if C3="yes" and D3<500000 then K3= H3*4

    Any help would be great!

    Thanks,
    Mel

    • Hi Mel,

      Try this one:

      =IF(AND(C3="yes", D3>500000), H3*0.5, IF(AND(C3="yes", D3<500000), H3*4, ""))

      • Hi Mam.. i have a doubt in excel
        If column A equals to column c then substract column b and colum d...
        Pls tell me how can i do in excel

  45. Hi,
    I'm hoping someone can help me.
    I have a spreadsheet that I enter X into a cell when a student has completed a task. If they completed four tasks (four X's) the are finished. I'm trying to do a formula that will count the X's for a specific row and if it is 4 or more it will put a "C" in another cell or better yet it will put a "C" and also colour the cell. Is this possible??
    Thanks!!

  46. Hi, I have a question.
    I want a formula to the following:

    If column S2=N return the sum of Q2*G2 or if S2=Y return the sum of R2*G2

    • Hi SandeeBee,

      Here you go:

      =IF(S2="N", Q2+G2, IF(S2="Y", R2+G2, ""))

      • Thanks you Sandeebee for asking this question.
        And thank you very much Svetlana for your response.
        You both have really help me solved an issue i have been dealing with here.
        Thank you.

  47. Hi Erin,

    Try the following formula for English scores:
    =IF(AND(C2>D2,C2>39),"pass",IF(D2>39,"pass","fail"))

    And if it works as you expected, make a similar one for Math.

    • Svetlana,
      That's great! Thank you! Is there a way, perhaps, to join both the english and the math? for example, if english is a passing grade and math is a passing grade, they pass the course. if english is a passing grade but math is a failing grade, they fail the course...? If both grades are passing, they pass, but if one or both grades are failing, they fail the course. Is there a way to put this into one formula, or should I calculate the english separately and the math separately and then create a formula from those?

      • Erin,

        Of course, there is a way:

        =IF(AND(OR(C2>39, D2>39), OR(E2>37, F2>37)), "pass","fail")

        The formula works with the following logic:
        If either column C (english) or D (english retake) is above 39 AND either E (math) or F (math retake) is above 37, it returns "pass", "fail" otherwise.

        My first suggestion was excessively complicated in fact, and there is a more elegant solution for individual subjects, e.g. =IF(OR(C2>39, D2>39),"pass", "fail")

        • Thank you so very much! This is MUCH appreciated!

  48. Thanks for the awesome website!
    I am trying to compile some data and was wondering if there was a way to do this?
    I have a list of test scores in column C (english) and E (math). In column D english retake) and E (math retake), I have a list of retake scores for those tests. Some of those scores will be blank because some students did not retake a test.
    I need to see who passes and who fails based on taking the highest score for each section. Basically, a student passes if english is above 39 and math is above 37. If one is less, they fail. I can figure a formula for this. However, I need to take into consideration the retake scores. If a retake score is higher than the original score, I need excel to take that number. If the retake score is lower or blank, that number can be ignored. Is this possible?
    Thanks so much!

  49. Thank you Svetlana, you are truly amazing!

  50. HI, I could really use some help making an if/then equation:

    -If Z2 equals 0, then "4"
    -If Z2 equals 1, and Y2 > 0, then "3"
    -If Z2 equals 1, and Y2 = 0, and U2 > 0, then "2"
    -If Z2 equals 1, and Y2 = 0, and U2 = 0, then "1".

    Thanks!

    • Hi Courtney,

      You can try the following nested IFs:

      =IF(Z2=0, 4, IF(AND(Z2=1, Y2>0), 3, IF(AND(Z2=1, Y2=0, U2>0), 2, IF(AND(Z2=1, Y2=0, U2=0), 1, ""))))

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