Excel Magic Trick 939: Use OR and AND Functions In IF Function For Logical Tests

[@rodrigocustodio1955]

Mike is the most humble Excel guru/genius in the world! Thank you, Mike!

[@excelisfun]

You are OR and AND Welcome, Rodrigo : )

[@excelisfun]

I am glad that you like the videos!

[@bradcarlisle9901]

BEST EXCEL TUTORIALS ON THE PLANET!

[@JRcastro1984]

This is by far the very best tutorial video I have come across on KZbin!!! Your tutorial is so easy to follow and you explained everything to the T! Your video and your explanation are clearer than taking a course! Kudos.

[@excelisfun]

+JR Castro Glad the video helped!

[@dubagentselekions8221]

I can atest... he made it clear and fun....now Excel is my favourite in Microsoft

[@excelisfun]

I am glad that you like the videos! Thanks for liking the videos!

[@jacic5893]

Once again you have proven that you are the MASTER!!!! I have learnt so much from you, more than I have ever learnt from a dumb course or MS Help (ha!)

[@excelisfun]

I am glad that you like the video!

[@excelisfun]

I am glad that you like it! I will keep it up!

[@ggbeach]

Thank you XLSFUN - your voice sounds a bit tired - please take care yourself - you are our angel !

[@tirathchhatriwala2018]

Your explanation is very clear. I wouldn't mind paying to learn this from you.

[@mukimmorshed8154]

Thanks a lot! You really are a very good trainer! All your videos are truly awesome! Systematically you explain all options. Hat's off sir!!!

[@excelisfun]

Mukim Morshed Glad you like the videos!

[@binababu657]

Keep it up !!! If u have a fan club, we all will become its members.. u r really systematic and planned.. Hats off to you Sir...

[@excelisfun]

I am glad that you like it!

[@HopeCastle2000]

Thank you. You are the BEST teacher I have ever listened to!

[@excelisfun]

I am glad that you like it!!

[@Rincon009]

My friend, you do a simple superb job on teaching this in a way that regular people like me can understand! KUDOS and Thanks!

[@excelisfun]

You are welcome!

[@footballsoccerreferee6152]

The best ever. Thank you so much, you are helping change the world one trick at a time.

[@tchambard]

Excellent! I’ve really been struggling with IF AND OR and I think this explanation might have finally made it sink in for me! Thank you!

[@excelisfun]

You are welcome, trina c!!!!

[@fammnak852]

Thank you so much!! You really made Excel fun and easy to learn! I used to avoid Excel and now I really love it. Explaining Excel functions are not an easy thing to do, so I appreciate the great help.

[@robertbrutus8774]

brilliant - thanks, you presentation was scaffolded step by step from simple to complex and your explanations were clear

[@excelisfun]

You are welcome!

[@hazemali382]

not only My Favorite Channel but also the best for everyone

[@excelisfun]

Thank you, Hazem!!!

[@excelisfun]

You are welcome!

[@philiplbrown]

Fantastic tutorial. I'm somewhat fuzzy on the IF, AND, OR formulas but this is tutorial is easy to follow and well structured.

[@excelisfun]

Are you still fuzzy after watching this? I hope the video helps!

[@ashafar8484]

@@excelisfun its cool that in every video i see, that u still react to new comments lol. Good customer support (:

[@excelisfun]

@@ashafar8484 : )

[@excelisfun]

I am glad that the videos help!

[@excelisfun]

Yes, I am aware of that! You are welcome for the tutorials!

[@shakeboi]

👍

[@donbreda]

Great video with clearly explained content! You really are an excellent trainer, I like how you vocalize the UI action / shortcut you are doing instead of just doing it silently. I always dragged to extend a formula, didn't know about double clicking to "send it down". I also learned the F4 trick from this video for locking cells. Nice job!

[@adewaleoke2000]

I always enjoy how you explain excel, it is really fun! ExcelIsFun!!!!

[@excelisfun]

Thanks! I am glad that the videos help!

[@linadudoniene2134]

Many thanks! You explain more better than official Office online

[@chrisnaidoo4596]

Excellent explanations and not too hurried, even I could get it right! Keep on inspiring us dummies :)

[@namelessone1811]

Just found and subscribed to your channel! Your a excellent teacher and excel god! Thank You!

[@excelisfun]

You are welcome, Nameless One!!! Thank you very much for your support with your comment, Sub and Thumbs Up : )

[@DanielDajer-y9e]

🎯 Key points for quick navigation: 00:00 *📘 Introduction to using the AND and OR functions within the IF function for logical tests in Excel.* 00:25 *🏢 Describes a company scenario for extending credit based on a logical test using the IF function.* 00:55 *🔄 Explains how a logical test or formula works, resulting in TRUE or FALSE outcomes.* 01:32 *✅ Example of an IF function where "extend credit" is returned if the logical test is TRUE, otherwise "no credit."* 02:44 *👫 Introduction to using the AND function when multiple conditions must be TRUE to extend credit.* 03:51 *👥 The AND function requires all conditions to be TRUE for a TRUE result, demonstrated with credit and sales conditions.* 04:59 *📈 Demonstrates embedding AND within an IF function, achieving a more complex logical test.* 06:07 *🔗 Explanation of the OR function, which requires only one condition to be TRUE for a TRUE result.* 07:11 *🌀 Demonstrates embedding the OR function within an IF function to return results based on alternative conditions.* 08:21 *🔍 Shows how to apply the AND function for three conditions that all must be TRUE, using locked cell references.* 09:43 *⚙️ Embeds the AND function within an IF function to output specific text based on all TRUE conditions.* 10:23 *❌ Showcases the NOT function combined with OR to indicate when none of the conditions are TRUE.* 12:54 *📉 Illustrates wrapping the NOT function around an OR function to identify rows where no conditions are met.* 13:39 *🧮 Demonstrates using IF with the NOT and OR functions to output custom text when no tests are passed.* 14:05 *🌟 Explains a complex condition where a specific condition must be true and one of the others must pass.* 16:19 *📊 Wraps up by embedding an AND-OR logical test in an IF function to extend credit based on a more intricate logic.* Made with HARPA AI

[@krn14242]

Love logical formulas. Thanks Mike.

[@hasanahmed-ls9lb]

You are genius ... rhanks a lot for teach us...pray for you sir

[@AnnaMariaZifia]

Many many thanks! This is your 3rd video I am watching. You saved the day! (by the way, nice voice and arthrosis. I mean, very clear.)

[@srvmusicmaker4792]

Your tutorials are amazing Mike. Being bad at Excel has hindered me from gaining grabbing good professional opportunities before. Not anymore :)

[@danicequeabeah7856]

This tutorial is great. I am getting to it bit by bit. thanks a million.

[@fernard8985]

Hey, I really appreciate your videos, I've learned a LOT from your lessons. I have one question though: Is there a video where you explain how torefer to an array of cells using the IF function? For example: =IF(AND(A1=B3,OR(A2=B4:B10)))- this one doesnt work obviously because IF doesnt "understand" the reference to an array.

[@maureenlymer9797]

Really enjoyable - I feel much more confident with various functions. Thank you.

[@telli22]

your videos are so helpful, thank you. How would I write IF a range of cells = yes or n/a then another cell = yes, but if any = no then the cell = no. I could do each individually but wonder if there is a faster way.

[@manjunathagangaiah6755]

Thank you so much sir. Step by step I tried with Excel sheet and everything worked fine. Nice explanation. I Easily learnt nesting of IF, AND, OR.

[@excelisfun]

You are welcome!

[@zainabahmadi1069]

Thanks to this video I was able to solve a question that I spent multiple hours on, in under 5 mins.

[@excelisfun]

Awesome! I am glad that the video helps, Zainab! Thank you for the support with your comments, Thumbs Ups and Sub : )

[@nnekababy]

Who else just did their homework in 2020 with the help of this oldie but goodie?!!!

[@excelisfun]

Glad to help with your homework, NnekaBaby !!! Thanks for your support with your comments, thumbs ups and Sub : )

[@excelisfun]

Thanks!

[@MrNinjagofan]

Great tutorial! Thank you so much for all your videos. They are simply AWESOME!

[@excelisfun]

Glad you like the video!

[@bugrahanryanunver3976]

Thank you for the detailed information. I just have a question. I am needed to make an excel chart that includes a percentage in one column, number of sales in one column (minimum a person has to make is 44) and number of mistakes in a month. To sum this database into a final gradin rate, I would need to come up with a function (the if function seems to be the one in my opinion). My question to you is, which function(s?) should I be using and how can I integrate a data that is different than the other ( percentage-number)? Thank you very much in advance

[@ron37wb]

Great video!! Happy 4th of July. Got Don't Fear The Spreadsheet, quite a book.

[@excelisfun]

Happy 4th!!

[@ModelbaanTBM]

Hey ExcelIsFun, Did you know about the 'evaluate formula'-function in Excel. It could be very useful, also for in tutorials I think.

[@loney4288]

I enjoy & love to watch yr video...explanation is very clear & straight forward !!I'm having some problems... by trying to insert the "Qty in stk" to the "Pending orders".What formula will best suit the following conditions ?1. "Product code" must be the same for these two files :"Pending Orders" & "Stk on hand".2. "Supplier id" must also be the same for these two files3. "Customer id" must be the same for these two files with additional conditions :a) If "Cust_id = PRO027" must specify clearly on result column as- Example : "PRO027=1,000" (to sum up the qty for PRO027 under "Stk on hand".b) Sometimes, per "Pending Order" line item may have two results ##:##1) Qty in stk (condition 1 to 3 are meet) &##2) "PRO027=1,000"4. When the result for above 1 to 3 is meet will need to 'sum' all the qty for repeated "product code/Cust/Sup" under the "Stk on hand" qty.Thank you in advance for your help.

[@sayedmohdsalman]

Simply great explanation.

[@ishansinghal850]

Awsm videos...i wanna ask if there is any fucntion thru which i can map a field that the value enter in that colum should range between only 2 values or the value put should not b more than the defined value ?

[@seshisvs6915]

it is very simple to understand...........Thank u sir

[@excelisfun]

T SESHARAO You are welcome!

[@jlnsarma2509]

Thanks a lot! You really are a very good trainer! All your videos are truly awesome! Systematically you explain all options. I would like know can we make data base software for HRMS wherein we can by simply putting employee code it shows all the data like first nam, middle name, surname, designation, address, department, ctc, job profile, d.o.b, d.o.j, age in years, date of retirement like so.

[@chandankumersarma9962]

Hi, thanks for your nice tutorial. i see in your tutorial you use $ sign in the function by shortcut key, pls teach me how to use $ sing in the formula by shortcut key. thanks!

[@jonathantaruc2124]

Finally, like Yoda, A TRUE MASTER!!!

[@67MGD]

HELLO ,is it possible to link the content of the box with number (10) in the "NEW FORMATING RULE" window at conditional formating >>format only top or bottom ranked values , with an external cell of the sheet ? Thanks ....

[@rammpeth]

THANK YOU SO MUCH YOU SAVED ME SO MANY HOURS!

[@anil11996]

Your work is excellence

[@Dlee-pz1ur]

Good sysmetical explanation. 👍

[@excelisfun]

Glad you like it!, deynes!! Thanks for your support : )

[@rubenss4]

thanks!! easy and direct to the tgt also for non English speakers

[@excelisfun]

You are welcome!

[@TarunendraDwivedi11]

Yes you can use conditional formatting.

[@xxxRebbleRulesxxx]

Great explanation, thank you.

[@joshuamanampiu6489]

Thank you for this.

[@athan1145]

Thanks, Great Examples!

[@fangyuanliu7573]

great tutorial as always

[@MrDavidcaldwell]

Very well presented many thanks:)

[@marionwalker2820]

You are fabulous! ;-) I learn more from your site than any other!

[@excelisfun]

Glad it helps!

[@ronaldtugume572]

I would like to combine these two IF functions in one cell - =IF(K134="M1-W1",J134,IF(K134="Monthly",J134,0)) and =IF(F4="Completed",0,IF(F4="PR Delivered to Logs",C4))

[@sarahphilp860]

Good evening, I have a spreadsheet that has a formula that I cannot work out. Am I able to send it to you to ask for your assistance?

[@georgikerkelov189]

Hi and thanks for the comprehensive video. I`m trying to create something in relation to this plus conditional formatting so wondering whether you can help me a little. I have a sheet that tracks accidents that employees have. The criteria is -once i type their name it should turn into a yellow colour ( meaning first accident) , then second - amber and third - red. Now, the problem i`m finding is how to get Excel to check all the names above, match how many times their name has appeared on the sheet and return the correct result as in amber or red colouring of the cell. Thanks in advance

[@futuresuperstar4life]

What does the F4 do???

[@selfmadetool]

Thanks! This helped my understanding a lot.

[@likhangchu1145]

Very useful and resourceful to me! Thx for this effort! I really learnt a lot :)))

[@J58988S]

Hi, Is my worksheet corrupt or am I doing something wrong here... Value in in cell D3 is the word "Unlimited" and value is D8 is $105 the formula =AND($D$3="unlimited",$D8>=1) is TRUE. However if I drag the formula down, D9 is a blank cell, I still get the value at TRUE for =AND($D$3="unlimited",$D9>=1). I tried the formula on a new workbook at it works fine. Is there away to rectify this? Thanks in advance.

[@alfarhangroup505]

Ur the best. I got one file need to put benfit for target which is done. Could you please give me hand 🙏🌹

[@DipakThapa88]

i want to know a conditon if any cell value 15 then another which i selected is value is 1, and if 18 then 2 if 26 then 3, how can i make it

[@puseletso100]

Can i have one cell change into different colors based on what i type

[@rajeshkumarshetty29]

Thankyou sir... Very nice and good explanation..

[@teeltee3701]

very good trainer

[@shirleyfred]

Your video provides me solution to most excel issues I've had living up to its name "Excellisfun". I am having trouble getting one to work. I tried using a nested if and function for a large number of data but no success yet. The condition are.....Vendor list = COD if series starts with "A" (A12345) and Pline = XY, Vendor=DOM if series starts with "A" (A12345) and Pline is BL, Vendor = DMM if series starts with "K" ( K12345) and Pline is DL, CL (not XY or BL) and Vendor = BUD if series does not start with a letter (12345) and Pline is XY,BL,DL,CL and other. Since I am only interested with the first letter in the "series" column, I included a left function. The single formula works by itself, but when I have all three if functions nested, it gives a #Value error. Single formula is =if(and(left(series,1)="A",Pline="XY"),"COD","BUD". my nested if function is =if(and(left(series,1)="A",Pline=""XY"),"COD"),if(and(left(series,1)="A",Pline="BL"),DOM,if(and(left(series,1)="K",Pline"XY","BL", "DMM"),"BUD"). Please tell me, wher did it go wrong or is it completely wrong.

[@anthonyverdin6743]

Your awesome Mike!!!

[@excelisfun]

Glad to help, Anthony!!!

[@wayneseymour1]

I want to know if I can use the nested "IF" ,"And","or" function to do the following": I'm making a electrical Load schedule that will calculate the totals for a electrician. I have in one column, three entries 1. Lights 2.Outlets 3. ACU If the user puts in the Lights or Outlets in (olumn A column C inputs in VA. My formula:=IF(OR(C6="LO",C6="CO"),"va") which works, but I want to also include the ACU so my result for ACU will be "hp" instead of VA, I can use a note on the sheet to tell the user to put Outlets and Lights first, but as Newton's law states... well you know.Plus the entries for Outlets and Lights may be short or long, there are alot more other "If" functions I'm using but they will depend on these being correct first. Plus I may have to use this same function using VA,hp and W further in the sheet.

[@vipinvarunny377]

Hi thanks for sharing your great knowledge.I do need a solution in excel like i do have some sort of prices of home appliances some are then below 100 and some are above hundred.I would like to add 5$ if the price will be above hundred $.Can you help me out ?

[@excelisfun]

Me too!

[@anil11996]

Your learning function(Trick) nice i like it

[@vickiallgood5639]

Help, I'm trying to making a sheet that has column A is a drop down list, in column B, I would like it to say if column A is (answer 2) they will need Form 1, can you help?

[@teeltee3701]

how can I ask a question to the trainer?

[@Sompopito]

Best video ever.

[@jeffchan8909]

What function do I use to model a situation where a ratio of less than 20% will yield 6% in an empty cell, 9% if the ratio is in between 20% and 50%, and 15% if the ratio is greater than and equal to 50%?

[@excelisfun]

Probably VLOOKUP. Here is a video all about VLOOKUP: VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function

[@sajidulmasum6880]

please can u help me ,how i can set auto weekly date in a month .

[@Hey_Delight]

Thanks for the big help Sir.

[@excelisfun]

You are welcome a lot, most awesome Delight In Life! Thanks for your consistent support : )

[@dubagentselekions8221]

Wow i think i have now grasp all the IF function(or (and 👍👍

[@excelisfun]

Glad the video helped you with the IF, Dub Agent!!!

[@915OneMusicOneLove]

"BOOM!" Thanks for the help

[@quizzingmaje]

Your videos are so very helpful and I've learned a ton! It's made my life so much easier. However, I've come across a question that I can't find a video for. Excel tells me I'm trying to list too many conditions in my formula. Would you be able to point me to a video to help or even better, help me fix the formula? Thanks so much. =IF(AND(AK18="F",AL18

[@quizzingmaje]

It works fine if only the first third is used, but I want to be able to differentiate between the F, S, and T and then what to do depending on which of those three letters appears. Thanks.

[@learnsolo2721]

thanks for the nice tutorial

[@KristinBodiford]

Thank you for the video! I am just learning IF/AND/OR (in Google Sheets) and trying to apply it to a blood pressure categorization where EU is SBP and EV is DPR. I want to say this - Normal (SBP under 140 AND DBR under 90), Grade 1 (SBP 140-159 OR DBP 90-99), Grade 2 (SBP 160-179 OR DBP 100-109), Grade 3 (SBP >= 180 OR DBP .>= 110). I keep getting a Parse Error. Can you spot my mistake(s) and help me learn what I am wrong? Thank you! =+IF(AND(EU2=110),"Grade Three","Other"))))))))

[@lordofson1596]

What is file name, I can't found

[@zhelpia952]

can i merge multiple data if the statement is true.. please help :)

[@marekwierzchowiecki5766]

Brilliant tut!!

[@excelisfun]

Glad you like it!