March 18, 2022

I received a lot of questions on how to use IF function with 3 conditions, so I've decided to write an article on this topic.

The IF examples described in this article assume that you have a basic understanding of how the IF function works. All examples from this article work in Excel for Microsoft 365 or Excel 2019, 2016, 2013, 2010, and 2007.

If you are unfamiliar with the IF function, then I strongly recommend reading my article on Excel IF function first. It's a step-by-step guide, and it includes a lot of useful examples. It also shows the basics of writing an Excel IF statement with multiple conditions, but it's not as detailed as this guide. Make sure you also download the exercise file.

As a data analyst, you need to be able to evaluate multiple conditions at the same time and perform an action or display certain values when the logical tests are TRUE. This means that you will need to learn how to write more complex formulas, which sooner or later will include multiple IF statements in Excel, nested one inside the other.

Let's take a look at how to write a simple IF function with 3 logical tests.

The first example uses an **IF statement with three OR conditions**. We will use an IF formula which sets the *Finance *division name if the department is *Accounting*, *Financial Reporting*, or *Planning & Budgeting*.

The IF statement from cell E31 is:`=IF(OR(D31="Accounting", D31="Financial Reporting", D31="Planning & Budgeting"), "Finance", "Other")`

This IF formula works by checking three OR conditions:

- Is the data from the cell
`D31`

equal to`Accounting`

? In our case, the answer is no, and the formula continues and evaluates the second condition. - Is the text from the cell
`D31`

equal to`Financial Reporting`

? The answer is still no, and the formula continues and evaluates the third condition. - Is the text from the cell
`D31`

equal to`Planning & Reporting`

? The answer is yes, our IF function returns TRUE, and displays the word Finance in cell E31.

Next, we focus our attention on an example that uses an **IF statement with three AND conditions**.

Our table shows exam scores for three exams. If the student received a score of at least 70 for all three exams, then we will return Pass. Otherwise, we will display Fail.

The IF statement from cell H53 is:`=IF(AND(E53>=70, F53>=70, G53>=70), "Pass", "Fail")`

This IF formula works by checking all three AND conditions:

- Is the score for Exam 1
`higher than or equal to 70`

? In our case, the answer is yes, and the formula continues and evaluates the second condition. - Is the score for Exam 2
`higher than or equal to 70`

? Well, yes it is. Now the formula moves to the third condition. - Is the score for Exam 3 higher than or equal to 70? Yes, it is. Since all three conditions are met, the IF statement is TRUE and returns the word Pass in cell H53.

The final section of this article is focused on how to write an Excel IF statement with multiple conditions, and it includes two examples:

- multiple nested IF statements
- formula with a mix of AND, OR, and NOT conditions

In this example, I have calculated the grade of the students based on their scores using a **formula with 4 nested IF functions**.

`=IF(E107<60, "F", IF(E107<70, "D", IF(E107<80, "C", IF(E107<90, "B" ,"A"))))`

Note: In this case, the order of the conditions influences the result of your formula. When your conditions overlap, Excel will return the [value_if_true] argument from the first IF statement that is TRUE and ignores the rest of the values. If you want your formula to work properly, always pay attention to the logical flow and the order of your nested IF functions.

If you have to write an IF statement with 3 outcomes, then you only need to use one nested IF function. The first IF statement will handle the first outcome, while the second one will return the second and the third possible outcomes.

Note: If you have Office 365 installed, then you can also use the new IFS function. You can read more about IFS on Microsoft’s website.

I have saved the best for last. This example is the most advanced from this article, as it involves an IF statement with several other logical functions.

In the exercise file, I have included a list of orders. Each row includes the order date, the order value, the product category, and the free shipping flag. We want to flag orders as eligible if the following cumulative logical conditions are met:

- the order was placed during
`2020`

- the order includes products from only two categories:
`PC`

or`Laptop`

- the order was
`not`

flagged as`Free shipping`

The formula I've used for cell H80 is shown below:

`=IF(AND(D80>=DATE(2020,1,1), D80<=DATE(2020,12,31), OR(F80="PC", F80="Laptop"), NOT(G80="Yes")), "Eligible", "Not eligible") `

Here's how this works:

`AND`

makes sure that all the logical conditions need to be met to flag the order as Eligible. If any of them is FALSE, then our entire IF statement will return the [value_if_false] argument.`D80>=DATE(2020,1,1)`

and`D80<=DATE(2020,12,31)`

check if the order was placed between January 1st and December 31st, 2020.`OR`

is used to check whether the product category is`PC`

or`Laptop`

.- Finally,
`NOT`

is used to check if the Free shipping flag is different from`Yes`

.

And there you have it. I hope that after reading this guide, you have a much better understanding of using IF function with 3 logical tests (or any number actually). While it may seem intimidating at first, I guarantee that if you write an IF formula with multiple criteria daily, your productivity will eventually skyrocket.

This is why, if you have any questions on how to use IF function with 3 conditions, please leave a comment below and I will do my best to help you out. I reply to every comment or email that I receive.

About me

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View coursesExcel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2022 ExcelExplained.com

How to create

If cell is 0% show 0% else get average of range of cells

Hi,

Try this: =IF(A1=0%, 0%, AVERAGE(A2:A4))

Just replace the cells with your own references and you should be good to go.

I'm trying to write a formula where a cell is populated with one of three different equations based on what descriptor is entered into another cell, but I keep getting a return of "False"; this is my formula:

=IF(E13="Hall Of Fame", G13*0.25, IF(E13="MVP", G13*0.5, IF(E13="All-Star", G13*0.1)))

Hi Greg,

Your formula doesn't have a default value_if_false argument, so you will get FALSE whenever you insert something in cell E13 that is not covered by the three text strings. You can overcome this by using a custom message or formula or blank ("") as the default value_if_false argument, like this:

=IF(E13="Hall Of Fame", G13*0.25, IF(E13="MVP", G13*0.5, IF(E13="All-Star", G13*0.1, "Custom message or formula")))

Other than that, your formula worked fine for me when using the text strings "Hall of Fame," "MVP," and "All-Star."

I am developing a spreadsheet where if a name is entered into either A3, A4, or A5, a different outcome will show for each of the cells. For example if I enter the name Suzie in A3, Smith will be the outcome, if I enter Suzie in A4, Jones will be the outcome, if I enter Suzie in a5, Brown will be the outcome. my Attempt: ("Suzie" is actually on a different sheet)

=IF((or('Class schedule'!A38="Suzie", 'class schedule'!a39="suzie", 'class schedule'!a40="suzie")), "Smith","Jones","Brown")

Apparently I've entered too many functions.

Hi Beth,

The reason you are getting this error is that the IF function takes only three arguments, but you have provided four. If you haven't done so already, I strongly recommend reading this article about how to use the IF function, as a solid foundation will help you learn to write more complex formulas.

Now, if I got your question right, the formula that you want is this:

=IF('Class schedule'!A3="Suzie", "Smith", IF('Class schedule'!A4="Suzie", "Jones", IF('Class schedule'!A5="Suzie", "Brown", "")))

Since your formula contradicts the question, I've created a formula that I think matches your request. If it doesn't, please leave a reply with a bit more details on how you want this to work.

Hi Radu,

Thank you for sharing this information.

I have 4 periods within a year for harvesting and i want to check on which days within a calendar year that there will be days of harvesting. So I have used the IF function for the period say 16 Jan 2022 - 3 Mar 2022 and it works successfully, but when i try to add other periods to the equation say add a second period say 8 April to 2 May the formula will not work.my successful equation is IF(AND(A14 >= B5, A14 >= C5), "YES", "")

How do i add another period date to the equation, it returns an value error or incorrect YES when I've tried.

Thank you for your help

Hi KD,

I think you should also include the OR function in your formula. Please try this and let me know if it works. Just insert the 4 periods in pairs like B5-C5, B6-C6.

=IF(OR(AND(A14 >= B5, A14 = B6, A14 = B7, A14 = B8, A14

Hi Radu,

Thanks for sharing and answering most of my questions. However, I've been struggling with some of my works using google sheet.

I'm working for our KPI and wanted to use IF function. So here is my current formula

=IF(F6 > 500, "70%", IF(F6 > 750, "75%", IF(F6 > 1000, "85%", IF(F6 > 1300, "90", IF(F6 > 1500, "95%"))))))

But still, it won't follow or show the correct result when I use them. I'm trying to use it for our KPI..

Example:

The average for January is 780, so I am expecting it to be 75% but the sheet will show 70%. Please help.

Thanks in advance!

Hi Lanie,

This is a very common error with IF statements and a lot of people struggle to understand this on their own because the formula appears to be correct from a logical point of view. The problem lies in the way Excel reads an IF statement.

In your example, you first check if 780 is greater than 500 which evaluates as TRUE and 70% is returned by the formula. This is where the formula stops. It doesn't check further and only returns the first case that is true.

And this makes sense if you think about it. 780 is greater than 750, but it's also greater than 500. To solve this, you need to enter the conditions in reverse order and check first if the value in cell F6 is greater than 1500, then 1300, and so on.

Let me know if you've managed to make it work.

Radu

Hello! I am trying to formulate a cell that if the cell does not yet have data entered, the formula should leave the cell blank, however if the cell does have data, I have two other True/False responses. Is there a formula for that? Right now when the referenced cell is blank, the formula indicates "Yes", but we want it to be blank instead. I tried the OR functions, but I kept getting an error message and I'm sure I'm doing something wrong. Thanks!

=IF(P77 < 0.0251, "Yes", "No")

Hi,

Yes, when the cell is blank it shows Yes because a blank cell is set to be equal to zero, which in your formula is lower than 0.0251. To display a result only when the cell is not blank you need to use the ISBLANK function and integrate it within the IF statement like this:

=IF(ISBLANK(P77), "", IF(P77

=IF(AND(L4="#N/A", P4="#N/A", T4="#N/A"), "#N/A", "In Analysis") This formula returns "#N/A" if one of the cells (L4, P4 or T4) is #N/A. I only want to put #N/A if all three conditions are happening at same time.

Hi Jose,

#N/A is a reserved error code which means that Excel will have trouble seeing it as text. The workaround is to use the ISNA function to check if the text in a cell is the error code #N/A like this:

=IF(AND(ISNA(L4), ISNA(P4), ISNA(T4)), "#N/A", "In Analysis")

Another option would be to use the text N/A instead of #N/A which would also solve your issue:

=IF(AND(L4="N/A", P4="N/A", T4="N/A"), "N/A", "In Analysis")

I hope this helps.

Hi Radu,

I'm trying to write a formula with three outcomes based on the value in a cell:

=IF(AND(H131 > 25, H131=75), "Right", IF(H131 < =25), "Left"))

I keep getting an error message and can't figure out my mistake. Thanks!

Hi Brian,

You have an extra parenthesis in the second IF statement. Also, try to use the same double quotation marks, as this can also trigger errors. Try this formula:

=IF(AND(H131 > 25, H131 = 75), "Right", IF(H131

I'm trying to do a formula where if F2 is greater than E2 it equals a 1 and if not it equals a 0. My options in E2 and F2 are BASE, SILVER, GOLD, PLATINUM, DIAMOND.

So basically if E2=SILVER AND F2=GOLD IT WOULD BE A 1.

This is the formula I came up with:

=IF(AND(E2="SILVER", F2="GOLD", 1, IF(AND(E2="BASE", F2="SILVER", 1, IF(AND(E2="GOLD", F2="PLATINUM", 1, IF(AND(E2="PLATINUM", F2="DIAMOND", 1, 0))))))))

Hi Jenny,

This requires a more complicated formula and I would suggest using a different approach. This way you can easily change or add more values in the future. I would create a separate table and next to each name I would put a number. For instance, since BASE is the lowest I would assign 1 and increment by one until DIAMOND which has 5.

Let's assume I have those on columns A and B from rows 1 to 5 like this:

A1: BASE / B1: 1

A2: SILVER / B2: 2

A3: GOLD / B3: 3

A4: PLATINUM / B4: 4

A5: DIAMOND / B5: 5

Then, I would just compare the numbers using a simple IF statement combined with VLOOKUP, like this:

=IF(VLOOKUP(F2, A1:B5, 2, FALSE) > VLOOKUP(E2, A1:B5, 2, FALSE), 1, 0)

Let me know if you are able to make it work. If not, we can try a formula using multiple nested IF statements, but it's messy and hard to edit later on.

sorry, the past comment doesn't show well, i will rewrite

I am working on work progress timeline.

I have in column A the task completion percentage (from 0% to 100%)

And in column B a date (task end date)

I need a formula in column C to:

if A=100% and B>TODAY() then "COMPLETE"

if A<100% and B<TODAY() then "ONGOING"

if A=100% and B<TODAY() then "COMPLETE"

if ATODAY() then "OVERDUE"

Hi Joe,

I think that the formula that matches your requirements is this:

=IF(AND(A1 = TODAY()), "ONGOING", "COMPLETE"))

Let me know if it needs any adjustments.

All the best,

Radu

Hi,

I am working on work progress timeline.

I have in column A the task completion percentage (from 0% to 100%)

And in column B a date (task end date)

I need a formula in column C to:

1- if ATODAY() then "OVERDUE"

2- if A=100% and B>TODAY() then "COMPLETE"

3- if A<100% and B<TODAY() then "ONGOING"

4- if A=100% and B<TODAY() then "COMPLETE"

Thanks,

Hi this is fascinating, thank you so much! Can I also get different results based on different conditions?

Here's my example:

If Hire Date (column D) is >9/30/2021, "Not Eligible due to Hire Date"

and/or: If Last Increase date (column E) >9/30/2021 AND the Reason (Column F) is "Promotion" then "Not Eligible due to Promotion" else "Eligible"

I've been trying this: =IF(AND(D16< DATE(2021,10,1), F16="PROMOTION")*OR(D16< DATE(2021,10,1)), "Eligible", "Not Eligible") but i would really love the extra information from the disqualifier of the reason for Ineligibility. Thanks in Advance!!

Hi Jane,

Sorry for the late reply. I was away this weekend. I think the formula you are looking for is:

=IF(D1> DATE(2021, 9, 30), "Not Eligible due to Hire Date", IF(AND(E1> DATE(2021, 9, 30), F1="Promotion"), "Not Eligible due to Promotion", "Eligible"))

Let me know if this works as intended.

=IF(B147000, "IF(SUM(7000*Q3)"))

Hello, thank you for the information you've provided---my formula in google sheets isn't working,

what I'm trying to ask is IF cell b14 is less than then i want the outcome of b14*q3 IF cell b14 is greater than 7000 then i only want the sum of 7000*q3

b14= 9744

q3= .006

thank you so much for your help.

BLESSINGS

Hi Priscilla,

The formula you need is:

=IF(B14<7000, B14*Q3, 7000*Q3)

All the best,

Radu

I have 4 columns; I need to find the following

Column A

A2 If response is either Yellow, Gold or Orange

Column B

B2 If text contains “Shirt”

Column C

C2 If text does NOT contain “Apple”

Column D

D2 Text is equal to YES

I would like to know if they are a MATCH, NOT a Match

Please let me know if you need any more information, thanks

Hi,

If I got your request right, I thinkg the formula you are looking for is:

=IF(AND(OR(A2= "Yellow", A2= "Gold", A2= "Orange"), ISNUMBER(SEARCH("Shirt", B2))=TRUE, ISNUMBER(SEARCH("Apple", C2))=FALSE, D2="Yes"), "Match", "Not a match")

Let me know if this is this works as intended.

Radu

I have a column with 5 choices, HA, GR, KA, BU, or CA...I need an if statement that assigns a different outcome to each of those choices...this is the code I wrote but it doesn't work...I tried to work it off your example above with the letter grades for the numerical grades, with the BAT being the statement for BU since it is not designated in the statements, so maybe that's the issue....

=IF(K2=”GR”, "DOG", IF(K2=”CA”, "CAT", IF(K2=”HA”, "HAMSTER", IF(K2=”KA”, "KANGAROO", "BAT"))))

Hi Alison,

Sorry for the late reply. Your formula was spot on, except for the double-quotes. If you pay close attention, you can see that you've mixed two types of double quotation marks:

”” is wrong

"" is good

Below is the corrected formula:

=IF(K2="GR", "DOG", IF(K2="CA", "CAT", IF(K2="HA", "HAMSTER", IF(K2="KA", "KANGAROO", "BAT"))))

Let me know if that works.

Radu

Hi Radu, I pasted the table but looks like it got posted without the table

(A) If : High, Med, Low

(B) And If: High =4, Med=3, Low = 2 then "Late", else "ok"

(C) will be the output either late or ok

No worries, I got the idea from the first message. I think 🙂