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.
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.
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.
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.
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 :)
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.
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.
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
@@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 :)
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!
@@AntoF1 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.
