OR Logical Tests Made Easy with FILTER Function! Excel Magic Trick 1743
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1743....
Learn about aggregate calculations with conditions and criteria. Learn about an AND Logical Test, and then see how the FILTER function can help with OR Logical Tests and advanced conditional calculations.
See a review of an AND Logical test using SUMIFS, COUNTIFS and similar functions. Learn how to do an OR Logical Test inside SUMIFS and COUNTIFS functions when there is ONLY one column from the table involved. Then see that there is trouble if you try to do an OR Logical inside AVERAGEIFS function, but the FILTER Array Function will come to the rescue. Then see how to use the TEXT function inside FILTER Function to make aggregate calculations inside the AVERAGE function. Then see how to use FILTER .
1. (00:00) Introduction
2. (00:21) Review of an AND Logical test using SUMIFS, COUNTIFS, AVERAGEIFS and more.
3. (00:52) How an OR Logical Test works on a single column.
4. (02:04) OR Logical Test across one column using SUMIFS and COUNTIFS functions.
5. (03:45) Trouble if you try to do an OR Logical inside AVERAGEIFS function, but the FILTER Array Function will come to the rescue.
6. (04:14) OR Logical Test on One Column is equivalent to “Compare Two Lists” Logic. Use XMATH and ISNUMBER functions
7. (06:10) TEXT function inside FILTER Function to make aggregate calculations inside the AVERAGE function for a Day of the Week condition. Formula from Teammate: Stein J. Hågensen.
8. (07:37) How an OR Logical Test works on more than one column.
9. (08:38) All Logical Worksheet Functions interpret any non-zero number as TRUE
10. (08:53) FILTER Function to run OR Logical Test across more than one column.
11. (09:12) OR Logical Test uses a Plus Operator.
12. (09:28) Order of Operations in Excel Worksheet Functions.
13. (10:01) New and upcoming book: The Only App That Matters!
14. (10:15) Summary, Closing and Video Links
![NoCap - Yacht Party [Official Music Video]](http://i.ytimg.com/vi/rm_lvDln8kE/mqdefault.jpg)
3:12 = 💖 SUMPRODUCT 💖 ! Long Live this function! thank for the video Mike, I look forward to your book! Pumped to get it !
The Publisher, Mr Excel, says it will be out July 1 : )
Thank you Mike, you tirelessly continue to crank out awesome videos that make the team better and better!
I am getting tired, but only because the book takes soooo much effort. But the luck news is that I am on page 625 in the book, with about 200 pages left... Thanks for your consistent support, Chris M!!!
Thank you Mike. "Great"
You are welcome!!!
Thanks Mike. You are amazing.
Even if I don't want to add something I can't watch only.
We are all should support by like + comment +share.
We are owe you a lot because of the times which saved by your tutorials.
You are welcome, SoftwareTrain!!! Thanks for your support, and I am happy to be on the Team with you : )
Boom!Another Class That Has Put A Smile On My Face "Simply Awesome"...Thank You Mike :)
Smiles on our faces is the way to go. But it is easy with Excel becasue it is so much fun : ) : ) You are welcome, darryl!!!!
Awesome! As usual! Excel is so powerful with array formulas!
Glad you like this, Guy!!!!
Thank you Mike for this great video. Love the fact that you always put the old school formulas as well. Fantastic 😍
It is more fun that way : )
Mike!!! You did it again, your videos are so super helpful. Thanks for your time and effort to keep us learning...
You are welcome!
Seriously awesome video!
Better than a strong cup of coffee for getting my brain in gear for the day! Love it!
Nice = good video = strong coffee : ) : )
Thanks for this great video!
You are welcome!!!
Deep deep and deeper insight, I downloaded sheet and read thesis this is amazing and my conclusion is that FILTER is nothing but extended version of IF. There is slight difference front side. Thanks excel is really fun.
Very very well explained thank you. Been trying to learn this for a while now.
Glad this helped! Was it the OR Logical Test that you were trying to learn, Daniel?
The best for ever thanks
You are welcome for ever : )
Thanks Mike. Amazing video!!! : ) : )
You are welcome, Formula Guy John!!!!
nice work!!!
Glad you like it, Peter!!!
good explanation
Glad you like it, Jonathan : )
Amazing .... thanks Mike
You are welcome, Hussein!!!!
Hi Mike
U r rockstar
Thanks to share with us ur amazing tricks trips
Thanks
You are welcome, ashish!!!
Awesome Mike! Even when you feature functions that I know (or think I know) I learn something new. Like that ISNUMBER/XMATCH combo for the average and standard deviation calculations. Thanks for the Tuesday fun with FILTER :)) Thumbs up!!
You are welcome, Wayne!!!! We all learn each day, including me, here at the excelisfun channel : ) : ) : )
@@excelisfun Go Team!!!
Excellent video Mike. Formulas like these are why I love Excel. Can't do them in PBI.
Glad you like the video, Cary!!! Well, we can make the same calculation in PBI, it is just done differently in M Code and DAX : )
@@excelisfun Thanks for the clarification Mike. I still use M-code and DAX in excel. I just prefer the Excel diversity with formulas, DAX, M-code, and VBA. Thank you for your Excellent teaching ability, knowledge, and outstanding support to the online learning environment.
@@bamakaze You are welcome! Thanks for your awesome support : )
Hello Mike, love your videos! You mentioned your upcoming book. Have you scheduled a launch date? I would like to be the first in line!
Great Video! Filter rocks !!! Other way for AVERAGEIFS=SUMIFS/COUNTIFS so AVERAGE=SUM(SUMIFS)/SUM(COUNTIFS) (=G11/G12)
Or if you have only AVERAGEIFS and COUNTIFS arrays , AVERAGE=SUM(AVERAGEIFS*COUNTIFS)/SUM(COUNTIFS)
Awesome Teammate Exceλambda!!!!! I have added the formulas to the workbook : ) Go Team!!!
If you add an index in the formula You can also put in a dropdown for the columnheadings and make it more flexible
=AVERAGE(FILTER($B$19:$B$29;(INDEX($B$19:$D$29;;MATCH($D$7;$B$18:$D$18))=$D$8)+(INDEX($B$19:$D$29;;MATCH($B$7;$B$18:$D$18))=$B$8)))
Thanks for the cool formula, 67duiker!!!! I will try to add your formula to the workbook, but I can not currently access the server computer. Go Team!!!!
Another awesome video, Mike. Thank you! Can't wait for the book.
When I looked at the file, I noticed that AVEDEV instead of AVERAGE was used in K8:K14. For people whose system isn't set up in English, the "ddd" inside TEXT function might need to be changed to whatever the code in their language is or you'll get a #CALC error.
Thanks for the note about AVEDEV. That is the wrong function on the answer sheet. I fixed it and re-uploaded it so the rest of the Team will benefit : )
Thanks about the comment about the formatting in the TEXT function also, Enny!!! Go Team!!!
I had the same comment on using “ddd” in an international perspective at 6m45s. J8:J14 could contain dates with a cell formatting showing the weekday text, and K8:K14 use a WEEKDAY comparison.
Super cool!
Glad it is super cool for you, Kate!!!
I love FILTER and I would like to learn all about it including combinations with other functions. Thank you for the awesome video, Sir!
@@katestan7002 I just made this FILTER function playlist for you, since you love FILTER so much (just like I do): ruclips.net/p/PLrRPvpgDmw0lm44xdS40aBFs7y_8G1DLb
@@excelisfun Oh my God! Thank you sincerely. I cannot describe how happy you have made me :)
@@katestan7002 Yes, I am happy to make you happy : ) See you in the comments over at those FILTER videos.
Link to my question in the earlier comment is here: docs.microsoft.com/en-us/answers/questions/465124/power-query-sumifs-on-one-table-master-data-based.html
Currently I have used DSum function to achieve this. In my criteria table i have 110 criterias. So for DSUM to work, i had to insert rows in between and enter headers for each 110 line items. It doesn't look pretty :).. Also i would love to do this in power query and keep the criteria data dynamic. So that any member in my team can change the criteria. And PQ will go fetch the values based on the updated table
I am analyzing attendance data for different departments, count the number of those with >96%, and find out what percentage are they from the total dept. What formulas should I use? I have 600 rows with 26 depts.
Waiting for your new book.
I am not sure if it will be a good book, but it will have everything that Excel can do : ) I am on page 630 with about 200 still left...
@@excelisfun I am ready to pre-order!
@@runboston580 Thanks for the support. I hope it will be ok. But trying to do a book with all that is in Excel is just proving to be very difficult. I am already one year behind. My plan is to hand over manuscript next month, but we will see...
@@excelisfun no pressure...to be honest, you have done a lot already...I learned a ton from you because you make Excel fun just like the name says. Cheers!
@@runboston580 I am so glad to help : ) Fun and efficiency with Excel has been my goal at RUclips for the last 13 years : )
Thanks Mike, very useful. Is there anyway of doing an AND rather than OR? I'm trying to filter a list of companies that have both Prod A & Prod B. Any advice appreciated
Yes of course, use * for AND and + for OR.
Here is video: ruclips.net/video/2kvPdv_nvbM/видео.html
@@excelisfun Thanks Mike, doesn't quite give me what I want. C7 needs to be a spilled array (All sales reps). This ay my answer should also spill down. I'm not sure it can be done, been looking for hours!!
Hello, I am a highschool professor from a Spanish-speaking country teaching a English-based class who has just discovered your channel and I feel I have found glory, I have to teach from MS Word, to MS Excel and I have been looking for the videos, is there a way I can communicate with you so that I can understand the order of your lessons? I appreciate it.
Here is the Office playlist: ruclips.net/p/PLrRPvpgDmw0l45snFj8uDmuDUMQeBAVtd
Advanced Excel: ruclips.net/p/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
Data Analysis: ruclips.net/p/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT
But please, just watch my 2 minute introductory video about all my classes: ruclips.net/video/l1-1aVgFth4/видео.html
Hi, I m looking for one of your video where you showed how to create the right relationahips between 2 tables on powerBi?where one table would show some sales numbers per category and other some complains number per type of model per category... The example that I remember you used was the category of cars example : Kia, Mercedes etc and the specific model on the other table, could you, please, help me to find that tutorial? Thank you
I am sorry I do not understand your question and I do not remember an example with Kia and Mercedes data set : (
@@excelisfun thank you for the answer, I m sorry the cars model was just for example, I don't remember the exact ones that was used.. again thank you alot, I truly appreciate your videos and learn alot from them
is there easy way to change date format: mm/dd/yyyy to text format: yyyymmdd in power query? Or vice versa.
I am not sure how to add number formatting. If you load to worksheet or the data model, you can change the number format there.
In those difficult cases I always use add column by example. that gives Text.Combine({Date.ToText([Date], "yyyy"), Date.ToText([Date], "MM"), Date.ToText([Date], "dd")})
@@67duiker I see. But that is not adding number formatting to a date. That is converting a date to text. I mistakenly thought you were asking about adding number formatting to a date (like we do in Excel with façade number formatting but number underneath stays the same), which I do not know how to do in PQ.
@@67duiker In the case of converting date to text, column by example is good because the pattern is consistent : )
I tried column by example; when the data is large, sometimes not all cells are populated yyyymmdd from m/d/yyyy. I tried Date.ToText([HireDate],"yyyyMMdd"); it is working if you use MM, not mm. you could just change data type to date from text yyyymmdd, it automaticlly change to m/d/yyyy date type. Power query is powerful. Thanks.
Hi Mr ExcelIsFun, I found something strange:
i put sequence in h14 for 10 rows (1 to 10)
next in I column I typed letters from a to j
next in J column I typed from K to Q
and I tried this vlookup:
=VLOOKUP(H14#;H14:J23;{2\3};0)
forsome weird reason it returns only 1 column instead of 2.
but when use =VLOOKUP(H14;H14:J23;{2\3};0) and "send it down" then it does return 2 column but works for 1 row.
i managed to achieve 1 formula 2 columns return by:
=CHOOSE({1\2};VLOOKUP(H14#;H14:J23;2;0);VLOOKUP(H14#;H14:J23;3;0))
but I wonder why my first solution does not return 2 columns?
VLOOKUP is notorious for not being able to handle arrays as well as other functions. Not as bad as SUMIFS and the like, but also not so good. I wrote about this in my Ctrl + Shift + Enter book back in 2012 : )
@@excelisfun well, yeah but now with dynamic arrays its gets even weirder :)
@@ExcelInstructor Ya, the old functions that had problems - still have problems with the new Excel worjksheet engine : (
@@excelisfun I dont understand it, I mean clearly MS has smart ppl working on office and excel, but somehow "pearls" like this still remains, clearly that repairable and fixable but Ms somehow ommits it.
Hi Sir, How are you? Sir, for couple of days, I've a problem to merge Data from different files. I want to say, - suppose you have 3 clients or 3 folders. "Client A", "Client B" and "Client C". Under each folder or client, there are multiple files. Let's say, "Client A" folder contains 5, "Client B" folder contains 10 and "Client C" folder contains 15 files. The main problem is, the spelling and the sequence of the Headers are same in all the files in the same folder but not in the others.
Let's say for an example, "Client A" folder consists of 5 files which are of same headers like (Emp ID, Emp Name, Age, Ph, State, Sales). Likewise, "Client B" folder has 10 files, which also have the same heading like (Name, ID, Phone, Age, State, Revenue, Zip Code), but not exactly same as "Client A". In the same manner, "Client C" has 15 Files having same headers like (Zip Code, State, Zone, E-Name, E-ID, Sales, Contact No., Age) but not same as headers in "Client A" and "Client B" Folders.
In this situation, how can I combine the whole data and make a pivot on it? I kindly request you to please make a video on this topic Sir.
Sounds like you can use Power Query Get Data from Folder to apply steps to one file and it would apply steps to all files in the folder and append.
Yes, I want to do it through Power Query. Bit, I think, I failed to make you understand what I'm trying to say. How will you get them Compiled where there are different spellings and the order of Headers are not same. Read my question again, and you will come to understand it.
Before expanding the table You have to use rename function of columns headers. The file should be externel files with common header name that can replace the old headers with common headers
@@ndjanardhan But what if, in future 2 or 3 more clients are added. And under each client, there are 10 files. Then Do I have to open and change each header name? Then why should I use Power Query? I've already done it through VBA. But, I want to check, can Power query do it? I want to make it automated through Power Query.
@@kartickchakraborty9135 Yes u can do it from Power query. go through the link i.e. ruclips.net/video/rWi1fAhowZs/видео.html
I want to whats your name your page in facebook