I've never seen someone explore the filter function in depth! Your explanation is brilliant. I recognized your voice and then I saw your name, you teach on another channel! Thank you very much!
Thank you so much for the in-depth coverage of the FILTER function. You’ve got a new sub! The new feature of data validation automatically picking up the unique list of values is new to me and is indeed a very useful feature.
That's right, there are plenty of filter videos out there but definitely not a lot of good videos discussing all the ways to use filter. This was an amazing educational video. Thank you!
Hello Deb, Thanks for the detailed video on the filter function and also the logical operators... I do struggle with them but this video has been extremely helpful to understand... Also is quite helpful in my office tasks... Keep up the great work 😊
I started with the chat GPT out of interest and ended up binge watching whatever came next. Thank you for the clear teaching method and delivery. Well-deserved subscription.
Es hat sich für mich absolut rentiert ein weiteres Video zum Thema Filter-Funktion anzuschauen. Du erklärst sehr gut und strukturiert, alle möglichen Aspekte werden beschrieben. Vielen Dank und ich freue mich als neuer Abonnent auf Deine weiteren Beiträge.
Great video! Filter has replaced most of the other functions I use. Definitely my #1 function! For the part Non - Adjacent Columns, an other way is to use the CHOOSECOLUMN function or create 3 separate filters for each column..
Thanks for the comment. Yes you could replace the first FILTER with CHOOSECOLS, if you have Excel 365. The function isn’t available in any other version :)
Hi! Great video! Although when I'm following it along, in the bit of the "One or another but never both", my results are different. I get people working in London with a profit greater than 40k in my filtered results. Same formula you used.
Dear Deb, To filter non-adjacent columns it is easier to use the CHOOSECOLS function: =FILTER(CHOOSECOLS(Results,1,2,5),(Results[Exam]=H5)*(Results[Month]=H9),"No Records") 🤗
@@JoseAntonioMorato hello, yes that’s mentioned in the video. You can use CHOOSECOLS if you have M365 only. It’s not available in any other version. FILTER is slightly more widely available.
Thank you for your reply! Yes, you are right but how can I count specific rows in column D without adjusting its filter, while filters are applied in columns A, B, and C? Column D shows two different row counts unless I filter it manually. I know this isn't a private tutorial, but this would be useful for anyone analysing data. Also, I want to commend you on the excellent courses you’ve provided online. I try to watch them all when I can.
Selecting columns with a nested filter shows a nifty trick (the embedded array). However, you can only display the columns in the relative order of the original source data. Sometimes that's fine, but using CHOOSECOLS is much more versatile, allowing you to specify which columns to return and in what order. But then, that's not demonstrating the use of FILTER as a highly versatile/adaptable function.
@@weiwang2000 hello, yes I mention that in the video. There’s an annotation that says you can use CHOOSECOLS. Need to remember that CHOOSECOLS is only available in Excel 365. FILTER is more widely available :)
Hi hope you're doing well. I have a big challenge filtering data where a column in the table contains values separated by commas. I'm using the following function: =FILTER(tblData, ISNUMBER(X.MATCH(tblData[MultipleText], A1:A10))) However, it doesn’t return any results or only those rows where there are no additional values separated by a comma. tblData[MultipleText] has rows with this format: Red, Blue, Yellow, Black Blue, Yellow Green Red, Ping (...) A1:A10 range Red Black Blue (...) Any advice would be appreciated. Cheers
Love the video however adding the sort function to the filter function does not return "No Results" when no criteria is met, it returns #Value!. is there a trick to that?
Hi, I have a question about creating batch folders in file explorer using excel. Are there any way to prevent the system from auto arrange base on alphabet (for example I have 4 names: d a c b, now I want to create batch folders in the exact same order d-a-c-b not a-b-c-d). Thanks
Hi Deb, I tried to follow your tutorial but somehow it is not working and kept getting an error I am working on this formula: =IF(ISBLANK(D9),"",FILTER(tblReport,tblReport[STATUS]=D9)*(tblReport[PO NUMBER]=D11)) D9 & d11 are both drop-downs do you have a tutorial for this? thanks
Dear Madam/All, I am working on a table and need to find the total number of rows in a column using a function, such as SUBTOTAL, SUMIF or SUMPRODUCT with the filter applied. However, the functions only work when the filter is not in use. When I apply filters to different columns, the results don’t update accordingly. How can I get the total number of rows with the filter applied? I would greatly appreciate any help or advice on how to get the total number of rows with the filter applied. Thank you!
@@theportervibes hello, you need to use SUBTOTAL with COUNT or COUNTA. In a cell somewhere type =SUBTOTAL(3,A1:A10) replace my cell refs with a column in the filtered table.
We have been changing the links to go through to my website. I’ve just tested it and it downloads the correct file for me. The link should be to cellmates website not Google drive. If you see Google drive, please refresh your browser.
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns i.e 20/11/2024 Diner to 24/11/24 Breakfast BF (Column) 4nos Lunch (Column) 3nos Diner (Column) 4nos If you give me a solution i will be very greatful.
I've never seen someone explore the filter function in depth! Your explanation is brilliant. I recognized your voice and then I saw your name, you teach on another channel! Thank you very much!
@@Luciano_mp amazing! Thank you so much for the brilliant feedback! Yes that’s me on ‘the other’ channel :) thank you for watching - appreciate it!
8:34 what you call curly brackets are just brackets or curved brackets () . Curly brackets are {}.
Ah, you're right! Just a slip of the tongue, thanks for pointing it out though. Hope you enjoyed the video! 🙂
😂 I even zoomed in to make sure those were in fact NOT curly brackets.
@@cellmatestraining yes, always keen to learn more excel
Thank you so much for the in-depth coverage of the FILTER function. You’ve got a new sub! The new feature of data validation automatically picking up the unique list of values is new to me and is indeed a very useful feature.
You’re welcome and thank you for subscribing!
That's right, there are plenty of filter videos out there but definitely not a lot of good videos discussing all the ways to use filter. This was an amazing educational video. Thank you!
@@liquidapathy82 thanks so much!
Hello Deb,
Thanks for the detailed video on the filter function and also the logical operators... I do struggle with them but this video has been extremely helpful to understand... Also is quite helpful in my office tasks... Keep up the great work 😊
@@saumilparadkar8636 thank you!
I started with the chat GPT out of interest and ended up binge watching whatever came next. Thank you for the clear teaching method and delivery. Well-deserved subscription.
Thank you so much!
FANTASTIC. This is by far the best video I have watched on the "FILTER" function, and I have watched a number of them. Thank you so much, madam.
@@MichaelBrown-lw9kz you’re welcome! And thank you so much for your kind comments :)
Thx you for your awesome work and simple but effective explanation. Keep following and recommending to other members
Thank you so much! I really appreciate that 😊
I thought I knew quite a bit about the FILTER function but seems every day is a school day! Thank you for the video. New subscriber
Awesome! Thank you and welcome!
I had no idea of this filter with operations option! Excellent new tip & presentation!
A lot of people don’t! 😊
Brilliant, only found your channel today, fantastic content, comprehensive and so easy to follow.
@@clivegamble379 amazing! Thank you and welcome! Appreciate the follow! :)
Es hat sich für mich absolut rentiert ein weiteres Video zum Thema Filter-Funktion anzuschauen. Du erklärst sehr gut und strukturiert, alle möglichen Aspekte werden beschrieben. Vielen Dank und ich freue mich als neuer Abonnent auf Deine weiteren Beiträge.
Thank you!
Good one Deb 🙆🙆
Great video. FILTER is a very useful function.
Agreed!
Great explanation Deb! Didn’t know we can use - and = operators
Yes! Pretty cool, huh? :)
Brilliant! Many thanks. Very clearly explained. 🙂
You're very welcome!
Very nice update for unique values in drop-down.
I know right? No more messing around creating a unique list first!
Amazing explanation, thank you very much.
Regarding selecting columns I would add the options to use the CHOOSE function or the CHOOSECOL function.
@@Ezra-b1w yes I mentioned that in the video :)
Great video! Filter has replaced most of the other functions I use. Definitely my #1 function!
For the part Non - Adjacent Columns, an other way is to use the CHOOSECOLUMN function or create 3 separate filters for each column..
Thanks for the comment. Yes you could replace the first FILTER with CHOOSECOLS, if you have Excel 365. The function isn’t available in any other version :)
What versions include FILTER that DON'T include CHOOSECOLS? I didn't know there were any unless it's 🤮 Mac.
@@ExcelTL Excel 2021.
@@ExcelTL TIP: If you google the function and go to the MS site, it says the versions the function is available on at the top.
@@cellmatestraining Why would anybody use Excel 2021? I guess price, but not worth losing out on really useful features in 365.
Hi! Great video! Although when I'm following it along, in the bit of the "One or another but never both", my results are different. I get people working in London with a profit greater than 40k in my filtered results. Same formula you used.
@@felge1977 have you used the correct operator? Should be 40000 :)
Great video
Such a good function!
So good!!
I love you, my beautiful teacher, how wonderful you are
Dear Deb,
To filter non-adjacent columns it is easier to use the CHOOSECOLS function:
=FILTER(CHOOSECOLS(Results,1,2,5),(Results[Exam]=H5)*(Results[Month]=H9),"No Records") 🤗
@@JoseAntonioMorato hello, yes that’s mentioned in the video. You can use CHOOSECOLS if you have M365 only. It’s not available in any other version. FILTER is slightly more widely available.
Thank you for your reply! Yes, you are right but how can I count specific rows in column D without adjusting its filter, while filters are applied in columns A, B, and C? Column D shows two different row counts unless I filter it manually. I know this isn't a private tutorial, but this would be useful for anyone analysing data. Also, I want to commend you on the excellent courses you’ve provided online. I try to watch them all when I can.
@@theportervibes have you tried AGGREGATE with the ignore hidden rows option selected?
excellent tutorial 😊
🙏
Quite useful for academic purposes. If I want a total of e.g.English how do I do ?
@@deepakadroja6065 you would need the SUMIF or SUMIFS function.
@@cellmatestrainingThanks for quick response 🙏🪴🙏incorporate with Filter function?
great video, thanks :)
You’re welcome! Thank you for watching :)
Selecting columns with a nested filter shows a nifty trick (the embedded array). However, you can only display the columns in the relative order of the original source data. Sometimes that's fine, but using CHOOSECOLS is much more versatile, allowing you to specify which columns to return and in what order. But then, that's not demonstrating the use of FILTER as a highly versatile/adaptable function.
I agree! The vid is focused on FILTER but CHOOSECOLS is a better option if you have 365! Anything older and you don’t have the function!
Thank you thank you so much for this video
I'm so glad you got value from it!
"choosecols" may be used to output non-adjacent columns in the filtered array.
@@weiwang2000 hello, yes I mention that in the video. There’s an annotation that says you can use CHOOSECOLS. Need to remember that CHOOSECOLS is only available in Excel 365. FILTER is more widely available :)
Crystal clear
@@adnanozkara3576 great!!
Brilliant, thanks.
You’re welcome! :)
Nice
Hi
hope you're doing well.
I have a big challenge filtering data where a column in the table contains values separated by commas. I'm using the following function:
=FILTER(tblData, ISNUMBER(X.MATCH(tblData[MultipleText], A1:A10)))
However, it doesn’t return any results or only those rows where there are no additional values separated by a comma.
tblData[MultipleText] has rows with this format:
Red, Blue, Yellow, Black
Blue, Yellow
Green
Red, Ping
(...)
A1:A10 range
Red
Black
Blue
(...)
Any advice would be appreciated.
Cheers
Wow...Thak you!
You’re welcome!
Love the video however adding the sort function to the filter function does not return "No Results" when no criteria is met, it returns #Value!. is there a trick to that?
In the part "Non-Adjacent filter, you used Filter(Filter. Why didn't you use Choosecol()? Is that not easier to understand (and to explain)?
Hi, I have a question about creating batch folders in file explorer using excel. Are there any way to prevent the system from auto arrange base on alphabet (for example I have 4 names: d a c b, now I want to create batch folders in the exact same order d-a-c-b not a-b-c-d). Thanks
Thanks
You’re welcome!
Hi Deb, I tried to follow your tutorial but somehow it is not working and kept getting an error I am working on this formula:
=IF(ISBLANK(D9),"",FILTER(tblReport,tblReport[STATUS]=D9)*(tblReport[PO NUMBER]=D11))
D9 & d11 are both drop-downs
do you have a tutorial for this? thanks
Good explanation, I am benefitted by this tutorial. Thanks
Glad to hear that
Dear Madam/All, I am working on a table and need to find the total number of rows in a column using a function, such as SUBTOTAL, SUMIF or SUMPRODUCT with the filter applied. However, the functions only work when the filter is not in use. When I apply filters to different columns, the results don’t update accordingly. How can I get the total number of rows with the filter applied? I would greatly appreciate any help or advice on how to get the total number of rows with the filter applied. Thank you!
@@theportervibes hello, you need to use SUBTOTAL with COUNT or COUNTA. In a cell somewhere type =SUBTOTAL(3,A1:A10) replace my cell refs with a column in the filtered table.
Mine does not auto the unique values, is that the version I am using from work?
Hi , example file is not same with video file. Could you update it again ?
We have been changing the links to go through to my website. I’ve just tested it and it downloads the correct file for me. The link should be to cellmates website not Google drive. If you see Google drive, please refresh your browser.
This is confusing - I don't understand/see the use of curly brackets?
@@jeniceprovost3322 I said curly bracket instead of bracket. Follow what you see on screens
I followed this but I get only 'Function is invalid'
@@bernpatton8966 which version of Excel are you using? FILTER is only available on M365 and Excel 2021.
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns
i.e
20/11/2024 Diner to 24/11/24 Breakfast
BF (Column)
4nos
Lunch (Column)
3nos
Diner (Column)
4nos
If you give me a solution i will be very greatful.