Excel - Multiple Conditions in IF - Episode 2025

Microsoft Excel Tutorial: How to use multiple conditions in IF function.

Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing the use of multiple conditions in the IF function. This is a common challenge that many Excel users face, and we are here to provide you with the best solutions.

Before we get started, make sure to check out the entire podcast series by clicking on the "i" on the top-right hand corner of the screen. This will take you to the playlist for all of our previous episodes, so you can catch up on any that you may have missed.

Let's begin with a simple scenario - the fictitious VP of Sales has announced that anyone with sales over $20,000 will receive a 2% bonus. This is where the IF function comes in, with its three parts: a logical test, what to do if the test is TRUE, and what to do if the test is FALSE. In this case, the logical test is whether the sales in cell B4 are greater than $20,000. If it is TRUE, the bonus will be calculated as 2% of the sales amount. If it is FALSE, no bonus will be given. By copying this formula down, we can see that only those with sales over $20,000 receive the bonus.

However, in most cases, bonus plans are not this simple. There are usually multiple rules and conditions to consider. In this scenario, we also need to check if the gross profit percentage is over 50%. This is where things can get tricky, and many users resort to nesting IF statements. But there is a better way - using the AND function. This allows us to check multiple conditions within one formula, making it shorter and easier to enter.

But wait, there's more! We also have other functions such as OR, NOT, NAND, NOR, and XOR. However, be careful when using the XOR function introduced in Excel 2013, as it may not give the results you expect. It is important to understand the logic behind these functions and how they work in order to use them effectively.

In conclusion, the IF function is a powerful tool in Excel, and by using the AND function, we can easily handle multiple conditions without resorting to nested IF statements. Make sure to check out our book for more useful tips and tricks, and stay tuned for our next episode where we will be discussing even more Excel techniques. Thank you for tuning in, and we'll see you next time on the MrExcel podcast!

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#excelformula
#excelformulasandfunctions
#microsoft365
#walkthrough

Table of Contents
(0:00) Problem Statment: Multiple Conditions in Excel IF function
(0:20) IF function with one condition
(1:02) IF function with 2 conditions - three solutions overview
(1:52) Nesting IF functions to handle multiple conditions
(2:36) Nesting more than 7 functions in Excel
(3:05) Using AND function inside IF function
(3:35) Using Boolean Math for multiple IF
(5:00) OR function in Excel
(5:10) NOT function for NAND or NOR
(5:35) XOR function in Excel has problems
(7:06) XOR function counts if odd number of TRUE
(7:21) Buy the book
(7:35) Recap from today
(7:55) Clicking Like really helps the algorithm

This video answers these common search terms:
Excel 2013 XOR function limitations
Excel nested IF function
Excel tutorial multiple conditions in IF
IF function logical test
IF function TRUE and FALSE results
Learn Excel from MrExcel podcast
NAND function in Excel
NOR function in Excel
Using AND function in Excel
Using NOT function in Excel
Using OR function in Excel
XOR function in Excel

[ywang]

So many videos for so many different situations on YouTube, but this is the one for my case, and I like your voice! THANKS!

[eoinconnolly]

That no function formula blew my mind! Thank you for sharing it. Whoever thought of it is a genius.

[GuardianApe]

You just answered a question I've spent weeks finding an answer to and I didn't need to ask.

[dselling]

I am having trouble with =if(or(cellA>cellB, cellA<cellB), "over", "under") it is always reading "under" despite it should read over? any ideas?

[MathewPartridge]

Have you come across the pattern of =switch(true(), test1, result1, test2, result2, ....) It allows a series of sequential evaluations of different conditions. A much easier to read pattern over certain types of nested IF statements.

[usmanghori]

Perfect Sr! But you put the practice file in every video, please dear

[AdrianMartinez-rehh]

Great video, straight to the point. Thank you so much.

[thomasshakelton]

straight to the point and solved my issue, thank you sir 🙏

[DennyGiuggioloni]

Hi there, I am working in a spreadsheet where I need a different outcome based on If the assumption i do is true or false. The IF formula would allow me to put only the outcome if the IF is true (1) or false(2). is there a formula that link the outcome 1 with the condition 1 and then outcome 2 with assumption 2 etc ?

[bsfatboy]

now I know what to do when I hate my coworkers

[production.coppergat]

CAN YOU HELP ME HOW TO SHOW THE FOLLOWING GIVEN BELOW;
8:15AM TO 8:30AM MENTION -1HOUR,
8:31 TO 9:00 MENTION -2HOUR,
9:01 MENTION -4HOURS,
12:00PM MENTION -4HOURS,
3:00PM MENTION -2HOURS,
4:00 PM MENTION -1HOURS ALL THIS IN ONE CELL IS THERE A WAY?

[orcreations]

Which formula used for multiple text criteria.please answer me urgently

[melissamcgranahan]

hello Mr. Excel,
I am working on a complex IF then statement. I keep getting an error with what I have
=IF(CB3<15, "0", IF(AND(CB3>15, CB3<31), CB3, "1", IF(AND(CB3>30, CB3<61), CB3, "2", IF(CB3>60, “3”))))
I need the function to be within a particular cell if a particular range of numbers is true(i.e., between 31-60, it will provide a value of 2). I can get the individual statements to work but not altogether. Any advice?

[jamesbowers]

Good video, you helped me out with my level three AAT revision there mate!

[tutsecret]

How about if, example: if is China or India, then is 4-Other Asian; if is USA or Russia or Ukraine, then is 1-White, if is Angola or Etiopia then is 2-Black, if is Vietnam, then it is 6-Vietnamese and so forth. I tried to use =IF(OR(A2={"China", "India"}), ""4-Other Asian", ""), so how I add the USA, Russia to give me the 1-White race category?

[epicurean]

What if two columns will be interpreted. please help me
Ex. c2 is frustration and e1 is instructional- the interpretation is frustration
if c3 is instructional and e3 is independent- the interpretation is instructional
if c4 is independent and e4 is independent- the interpretation is independent

I have two cells to be interpreted please help me out to generate automatically

[maticulex]

How do I make an IF function that has a greater than, less than, and in between. Ex: IF(B10>75, “High”, IF( B10<25, “Low”)) but I need to include a 75>B10<25, Moderate

[mohamedelbaz]

Hello, Can we make a one equation for multi conditions like the following :-
- if someone have data within a couple of months (date of visit - weight- a score)
we need to find out if he acheived this score or no within 30 days
OR if his weight decreased by a specific percentage within 30 days

[hhectorlector]

99.8k subs.. just found your channel and added 1 to that count :) do you have any online classes like on Udemy or something like that?

[redhaakhund]

Thanks a lot for the video. I have Q., i have three conditions ‘On Leave’, ‘On Duty’, ‘Wating’ and Date From and Date To.. How to solve this, i compared To Date with TODAY() function it didnt work?! How to fix this. Please advise