Hope you found this useful. Why don't AND, OR functions work? I cover more details on pairwise lifting, broadcasting and functions that return scalar values in my course here: www.xelplus.com/course/new-excel-functions-course/ - this will explain why this method works and others may not. Grab the file I used in the video from here: pages.xelplus.com/filter-file-download
Hi, It's really a good learning! Just need one query resolution, Can we use both include & exclude(Not(is number(value))) criteria. Eagerly Waiting for your response on my query.
THANK YOU! After spending AGES trying to use the built in filters to do what I wanted, my brain finally kicked into gear and thought "didn't Leila do a video on this?". If I had only thought of you first, I would have saved loads of time. Boomer alert -getting old sucks.
Hi Leila, I need some help! I have searched through your videos but cannot find the solution for where there are unique values in each column. ie. Each column contains data for a specific month vertically. The vertical data local to that month contains business names and customer names. I want a function to only limit the results displayed to the chosen month. ie. return specific columns (not rows). Every video I've seen on the filter function as well as index/match functions thus far have only been solutions where each column is a singular type of identifier and the results work for rows. Does a solution exist that can produce columns as a result?
As I understand what you're writing, Row 1 is a Header. Column A is January, Column B is February. etc. ect. Rows 2:nn is data. You want to only display a particular month's data (presumably on another worksheet. Won't choosecols work?.@@G-Man01
@@G-Man01You could combine the Transpose function perhaps in your formula. So before applying the filter, transpose the data, so that the column entries become row entries, and then the Filter function should work as per your requirement.
Your videos have freed upto six hours of work time for me on a daily basis. So now I pretend to be working on those completed tasks during my new found down time. Many thanks for adding hours to my day!
This is by far the best excel video ever. Brilliant fucntion and super explained. You can also reorder your coloms in a different order when you as you do as in the example 2;4, you can also 2;4;3 the the coloms will be displayed in that order in the filter function.
You re soooo good in explaining both the usage of this function AND also the logic (Boolean logic to be precise) behind it. Many, many thanks for this excellent upload.
Quite a helpful video. The way you construct your videos with explanatory graphics, clear slow audio, overall managed pace & a moderate video length makes for swift learning of MS Excel. Thank you kindly.
I'm doing some other tutorials where they don't teach by making mistakes. I love how you're teaching by making mistakes! It helps align our thinking and understanding with yours.
I used your Excel videos to give training in my office and it was grand success. Thanks a ton!!❤ Except this particular use case covered in this video...! I also covered a similar case.
You are just amazing !!! The way you presents and scenarios discussing are not just a mere exhibition of your talents rather an excellent way to making audience enthusiastic and helping them to think independently and develop accordingly...!!! HATS OFF 🤩🤩
Wow... I've seen a bunch of formulas use the multiplication thing {true,false,etc...} before but never understood why. Thank you for that VERY clear, simple explanation! SUBSCRIBED!!!
Wonderfull! I used the Index funcition to select the collumns I wnanted exclude for the result. This command "choose" is much more easier to use. Really excell is turning easy.
Really enjoyed this, I like the fact you stress bracketing, it's so easy to slip up using say sumproduct with multiple and / or criteria, re bring back certain columns, you can also use another filter; filter ( filter range, { 1,0,1,1} ) , where 1 = column to return 0 = ignore. but you can't reorder with this, but
strange that the AND or OR function does not work there. I tried after seeing your video, but couldn't get it working like this: =FILTER(B8:C17;AND(B8:B17="a";C8:C17>100);"") I guess that works with single lines only
Did you try it as an *Array Formula* ? Also, known as *CSE* or *Control+Shift+Enter* Formula - when a formula is entered with CSE, Excel automatically wraps the formula in *curly braces { }* - do not type the curly braces manually - it doesn't work and it can generate errors with some Add-ons. Type: = FILTER( B8:C17; AND(B8:B17="a"; C8:C17>100); "" ) *[Ctrl] + [Shift] + [Enter]* Cell: = {FILTER( B8:C17; AND(B8:B17="a"; C8:C17>100); "" )} By the way, I appreciate so much you are *using ";" as a separator* - Excel International versions as default. I always use it whenever possible - and advise people to do it, including Miss Leila - it is more "internationally-cross-cultural" readable - but it seems nobody cares. Note: I consider Excel Formulas as a Programming Language, so I use *space strategically* (even before Python was released) - it makes the formula more readable and it helps to keep track of nested parenthesis (not brackets, *these [ ] are brackets* ). Don't worry, it works as many spaces as we want, I add after every "formula =" (before causes error), after every *; separator* , and if there are nested parenthesis insert spaces to some correspondent pairs. In big formulas, I also use linebreaks mainly with nested IF() functions - it works as well.
As always, @leilagharani pulls through. I restructured a table I use pulling it apart to simplify data entry. I didn't know how I'd tie them together, but I knew I could count on Ms Gharani on showing me how.
Thanks a ton. I never used Filter and Index formula before, but thanks to your tutorial, I am now able to use both to create a Income Statement where for any drop down product, the whole statement changes.
Your multiple criteria in one column works when you have a fixed number of values you're searching for. But you need a different formula based on the count of values. I always like to come up with a single approach, so for each potential value, you need to do something like ( (( ="")+( = [table].[department])) * (similar for second value) * (similar for third value) * ... * (however many potential match values you're allowing) ). It works, and it's semi generic to allow for up to a particular number of values. But it's still "brittle" in that additional values to check in one step have to be added to the formula. It's also quite unwieldy to work through unless using the Function Editing environment Lab add-in. My conceptual way is to enter a delimited set of values and use TEXTSPLIT to get an array of values and the FILTER function has selected all potential values. I'm just not sure what wand to use, or start with a different approach. Anyone have idea how to approach this in an arbitrary way?
Top notch techniques for, essentially, doing multi-criteria lookups with just one formula. I have a table of data where Col A and B define a unique row and I need to find Col C. This becomes: Choosecols(Filter((theTable, theTable[Col A]= X) * (theTable[Col B]= Y), "NF"),3). Maybe this needs to be added to an IFError Function in case there are no matches. In any case, great stuff. Many thanks. I have been a subscriber for quite awhile and really appreciate all the great videos you produce.
An additional challenge to the challenge… How can you make the challenge problem a little more user friendly? For example, instead having a user enter in the filter criteria in a cell or use multiple drop downs, a user could check boxes for whichever departments they wanted to include. This would offer a more dynamic solution that wouldn't require using multiple cells for criteria of the same column. I was working on this problem last week and the solution stumped me for a little while but I eventually figured something out that didn't require macros.
This is a better way to handle AND or OR functions. I had so many problems in the past with those 2 commands that this seems much easier. Thanks for pointing this out Leila!
Loads of thanks for this very very valuable insight on the filter function, I was struggling so hard to get the multiple selections work and you just made me understand how filter works. Thanks thanks thanks.......
Nice one, as usual! But I was wondering why we can't use the AND() or the OR() functions instead of multiplying and adding. I discovered the answer: those functions do no spill. Therefore you can't use them in the FILTER criteria. Fortunately, the arithmetic operators (+, -, * and /) do spill.
Logical functions follow single valued logic however when you are multiplying 2 arrays, that's giving you another Array of True and false for each row, then all those rows that have a TRUE gets filtered out. If you use AND() then your answer will treat entire array as a single input and give FALSE even if a single value does not match which is pretty obvious.
Excellent video Leila, another option for multiple criteria could be: (and I learned this from Mike Girvin = @ExcelisFun ) = FILTER( TableDiv, ISNUMBER( XMATCH( G1:G2, TableDiv[Division], 0 ) ) It should work, since there's no link for the file, I can't test it, but it should. Nice intro for CHOOSECOLS function, thank you!
Thanks a lot Leila, I spent my afternoon trying to figure out how to return non adjacent columns with Filter(), and choosecols() was the answer. Many thanks
Excellent details and explanations. Had an issue trying to filter some data and never got the exact result needed but this just made my day. Thank you!
Absolutely love the way you explain it step by step. I do have one question. In your example, if you have lets say 10 departments that you have to choose, is adding them one by one the only option or can I just choose a range of the selected departments. That is my current problem. I did go through your video and simultaneously produce the results, but I had to add each one of the 10 departments in the formula I wanted to choose. I am thinking there is definitely a better way to do that, right? Please let me know. Thank you so very much Leila!
Nice tutorial Leila. I 'd like to ask you, how to filter by several values in the same column, that is, select rows that belongs to two departments. And, show all rows, but exclude those that its departments are in a list.?
This is great! qq: what if you want this to be dynamic so someone could select both filters, see results, or select just one of the filters, keep the other blank, and show results that are true for just that 1 filter, ignoring the second?
Instead of putting the column number in the choosecolums function, you could do a match lookup, this way you make it dynamic and don't need to remember which columns you chose when you wrote the formula, and the index function could also be used for the same goal, but that would requiere vstack for your lookups
Thank you so much for this! I had to do this for surveys where question and answer were on the same line. By using transpose unique questions, was able to get that lateral and use your method to match the user with the question from the table and copy the whole thing down and done! One thing I would ask, please use proper terminology for signs. ( ) are parenthesis, [ ] are brackets, { } are curly braces or curly brackets. Following your steps was making me cringe whenever you would call a parenthesis a bracket while having brackets inside. Just a personal thing. lol.
Coincidentally I was trying to solve exactly the same problem a few minutes before this video dropped. I had not even searched on Google or CZcams yet 😊
So the need for the capability to do this appeared to me today about 5h ago at work. Just got home and saw you uploaded this 3h ago. Couldnt have timed it better.
Thanks Leila for posting all these great contents, you always provide super useful tips and love the way you explain!! I tried to go a little bit deeper on this function. Is there a way to Group the results using the Filter function and summarize the totals? I noticed there is a GROUPBY Function, but it is still not released. So, I wonder how to accomplish this in the meantime. Thanks!
In sql this is so much easier. No n|d for so many nested functions for which im not a fan of, id rather use different columns. Very refreshing to see sort and choose cols I didnot know these columns
Perfect! Question: how do I adjust the formula to make the criteria LIKE instead of Equal? For example, return results that the Name column contains the word "Gary"; regardless of what comes afterwards.
Sooo cooool. I tried to apply *, + and - operators also in IF function and it works wonders. I hate AND/OR function, visually it confuses a lots especially in nested IF functions. Thankkks
Holy Sheet! - Super video just like the rest of yours. Have been using "AND" criteria logic frequently since seeing this video and understood "OR" as well. Had a bit of an epiphany today. "AND" and "OR"s can be nested as needed. Today I needed =IF((A1="X")+((A1="Y")*(B1="Z")),TRUE,FALSE). Worked like a charm! I'm sure there is a limit to the amount of nesting you can do, but this is a game changer. Thaks so much for these videos!
Excellent video as usual. Can you use FILTER function with two columns, but have an option to show ALL in either column? e.g. any Division and a specific Department, or a specific Division and any Department, or all Divisions and all Departments.
Thank you so much for that Leila. I have been using a double filter function for ages to get only the columns that I want. Very tedious when there are a lot of columns to put all those 0's and 1's. I will be using Choosecolumns from now on
Hi I'm a fan of your educational videos as they are always easy to understand and to the point. Now you insert brief movie clips to convey visual expressions. In my opinion they distract the viewer attention. The static images were good.
Is there a way that you can set up the 'or' formulas (using +) to also include the possibility of that cell being blank? Example - in your final example you have listed game or utility, but could I leave either of those blank it is still finds only the game or utility, which ever was left???? Great video - thanks!!
Thank You for this video. You have been very helpful. I have been looking for a filter function for Excel. I'm looking forward to trying this out for myself.
This tutorial is fantastic for understanding the FILTER function! I've covered similar Excel functions on my channel, and I love seeing how different approaches can solve similar problems. Keep up the great work!
I just used this same And formula last week only and today got Ur video..😊....but my was with additional conditions that filter only some alphabet in a cell so used search and isnumber formula along with filter...
Thanks for the super cool formula. I have a request here. I need to get the column headers alongwith the values. Can you suggest some formula. This becomes important when you have large dataset with various columns having similar looking values... Like monthly sales etc... Thanks in advance.
Great info. as always. Thanks for showing an actual "mistake". For me, that hits the part of my brain that really integrates all the elements of the formula and makes me think "aha".
Love this... Can this be setup to filter based on the columns containing just a part of the column text, i.e., filter by "ity" so that the filter results contain both "Productivity" and "Utility"?
Great Video! It will be incredibly helpful when I implement it into my workflow however, I am getting hung up something and not sure why. When I use the filter function and it returns the values that match the criteria certain columns' values are being repeated in the new cell. For example a cell that contains a 4 in the original table appears as 444444444 in the filtered results. Thanks for any help!
Hello I am new to your channel but I already love it. :) I have a dumb question for you though. How do i do the same thing you did in this video. By using my Data Entry Worksheet (DataEntry Table) as my source data, and a second worksheet to display columns that i want?
Hi Leila, as always a great tutorial, what if the filter criteria is not just 2 arguments, instead a bigger (dynamic) list, so how would you tackle this?
Hope you found this useful. Why don't AND, OR functions work? I cover more details on pairwise lifting, broadcasting and functions that return scalar values in my course here: www.xelplus.com/course/new-excel-functions-course/ - this will explain why this method works and others may not.
Grab the file I used in the video from here: pages.xelplus.com/filter-file-download
Hi,
It's really a good learning!
Just need one query resolution, Can we use both include & exclude(Not(is number(value))) criteria.
Eagerly Waiting for your response on my query.
THANK YOU! After spending AGES trying to use the built in filters to do what I wanted, my brain finally kicked into gear and thought "didn't Leila do a video on this?". If I had only thought of you first, I would have saved loads of time. Boomer alert -getting old sucks.
Hi Leila,
I need some help! I have searched through your videos but cannot find the solution for where there are unique values in each column. ie. Each column contains data for a specific month vertically. The vertical data local to that month contains business names and customer names. I want a function to only limit the results displayed to the chosen month. ie. return specific columns (not rows). Every video I've seen on the filter function as well as index/match functions thus far have only been solutions where each column is a singular type of identifier and the results work for rows. Does a solution exist that can produce columns as a result?
As I understand what you're writing, Row 1 is a Header. Column A is January, Column B is February. etc. ect. Rows 2:nn is data. You want to only display a particular month's data (presumably on another worksheet. Won't choosecols work?.@@G-Man01
@@G-Man01You could combine the Transpose function perhaps in your formula. So before applying the filter, transpose the data, so that the column entries become row entries, and then the Filter function should work as per your requirement.
Your videos have freed upto six hours of work time for me on a daily basis. So now I pretend to be working on those completed tasks during my new found down time. Many thanks for adding hours to my day!
This is by far the best excel video ever. Brilliant fucntion and super explained. You can also reorder your coloms in a different order when you as you do as in the example 2;4, you can also 2;4;3 the the coloms will be displayed in that order in the filter function.
You re soooo good in explaining both the usage of this function AND also the logic (Boolean logic to be precise) behind it. Many, many thanks for this excellent upload.
Quite a helpful video. The way you construct your videos with explanatory graphics, clear slow audio, overall managed pace & a moderate video length makes for swift learning of MS Excel. Thank you kindly.
Thank you for the kind feedback
Leila you're a Star....
I'm doing some other tutorials where they don't teach by making mistakes. I love how you're teaching by making mistakes! It helps align our thinking and understanding with yours.
Wow, your logical reasoning is amazing. How you retain all this in your head astounds me.
I used your Excel videos to give training in my office and it was grand success. Thanks a ton!!❤
Except this particular use case covered in this video...! I also covered a similar case.
Happy to hear the content is helpful at your work!
You are just amazing !!! The way you presents and scenarios discussing are not just a mere exhibition of your talents rather an excellent way to making audience enthusiastic and helping them to think independently and develop accordingly...!!! HATS OFF 🤩🤩
Thank you so much for the kind feedback!
Great reminder tip at the end 08:55. (OR belong together), then (AND).
Oh Leila, you don't know how this formula has saved my life. Thanks so much!!!
You're so welcome!
Your teaching is incomparable. Thank you very much!
You blow my mind and make smile along the video. 😆
Wow... I've seen a bunch of formulas use the multiplication thing {true,false,etc...} before but never understood why. Thank you for that VERY clear, simple explanation! SUBSCRIBED!!!
This was a neat, clean, and entirely useful tutorial on a common thing one does in SQL.
You've saved me time with this. Thank you.
I don't think I've ever seen anyone teach this good.
Wonderfull! I used the Index funcition to select the collumns I wnanted exclude for the result. This command "choose" is much more easier to use. Really excell is turning easy.
Really enjoyed this, I like the fact you stress bracketing, it's so easy to slip up using say sumproduct with multiple and / or criteria, re bring back certain columns, you can also use another filter;
filter ( filter range, { 1,0,1,1} ) , where 1 = column to return 0 = ignore. but you can't reorder with this, but
U can use xmatch to create the trues/falses when searching for multiple values in the first column and feed xmatch result to filter.
strange that the AND or OR function does not work there. I tried after seeing your video, but couldn't get it working like this:
=FILTER(B8:C17;AND(B8:B17="a";C8:C17>100);"")
I guess that works with single lines only
Did you try it as an *Array Formula* ? Also, known as *CSE* or *Control+Shift+Enter* Formula - when a formula is entered with CSE, Excel automatically wraps the formula in *curly braces { }* - do not type the curly braces manually - it doesn't work and it can generate errors with some Add-ons.
Type: = FILTER( B8:C17; AND(B8:B17="a"; C8:C17>100); "" ) *[Ctrl] + [Shift] + [Enter]*
Cell: = {FILTER( B8:C17; AND(B8:B17="a"; C8:C17>100); "" )}
By the way, I appreciate so much you are *using ";" as a separator* - Excel International versions as default. I always use it whenever possible - and advise people to do it, including Miss Leila - it is more "internationally-cross-cultural" readable - but it seems nobody cares.
Note: I consider Excel Formulas as a Programming Language, so I use *space strategically* (even before Python was released) - it makes the formula more readable and it helps to keep track of nested parenthesis (not brackets, *these [ ] are brackets* ).
Don't worry, it works as many spaces as we want, I add after every "formula =" (before causes error), after every *; separator* , and if there are nested parenthesis insert spaces to some correspondent pairs.
In big formulas, I also use linebreaks mainly with nested IF() functions - it works as well.
That is correct
For array formulas you need to use + for "or" and * for "and" as per the video.
I built a dashboard using all of this in the past week. Choose columns is a nice add! Thanks for all you do.
As always, @leilagharani pulls through. I restructured a table I use pulling it apart to simplify data entry. I didn't know how I'd tie them together, but I knew I could count on Ms Gharani on showing me how.
Glad it was helpful!
I love how detailed you always are showing different scenarios of data extraction on 1 function. Thank you Leila!
Thanks a ton.
I never used Filter and Index formula before, but thanks to your tutorial, I am now able to use both to create a Income Statement where for any drop down product, the whole statement changes.
Your multiple criteria in one column works when you have a fixed number of values you're searching for. But you need a different formula based on the count of values. I always like to come up with a single approach, so for each potential value, you need to do something like ( (( ="")+( = [table].[department])) * (similar for second value) * (similar for third value) * ... * (however many potential match values you're allowing) ). It works, and it's semi generic to allow for up to a particular number of values.
But it's still "brittle" in that additional values to check in one step have to be added to the formula. It's also quite unwieldy to work through unless using the Function Editing environment Lab add-in. My conceptual way is to enter a delimited set of values and use TEXTSPLIT to get an array of values and the FILTER function has selected all potential values. I'm just not sure what wand to use, or start with a different approach. Anyone have idea how to approach this in an arbitrary way?
Top notch techniques for, essentially, doing multi-criteria lookups with just one formula. I have a table of data where Col A and B define a unique row and I need to find Col C. This becomes: Choosecols(Filter((theTable, theTable[Col A]= X) * (theTable[Col B]= Y), "NF"),3). Maybe this needs to be added to an IFError Function in case there are no matches. In any case, great stuff. Many thanks. I have been a subscriber for quite awhile and really appreciate all the great videos you produce.
An additional challenge to the challenge… How can you make the challenge problem a little more user friendly? For example, instead having a user enter in the filter criteria in a cell or use multiple drop downs, a user could check boxes for whichever departments they wanted to include. This would offer a more dynamic solution that wouldn't require using multiple cells for criteria of the same column. I was working on this problem last week and the solution stumped me for a little while but I eventually figured something out that didn't require macros.
I have a similar problem
Same problem
This is a better way to handle AND or OR functions. I had so many problems in the past with those 2 commands that this seems much easier. Thanks for pointing this out Leila!
Since the include parameter needs a number (0 or >o), for any more than 2 ()+() statements, you can use IfNA(Xmatch(),0) as multiple OR statements.
👍
Loads of thanks for this very very valuable insight on the filter function, I was struggling so hard to get the multiple selections work and you just made me understand how filter works. Thanks thanks thanks.......
Hello teacher, I am from India. The way you understand in all your videos is very simple.
Because of you, I got the job of MIS manager of a company. You are very dear, I respect your heart.🙏
Wow, that's great. Congratulations!
It would have been helpful if you could explain the proper way to select a table, whether to include the headers or not.
Nice one, as usual! But I was wondering why we can't use the AND() or the OR() functions instead of multiplying and adding. I discovered the answer: those functions do no spill. Therefore you can't use them in the FILTER criteria. Fortunately, the arithmetic operators (+, -, * and /) do spill.
Logical functions follow single valued logic however when you are multiplying 2 arrays, that's giving you another Array of True and false for each row, then all those rows that have a TRUE gets filtered out. If you use AND() then your answer will treat entire array as a single input and give FALSE even if a single value does not match which is pretty obvious.
Excellent video Leila, another option for multiple criteria could be: (and I learned this from Mike Girvin = @ExcelisFun )
= FILTER( TableDiv, ISNUMBER( XMATCH( G1:G2, TableDiv[Division], 0 ) )
It should work, since there's no link for the file, I can't test it, but it should.
Nice intro for CHOOSECOLS function, thank you!
Thanks a lot Leila, I spent my afternoon trying to figure out how to return non adjacent columns with Filter(), and choosecols() was the answer. Many thanks
Glad it helped!
This is a freaking game changer. 100000/10. You are amazing! Subscribed!
Awesome, welcome aboard!
Excellent details and explanations. Had an issue trying to filter some data and never got the exact result needed but this just made my day. Thank you!
thanks leila. you are the best from the best
Absolutely love the way you explain it step by step. I do have one question. In your example, if you have lets say 10 departments that you have to choose, is adding them one by one the only option or can I just choose a range of the selected departments. That is my current problem. I did go through your video and simultaneously produce the results, but I had to add each one of the 10 departments in the formula I wanted to choose. I am thinking there is definitely a better way to do that, right? Please let me know. Thank you so very much Leila!
Nice tutorial Leila. I 'd like to ask you, how to filter by several values in the same column, that is, select rows that belongs to two departments. And, show all rows, but exclude those that its departments are in a list.?
You're so amazing it's unbelievable
Hello Leila!
I've tested with three AND criteria and worked!
Same syntax shown. Just added a third.
Thanks again!
This is great! qq: what if you want this to be dynamic so someone could select both filters, see results, or select just one of the filters, keep the other blank, and show results that are true for just that 1 filter, ignoring the second?
Instead of putting the column number in the choosecolums function, you could do a match lookup, this way you make it dynamic and don't need to remember which columns you chose when you wrote the formula, and the index function could also be used for the same goal, but that would requiere vstack for your lookups
Thank you so much for this! I had to do this for surveys where question and answer were on the same line. By using transpose unique questions, was able to get that lateral and use your method to match the user with the question from the table and copy the whole thing down and done!
One thing I would ask, please use proper terminology for signs. ( ) are parenthesis, [ ] are brackets, { } are curly braces or curly brackets. Following your steps was making me cringe whenever you would call a parenthesis a bracket while having brackets inside. Just a personal thing. lol.
Thanks. Also, Pls share the other activity where and all can use this functions.
Mind blown. Awesome information. Another great video. Thanks!
Glad you liked it!
Very Nice Mam
Amazing Logic of Boolean Algebra
As usual, the Excel advice was top notch but the Columbo clip put it over the top! 🤓
😂
Coincidentally I was trying to solve exactly the same problem a few minutes before this video dropped.
I had not even searched on Google or CZcams yet 😊
Very well explained 👍. Thank you leila ...Love u ..from India ...🇮🇳🇮🇳
So the need for the capability to do this appeared to me today about 5h ago at work. Just got home and saw you uploaded this 3h ago. Couldnt have timed it better.
Glad we got the timing right 😁
I have watched so many videos on building filters, but this is crystal clear, thank you. I finally get it!
Thanks Leila for posting all these great contents, you always provide super useful tips and love the way you explain!!
I tried to go a little bit deeper on this function.
Is there a way to Group the results using the Filter function and summarize the totals?
I noticed there is a GROUPBY Function, but it is still not released.
So, I wonder how to accomplish this in the meantime.
Thanks!
Excellent Presentation
Dear madam, I found it every useful.
Thank you a lot.
Wow Leila so detailed and explained well. Thank you so much it helped a lot! ❤❤❤
Glad it was helpful!
AWESOME exactly what i needed!
Thanks Leila. Maybe VSTACK is also very intuitive? first make 2 or more ranges and then stack them together....
Great! Thanks for sharing, Bart!
Mam you're Excel teaching skill is so cool, I understood everything in this video thanks for taught like this 😊😊.
In sql this is so much easier. No n|d for so many nested functions for which im not a fan of, id rather use different columns. Very refreshing to see sort and choose cols I didnot know these columns
Perfect! Question: how do I adjust the formula to make the criteria LIKE instead of Equal? For example, return results that the Name column contains the word "Gary"; regardless of what comes afterwards.
Sooo cooool. I tried to apply *, + and - operators also in IF function and it works wonders. I hate AND/OR function, visually it confuses a lots especially in nested IF functions. Thankkks
Holy Sheet! - Super video just like the rest of yours. Have been using "AND" criteria logic frequently since seeing this video and understood "OR" as well. Had a bit of an epiphany today. "AND" and "OR"s can be nested as needed. Today I needed =IF((A1="X")+((A1="Y")*(B1="Z")),TRUE,FALSE). Worked like a charm! I'm sure there is a limit to the amount of nesting you can do, but this is a game changer. Thaks so much for these videos!
Yay, that's great to hear! Glad it was helpful.
this formula was my life saver, thank you
Excellent video as usual. Can you use FILTER function with two columns, but have an option to show ALL in either column? e.g. any Division and a specific Department, or a specific Division and any Department, or all Divisions and all Departments.
Every time I am looking for solutions to my problems, you seem to upload a video for it. Can you read my mind? 😊😊
Thank you so much for that Leila. I have been using a double filter function for ages to get only the columns that I want. Very tedious when there are a lot of columns to put all those 0's and 1's. I will be using Choosecolumns from now on
Thank you very much Lila ben, my 2 yr old dream came true today. This video helped to solve that problem
wow the multiplication and addition explanation was spot on!
Glad it was helpful!
Hi
I'm a fan of your educational videos as they are always easy to understand and to the point. Now you insert brief movie clips to convey visual expressions. In my opinion they distract the viewer attention. The static images were good.
Thanks for your feedback!
Instead of choosecol function, we can also use xlookup, then select the headers and choose specific columns;)
In addition to this, if you just want to return a certain row, you can then use chooserows. Also try out take and drop.
You are great I learnt not only new formulae and also got to know about true false function how to use where to use * or + the reason behind it.
Is there a way that you can set up the 'or' formulas (using +) to also include the possibility of that cell being blank? Example - in your final example you have listed game or utility, but could I leave either of those blank it is still finds only the game or utility, which ever was left????
Great video - thanks!!
Thank You for this video. You have been very helpful. I have been looking for a filter function for Excel. I'm looking forward to trying this out for myself.
Very useful, especially the last part. Bracket is super important
This tutorial is fantastic for understanding the FILTER function! I've covered similar Excel functions on my channel, and I love seeing how different approaches can solve similar problems. Keep up the great work!
finally a good explanation to that * boolean logic. couldnt get sumprod because of that.
Glad it was helpful!
Superb explanation
You changed my life; you're the best.
I just used this same And formula last week only and today got Ur video..😊....but my was with additional conditions that filter only some alphabet in a cell so used search and isnumber formula along with filter...
This is a very clear guidance on very useful Excel functionality. Explaining logical operators is not easy but Leila presents it very well. Thanks.
Thanks for the super cool formula.
I have a request here.
I need to get the column headers alongwith the values.
Can you suggest some formula.
This becomes important when you have large dataset with various columns having similar looking values... Like monthly sales etc...
Thanks in advance.
this was great. Game changer. Thanks!
Great to hear!
One who is well versed with SUMPRODUCT, will understand this AND OR concepts quickly. Thanks Leila :)
Great info. as always. Thanks for showing an actual "mistake". For me, that hits the part of my brain that really integrates all the elements of the formula and makes me think "aha".
Love this... Can this be setup to filter based on the columns containing just a part of the column text, i.e., filter by "ity" so that the filter results contain both "Productivity" and "Utility"?
Thanks, you save my day
Instead of using * and + signs, you can use BITAND, BITOR or even BITXOR (if exclusive needed).
Wonderful, concise, and detailed. Exactly what I needed. Thank you! God bless!
Hello Mam,
Can you make a video on how to have subheading for tables on excel for the web. It would be really helpful.
Great Video! It will be incredibly helpful when I implement it into my workflow however, I am getting hung up something and not sure why. When I use the filter function and it returns the values that match the criteria certain columns' values are being repeated in the new cell. For example a cell that contains a 4 in the original table appears as 444444444 in the filtered results. Thanks for any help!
Thank You Leila, Your videos has been helping me a lot. God bless you and Thank You for your efforts taken to prepare these videos
Very well explained!
Glad you think so!
This is sooo helpful... I use the formulas to NO END!! you explain each step so well. TY TY!! I love these instructional videos!
Hello I am new to your channel but I already love it. :) I have a dumb question for you though. How do i do the same thing you did in this video. By using my Data Entry Worksheet (DataEntry Table) as my source data, and a second worksheet to display columns that i want?
You could download my workbook (link is in the description of the video) and adjust to your needs.
Awesome knowledge you have.. the way you make us understand any formula, is very simple but powerful👍
Hi Leila, as always a great tutorial, what if the filter criteria is not just 2 arguments, instead a bigger (dynamic) list, so how would you tackle this?
Choosecols! Didnt know about that one have been using "Choose" and an awkward array all this time. that's great!
So was I! CHOOSECOLS is much simpler