ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
How to FILTER with Multiple Criteria in Excel
ฝัง
- เผยแพร่เมื่อ 4 มี.ค. 2023
- Join 400,000+ professionals in our courses: www.xelplus.co...
In this video, we’ll set up the FILTER function with two criteria in different columns. Both of these conditions have to be met (AND) for the result to be included. If you're searching for how to FILTER your data with multiple criteria, this video has the answer. If you need OR criteria, for example looking up two different values within the same column, you'll need to use the PLUS operator.
🌍 My Online Excel Courses ► www.xelplus.co...
🎬 LINK to more TH-cam Shorts videos: • Shorts (Tips, Tricks &...
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
📚 Excel RESOURCES I Recommend: www.xelplus.co...
🎥 GEAR I use: www.xelplus.co...
More resources on my Amazon page: www.amazon.com...
🚩Let’s connect on social:
Instagram: / lgharani
Twitter: / leilagharani
LinkedIn: / leilagharani
👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
📎More explanation and examples in this video: th-cam.com/video/1mHAVptUKAk/w-d-xo.html
I cannot use the link ☹️.
Impossible to copy or click.
I think it is helpful to understand that true = 1 and false = 0, which is why you multiply for AND and add for OR. Going the other direction 0 evaluates to false and not 0 is true. Knowing this helps for all kinds of excel “tricks”.
Thank you! I was just coming to the comments hoping for an explanation of this!
Yes. Thanks - I’ve explained in my longer form videos on Filter. I’ll be doing another one specifically for this.
@@LeilaGharani can we use sum formula in exact last cell of filtered data? it may be changed length of filtered data.
That was a very useful tip to remember!!! Thank you!
The adding as an OR is a bit dodgy since two true return values added together is going to be two rather than one.
Of course Walter gets an A in chemistry. After all he is the best cook.
Underrated and unexpected comment
Well you do try. Some people are just not that observant.
are those breaking bad characters? 💀
Omg I thought it was such particular names
I always use + and * instead of "AND" "OR" - works fine!
No
@@bobgreenfield9158 yes
a much more straightforward approach is to turn data into a table and use slicer.
Love the logic. I wouldn't have went here and just used table references in a formula still but love
A) Using a table to reduce calculation time and being dynamic
B) Slicer would be the most straight forward method. Kudos
From my experience using Let Function and 2 Filter Formulas so that you could amend the criteria easily to suit any changes needed to made.
Are you referring to Power Query?
Thank you! very fast explanation
I multiply results from logic checks all the time. Sometimes in nested Index(match(index())) format. In situations where I can use FILTER instead, it should massively reduce the processing load on excel. Thanks!
Or you could use tables and table references with absolutes. Even further reduce the calculation run time. Get real fancy could have just made a PowerPivot table but meh
I typically compare the concatenated string - using the function or & but I guess this is more elegant. 😊
Thx for information *,+
The only drawback to this is it only works if you have only 2 things to filter. Not for more. I had to get really creative to have 3 or 4 filtering criteria
Thanks !!!
Or in SQL:
SELECT Student FROM Table WHERE chemistry='A' AND geography='A'
I was thinking the same thing 😄
I
THANK YOU!!! I'm learning python and sql and this really helps!
Ftw
i think excel is better
Awesome, thanks for tip
Thank you very much, i needed this video 🙏
The or condition only works if the conditions never occur simultaneously... because it just adds up 1s and if you go to 2 it breaks. Please mention that somewhere it can lead to serious errors.
Yes. I’ll cover that in a longer video version 🙌
@@LeilaGharani yes please. Literally nobody gives that hint... not even the official documentation. It is always assumed to be obvious (which it is only when you look at it as summation of 1s and 0s... but nobody talks that way. I mean having more than 2 conditions is also pretty common)
This is great...I use another way of incorporating Index Match but this seems slightly more simple to use.
Excel by the easiest instructor
You’ve literally just saved my life with this 🤣
How to display data from two different sheets using filter function specifically when the condition is " or " either in 1 sheet or second sheet
Thanks
That's so helpful.
Danke Oida! Voll hilfreich! :D
Thank you!
Excellent ma'am 😊
Thanks Leila
Thank You! ❣️
VERY VERY USEFUL
Hi Leila,
I really love your tips.
I have for you that looks similar to this.
I have a lottery syndicate with 20 members.
They all have different numbers so how do I get excel to highlight when a number drawn matches one of theirs on a weekly basis?
Was excited for this info. Only to realise my work pc is still on Excel 2016 :-(
Here's a "legacy" array formula that will do it, it must be entered using the key combo CTRL+SHIFT+ENTER instead of just ENTER into cell G3: =@IF(ROWS($G$3:G3)>SUMPRODUCT(($B$2:$B$11="A")*($C$2:$C$11="A")),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11="A",IF($C$2:$C$11="A",ROW($A$2:$A$11)-ROW($A$2)+1)),ROWS($A$2:A2))))
Once you have entered it you will see that Excel has added curly brackets at the beginning and end of the formula. Drag the formula down to cell G12. Seems straightforward to me...
@gorflunk Thanks. Time to brush up on my Excel arrays skill.
You could have used the AND function, correct?
Thank you again, Mam....
No one struggles with this, i promise you, and this 'solution' is only for comparing two columns.
give more about connecting multiple tables in one slicer.
I have a bit odd calculation requirement using the Filter function
I have Employee Trip data - with Employee Code - Country Visit - Trip Value
I need the only Employee with visited specification locations (e.g. USA & Canada) - the result should return the employee(s) who visited both countries
Wouldn't be easier and less confusing if the developers have decided to use the "and" and "or" operators instead?
Excellent
You are awesome ❤❤❤❤
دمت گرم. 👍🏻
LOVE YOUUUUUUU
When I have two excel spreadsheets open how do I prevent undoing information from the other sheet when making correction one?
You have to open up different instances of excel. You will lose the ability to copy formulas or reference across workbooks, but it also limits what the undo undoes. ;)
how can we use NOT as a criterion?
Does this completely replaces the AND( ) or the OR ( ) formula?
How choose only one value from filtered as per criteria mentioned
You just saved my day. I was trying to find a solution for this for 5 hours. Just landed on this video by chance. Now I believe in God 😅
Great to hear - there is a long form version as well here: th-cam.com/video/1mHAVptUKAk/w-d-xo.html
Can I do this for cells which have been highlighted with conditional formatting? Multiple columns, with individual formulas for each column to identify entries not quite right/need checking for errors.
But in larger number of rows, it takes too much time to calculate, any alternate solution for this?
How about both AND and OR?
How do you get excel to highlight or filter cells with the same name? I.e. Disney on page 1, page 2.
what about the OR function when I replaced the '*" to '+"??
Is there any way to have array criteria in the filter function?
One who has used the sumproduct can easily relate to this..
Hi
I had a doubt which is slightly different from the topic covered here.
How to paste values to a column where a filter is applied ?
Unfiltered the column, then paste as value.
After that filter again the column.
Good luck to you.
Hi ,thank you for your reply
But I wanted to paste the values into the cells that are filtered.
So the only option left for me is to take help of a lookup function I guess.
Does anyone know how to do this when you have not 1 or 2 criteria but 100+? Is there away to avoid having to do (...)*(...) this for all 100 of them?
Hi Leila ,
Hi to all followers,
I need your help on smthg I am trying to setup
I have a table with the following columns: Product, Month, Target
I need to build a table showing monthly total target for a dynamic array of products (months in rows and total target figure based on one or more products listed in a table)
Thnks for ur support
Breaking bad cast haha
Dope 👍.
Is this dynamic array functions only?
May I please know which version of MS office are you using 🙏, cause my Excel doesn't seem to recognise these formulas to practice them in first place
Excel for Office 365. You can practice them if you setup a free Microsoft account and use Excel on the web.
@@LeilaGharani thank you so much
Omg Walter white yo
=AND(FILTER(1),FILTER(2))
=IF(AND(FILTER(1),FILTER(2))="A",[@[Student]],"")
Figured I'd finish you off
and i use ChatGPT to get me the formula and in the next 3-4 years excel will have chatgpt built into it with microsofts multibillion dollar deal that they made with it and we no longer need youtube channels to help us with any more excel problems.
or just use a pivot table... no?
If I wanted to display those results into one cell, could I use the TEXTJOIN function?
Yes 👍
Oooooo, I think you might have landed on the quickest calculation method (run time for refreshing adding new data) I didn't even think of! Assuming it's on a table properly:
=IF(TEXTJOIN([@[Geography]],[@[Chemistry]],TRUE,",")="A,A",[@[Student]],"")
Damn. This is why I love Excel formulas (or logic for that matter) and brainstorming since there at easily over a dozen ways to do this but I think your idea would 100% be the fastest calculation time which actually does make a difference when you're dealing with 5 or 6 figure lines of data, let alone multiple other formulas that need to calculate or Queries/VBA scripts. Kudos to that unique idea!
But either way to your question, yes, just add a new column to your table and use:
=IF(RIGHT(TEXTJOIN([@[Student]]&(IF(XLOOKUP([@[Geography]],[@Chemistry]],[@[Chemistry]],"")="A"," - A","")),1)="","",IF(TEXTJOIN([@[Student]]&(IF(XLOOKUP([@[Geography]],[@Chemistry]],[@[Chemistry]],"")="A"," - A",""))
That would result in the Student name - A, or:
Jessie - A
If you wanted to change the hyphen it's the very last formulas and results within quotation marks. The outside if statement checks if Student name - and is blank since it wasn't an A in both and returned blank would then just not return a response or be blank. Your quicker method would be PowerQuery
Wow...🤯
My brain wants to equate (+) to "and".
I Dont want to filter but get unique. how?
It seems counterintuitive that PLUS is for the OR condition not the AND
Why? & is literally the symbol used to calculate bringing two items together
Either way CONCAT is the better way to combine, but you can also literally write out OR(a1,b1) and it will do or check, too, you'd just need to nest it within another if statement checking IF(....="TRUE",....)
I guess because in maths x + y is equivalent to x AND y. Not a buggy j just find it odd.
❤
Learned something new
Nice filter, I see what you did there...hehe
jesse shud hv got F in chemistry and Walter A+
At some point its just easier to learn SQL
id be careful in using this though, at 100k rows itll slow you computer down so much at itll run for at least 1 hr before it completes
cool
😘😘😘❤️❤️❤️🙏🙏🙏
In some cases instead of asterisk works plus symbol.🤔
Yes 👍 that’s for OR condition. When both criteria are in the same column.
@@LeilaGharani sometimes it works in different columns.🤗
@@Rice0987 Oh yes. True 🙌 When you're looking for either Column A or Column B
Does this also work for conditionals for XLOOKUP functions as well or is it still better to precombine two candidate key fields together instead?
You would have to nest you lookup in an if statement (or IFS if you had more than two) so technically you'd be doing the same thing but your calculation time would 100% be quicker, especially if it was on a table so:
=IF(XLOOKUP([@[Chemistry]],[@[Geography]],[@[Geography]],"")="A",[@[Student]],"")
Besides being able to shortcut fully writing the column header you also won't have to deal with a 3rd nested of IFERROR should you use absolute cell to column references and returns while avoiding getting NA
I would honestly just put those two columns in alphabetical order…
Ms Leila i have humbally request to you..i need Ms 365 that you're using..
Just my biggest wishes...can you give me this setup in free please
Assuming this was done on a table properly:
=IF(([@[Chemistry]]&[@[Geography]])="A",[@[Sudent]],"")
=IF(XLOOKUP([@[Chemistry]],[@[Geography]],[@[Geography]],"')"A","",[@[Student]])
Those would be my preferred method but to each their own and plenty of methods, probably over a dozen for this issue, I just like table references as you can shortcut after the [@[ and just a few letters of the header. Wish you normalize tables and table references so filling was a moot step for most. Maybe one day
There no way Jesse was able pass either of the classes😡🤬
Why are not just using and logic?
Perez is treading on thin ice with that pissy act he's doing
One who has used SUMPRODUCT, will get this fast. Thanks Leila :)
Thank you. For illiterates like me if(b2:b11 ="A", if(c2:c11="A",true,),)
Um, no. That's a broken formula. You have if Chemistry = A, return Geography. That is all your formula would do. Should you really want to go that method you could put your formula within a 3rd if statement where if = "A" then return [@[Student]],""
❡⭕⭕👌 ❤❤❤❤😢😂
Woah 😮
😊
😊