Excel FILTER Function + Trick to Rearrange Column Order

Рет қаралды 145,172

Күн бұрын

Excel’s FILTER function is incredible. It enables you to extract a subset of your data based on criteria you specify. You can think of it like a lookup that returns multiple matches. It can return a single column of data or multiple columns, and with a clever trick, which I’ll show you at the end, it can even return non-adjacent columns.
Download the Excel file here: www.myonlinetraininghub.com/e...
0:00 Introduction to FILTER
0:31 FILTER function Syntax
1:03 FILTER with one criteria
2:03 FILTER with multiple AND criteria
4:31 FILTER with multiple OR criteria
6:26 Handing errors
7:56 FILTER and return non-contiguous columns
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy

Пікірлер: 434

@PCor18 Ай бұрын

Disclaimer: If any European people have trouble getting this to work use {1\2} instead of {1;2} if the semicolon is your default separator. After I spent nearly an hour figuring out this wasn't working because differences in regional formatting I'm so grateful for this very, very elegant solution!

@MyOnlineTrainingHub Ай бұрын

Thanks for sharing 😊

@lorenzoladejobi8701 2 жыл бұрын

The expo on use of "FILTER and return non-contiguous columns" is a game changer for me ! Thank you Mynda

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it, Lorenzo! 😊

@alinaqvi6203 6 күн бұрын

Thanks Mynda , learnt a lot about FILTR function, your videos are full of knowledge.

@MyOnlineTrainingHub 6 күн бұрын

Happy to hear that!

@indzara 2 жыл бұрын

Really liked the OR logic and the fact that everything >0 is treated the same. Nice design. Enabling multiple error values is another good tip. Thanks for sharing. 👍

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it 😊

@AnthonyEnglish Жыл бұрын

Phenomal, clear explanation, taking us step by step. This filter function is great, and I love the bit at the end where you show non-adjacent columns.

@MyOnlineTrainingHub Жыл бұрын

Thanks so much, Anthony!

@MichaelBrown-lw9kz 2 ай бұрын

The way you used the CHOOSE function was brilliant. I would like to think I would have come up with this, but I don't know. Absolutely fantastic

@MyOnlineTrainingHub 2 ай бұрын

Thank you very much! 🤩

@jeremylau93 2 жыл бұрын

The last part is amazing!! Filter and choose function work together. That’s cool~

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it, Jeremy! 😊

@stevereed5776 2 жыл бұрын

Had to watch this twice to get my head around it, mainly because I've never used the CHOOSE function. This works great so will definitely be using it. Thanks for the video

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Steve!

@JeffreyWigington Жыл бұрын

I did not know about the Choose function before! That is a really nice way to rearrange columns on the filter! I add seen another method using a double filter where the inner filter was on the whole table, but the second filter was a set of logicals [1,0,0,1] to "turn on" certain columns. It works well for removing unwanted columns, but doesn't let you rearrange columns. The choose function makes this so much cleaner. Thanks for your example!

@MyOnlineTrainingHub Жыл бұрын

Glad it was helpful!

@aviman2010 11 ай бұрын

Thanks Mynda. You've turned my world upside down (in a positive way) yet again. Love your teaching style

@MyOnlineTrainingHub 11 ай бұрын

Awesome! Thank you!

@nikkicroxall6113 2 жыл бұрын

Genuinely spent the last two days straight trying to figure out how to rearrange the columns.... thank you!!!

@MyOnlineTrainingHub 2 жыл бұрын

So pleased I could help, Nikki!

@nadermounir8228 2 жыл бұрын

Thank you Mynda for this great video and tip. Nice to use the choose function. Filter function saved me from using Index, small and row functions

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@mickaell3451 2 жыл бұрын

Hi Mynda! Thankx for the filter+choose combo. Will use it. Cheers

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@mattschoular8844 2 жыл бұрын

That's was great. Thanks Mynda. I too like the Choose option for the non-contiguous results.

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Matt!

@brianwied3702 Жыл бұрын

The last segment on discontiguous columns saved me a lot of time, excellent video!

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@ivancortinas5427 2 жыл бұрын

Thanks Mynda. Excellent review of the FILTER function. Everything is very practical and used.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Ivan 😊

@mathiasburkard2403 2 жыл бұрын

Thanks Mynda. Thats exactly what I was looking for. I love the way you teach Excel!

@MyOnlineTrainingHub 2 жыл бұрын

Awesome 😊

@ismaelkourouma5558 Жыл бұрын

Wow ! Amazing 🤩 ! I often use Filter function but I didn’t know some tips and tricks shared here. Thanks Mynda 🙏

@MyOnlineTrainingHub Жыл бұрын

Glad I could show you something new 😊

@pupycron4302 2 жыл бұрын

Literally one day ago I was trying to figure out how to rearrange columns but couldn't. Excellent timing on this video ;) What an amazing method. Thank you so much!

@MyOnlineTrainingHub 2 жыл бұрын

So pleased to hear that 😊

@josecarlosconejo5724 2 жыл бұрын

Amazing!! I knew how to get only the columns that I wanted by wrapping the FILTER function with another FILTER and a set of 1s and 0s as criteria, but I had no idea about how to rearrange the column order. Very smart use of the CHOOSE function. I use FILTER a lot at work, but with your tutorial I will take it to a new dimension. Many thanks for sharing.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@marlonmadrona1309 2 жыл бұрын

@@MyOnlineTrainingHub Which version of Excel did you use on this video?

@rusektor 2 ай бұрын

You can also use HSTACK instead of CHOOSE

@peterharbman 2 жыл бұрын

Always fun and exciting new formulas/functions and presented in an easy to understand fashion... thank you!

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it 😊

@Ahmed-fq6si 2 жыл бұрын

This is so useful and amazingly well explained as usual. Wish I could give 1000 likes to each of your videos.

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much, Ahmed 😊

@arkai59 2 жыл бұрын

Thank you so much for your amazing work as always, very comprehensive and understandable

@MyOnlineTrainingHub 2 жыл бұрын

Thanks for your kind words 😊

@rajivghildiyal8265 6 ай бұрын

Thank you Mynda, very clear and well presented. Hoping to make the time and watch more of your videos and work. God speed and God bless!

@MyOnlineTrainingHub 6 ай бұрын

Awesome to hear!

@robertoschenone9648 2 жыл бұрын

The truck for filtering not contiguous columns Is really great! The best I have seen so far

@MyOnlineTrainingHub 2 жыл бұрын

Wow, awesome to hear, Roberto!

@fr-reisid5405 2 ай бұрын

Love all of your content, extremely well explained. Have used CHOOSE() concept with FILTER() function from day 1. But to make things even more easier have used tables for source data and with INDIRECT() function refer to FILTER() function header row. When only you change FILTER() header value to existing source table valid header value job is done. INDIRECT() is volatile function but for not too big data sets works like a charm.

@MyOnlineTrainingHub 2 ай бұрын

Interesting use of INDIRECT 🤔 thanks for sharing.

@lww8132 2 жыл бұрын

This solves my immediate problem at work! Thank you so much for sharing.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear!

@karimsamnani3660 2 жыл бұрын

Thanks, Mynda! This is a wonderful formula, and you have a very nice explanation for your viewers. We all viewer like it your tutorial. God Bless you

@MyOnlineTrainingHub 2 жыл бұрын

Thanks for your kind words, Karim!

@marksant5488 Жыл бұрын

You just helped me out big time with rearranging the columns! Thank you from Prague. :)

@MyOnlineTrainingHub Жыл бұрын

Great to hear, Mark!

@martyc5674 2 жыл бұрын

Really good- I have been using filter and index to do the same, your method is cleaner if it’s just say 2-3 columns you want returned from a large data set. But index I think is cleaner if you want to Omit only a few columns from a large data set. Thanks a lot- this is great.

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Martin!

@zubairso 2 жыл бұрын

This is truly amazing! Thanks for the detailed video, will definitely give it a try.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@ravichandrankannappan6552 2 жыл бұрын

Very good way of illustrating how 'Filter' function works. Thanks 'MyOnlineTrainingHub' for teaching me nicely the Filter function. Please keep it up!

@MyOnlineTrainingHub 2 жыл бұрын

Glad it was helpful!

@jcethan2859 2 жыл бұрын

FILTER CHOOSE is my new favorite thing about O365, until the next thing inevitably takes its place. Great video! 👍

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much! 😊

@stevenlagoe7808 2 жыл бұрын

Such a clear explanation. Even I got it first time! Thanks.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Steven!

@user-ns5cq7bb2c 2 ай бұрын

Thank you so much!!!! I have a project that I can try this on and I'm sure it will save me so much time. You're always a fantastic help.

@MyOnlineTrainingHub 2 ай бұрын

Awesome to hear!

@bogdanp6092 2 жыл бұрын

Filter and Choose a powerful combination! Thanks!

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it 😊

@tomfontanella6585 2 жыл бұрын

Very clear and useful video (as always!). Thank you!

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Tom!

@helenasvensson6595 Жыл бұрын

Wonderful tutorial! So well explained and helpful! Thanks!

@MyOnlineTrainingHub Жыл бұрын

Thanks so much, Helena!

@Fabi_terra 2 ай бұрын

Thank you, Mynda! Great tutorial I learned a lot. 🤗

@MyOnlineTrainingHub 2 ай бұрын

I'm so glad!

@miiihaaas 2 жыл бұрын

Thanks for CHOOS(E)ing to show us how to FILTER and return non-contiguous columns :)

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@davidferrick 2 жыл бұрын

good stuff, never thought of using the CHOOSE option for non-contiguous column returns.

@JoelShafron 2 жыл бұрын

I use CHOOSE inside of UNIQUE to get a unique list from non-contiguous columns. Didn't think of using it here either.

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Dayve!

@MyOnlineTrainingHub 2 жыл бұрын

CHOOSE is such a versatile function. Underrated IMO 😊

@JoelShafron 2 жыл бұрын

@@MyOnlineTrainingHub Do you have a training on CHOOSE?

@MyOnlineTrainingHub 2 жыл бұрын

Please see here: www.myonlinetraininghub.com/excel-choose-function

@laStar972chuck 2 жыл бұрын

The use of the CHOOSE function in combination with the matrix notation is sheer genius ! (didn't even know one could put table in the CHOOSE "values" argument...definitely redeem the usefulness of this function in my mind now, lmao)

@MyOnlineTrainingHub 2 жыл бұрын

So pleased you liked it 😁

@wayneedmondson1065 2 жыл бұрын

Hi Mynda. Awesome examples! Love the {} trick for filling out the results table when no records are found and the CHOOSE trick to control the output format. Brilliant! Thanks for sharing :)) Thumbs up!!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much, Wayne!

@FaysalEasyExcel 2 жыл бұрын

Mynda Your simplification is awesome

@MyOnlineTrainingHub 2 жыл бұрын

Thank you! 😃

@darrylmorgan 2 жыл бұрын

Hi Mynda!Great Tutorial On The Awesome FILTER Function...Thank You :)

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure, Darryl! Glad you liked it 😊

@sukunair8918 7 ай бұрын

Thanks, literally very useful

@MyOnlineTrainingHub 7 ай бұрын

Awesome to hear! 🙏

@nonoobott8602 2 жыл бұрын

Wow. This is very useful and so explicit. Thanks so much for sharing

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@utubeAgape 2 жыл бұрын

So EXCELLENT! Thank you for another useful video!

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@NiteLynr 2 жыл бұрын

I think it says something about me when you said 'I hope you're excited to give it a try" and I thought "Yes, yes I am". Also, half-way through was thinking about a list of people who'd find great uses for it to share this with. Excellently presented as always, missed this one when it was released because we hadn't made the switch to 365 at the time.

@MyOnlineTrainingHub 2 жыл бұрын

So pleased you can make use of it :-)

@ChristosLefkimiotis 2 ай бұрын

It cried for choose and offset with match (for the headers) makes it more variable, Nice video

@MyOnlineTrainingHub 2 ай бұрын

Glad I could help 😊

@henryhache5509 10 ай бұрын

thank you! They removed my choosecols() function in an update, so i needed this!

@MyOnlineTrainingHub 10 ай бұрын

I'd update again, because CHOOSECOLS is still available. Sounds like you may have had your version rolled back somehow.

@shoaibrehman9988 2 жыл бұрын

Super uses of filter function, thanks gr8 video.

@MyOnlineTrainingHub 2 жыл бұрын

Thanks, Shoaib!

@xinliu7379 2 жыл бұрын

So powerful ! Thank you!

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@Luciano_mp 2 жыл бұрын

Great, i liked Filter. Thank you Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Luciano! 😊

@Luciano_mp 2 жыл бұрын

@@MyOnlineTrainingHub For you too!

@vgb311 5 ай бұрын

Great! Thank you for the video step by step.

@MyOnlineTrainingHub 5 ай бұрын

Glad it was helpful!

@zaighamuddinfarooqui1705 2 жыл бұрын

Excellent. Thanks for such a great lesson👍

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it 😊

@sjn7220 2 жыл бұрын

Still used to Advanced Filter but may have to start using the filter function more.

@MyOnlineTrainingHub 2 жыл бұрын

FILTER is way better than advanced filter because is evaluates when the source data changes, whereas advanced filter has to be run again.

@jiajiaphotography 2 жыл бұрын

Wow, the last trick is really unexpected. Cool

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it!

@marywise5943 2 жыл бұрын

This is great! I can think of several uses where I can apply this filter method. I would also love to see how you would select the columns based on a value example of something like

@MyOnlineTrainingHub 2 жыл бұрын

Yep, that's easy to do, Mary. Just reference the value column and apply the logical test in your criteria arguments of FILTER. If you get stuck you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@marywise5943 2 жыл бұрын

Perfect! Thank you!

@garyevans7620 2 ай бұрын

Excellent lesson, I learnt a lot, thank you. One small point. Parentheses are both brackets ( and ). So, a bracket is ( or ) and parentheses are () 🙂

@MyOnlineTrainingHub 2 ай бұрын

In Australia and the UK (and probably elsewhere) this ( is a bracket and these () are brackets. My understanding is in the US this ( is a parenthesis and these ( ) are parentheses. Usually the term bracket/s are reserved for square brackets [ ]. But I’m not American, so feel free to correct me if you are 😁

@redhaakhund1271 Жыл бұрын

Amazing... Thank you soooo much!

@MyOnlineTrainingHub Жыл бұрын

You're very welcome!

@garyallan69 2 жыл бұрын

Love the Non - Contiguous columns. I’m now starting to use Choose for the first time in my life LOL

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Gary!

@aivie1900 Жыл бұрын

omg. this is what I am looking for. thank you so much.

@MyOnlineTrainingHub Жыл бұрын

Wonderful to hear 😊

@anv.4614 Жыл бұрын

Thank you, tutor. great lesson.

@MyOnlineTrainingHub Жыл бұрын

Glad you liked it!

@truewiking Жыл бұрын

Great explained, solved my problem, Thank you very much

@MyOnlineTrainingHub Жыл бұрын

Glad I could help 😊

@GeertDelmulle 2 жыл бұрын

I vaguely remember there is another way to select columns, something with a string of ones and zeros, but this choose trick is far more elegant and allows for sequencing the columns exactly the way you want. Somehow this choose trick reminds me of the xlookup function.

@MyOnlineTrainingHub 2 жыл бұрын

Yes, you can use FILTER in FILTER like this to return the 1st, 2nd and 4th columns: =FILTER(FILTER(B11:F19,B11:B19="Sales"),{1,1,0,1,0})

@GeertDelmulle 2 жыл бұрын

@@MyOnlineTrainingHub Indeed, but that trick reminds me (as it did before) of the vlookup function: if the composition of the table changes, this trick will produce the wrong results or break down. That’s why I like the choose trick better: more robust.

@Giovanni-vx8xl 2 жыл бұрын

you are amazing and a super teacher and beautifully of course :) Thank you for sharing

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it, Giovanni!

@zainululum 2 жыл бұрын

thanks for clear and shaep tutorial as always

@MyOnlineTrainingHub 2 жыл бұрын

You are welcome!

@sachinrv1 11 ай бұрын

Excel in Office 365 has much more to offer than one can imagine. Wonderfully explained :)

@MyOnlineTrainingHub 11 ай бұрын

Glad you think so!

@philippemartinemorel1399 8 ай бұрын

Thanks for the very clear explanations ! I used the OR trick to add my initial table headers to the filtered result. Nevetheless it seems impossible that the filtered result becomes a table. Am I wrong ?

@MyOnlineTrainingHub 8 ай бұрын

Great to hear it was helpful! Correct, FILTER cannot be placed in an Excel Table. Dynamic arrays are not supported for Tables.

@vitruvian_man 2 жыл бұрын

Good stuff. Thanks Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much 😊

@hamidsh4789 2 жыл бұрын

Thanks... keep on excellent tutorials...

@MyOnlineTrainingHub 2 жыл бұрын

Thanks, will do!

@stephenmascarenhas1987 3 ай бұрын

Well presented with some intricacies in the formulae's

@MyOnlineTrainingHub 3 ай бұрын

Thank you 😊

@benign39 2 жыл бұрын

So useful and helpful. Thank you.

@MyOnlineTrainingHub 2 жыл бұрын

Glad you’ll be able to make use of it, Jose!

@SamPandiangan 2 жыл бұрын

This is another great tutorial, as usual.

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Sam!

@spilledgraphics 2 жыл бұрын

woah! great tip on minute 7:22 Mynda! 😁👌👌 thanks Mr. Excel !!! 🙏

@MyOnlineTrainingHub 2 жыл бұрын

Cheers! Glad you liked it 😊

@jobygeorgev06 3 ай бұрын

Thanks for making our life more easier

@MyOnlineTrainingHub 3 ай бұрын

It's my pleasure 😊🙏

@daliladuf 2 жыл бұрын

Amazing, as always!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks, Dalila!

@pvrameshbabu 2 ай бұрын

Thanks. It's a very useful function.

@MyOnlineTrainingHub 2 ай бұрын

Glad it was helpful!

@ademkursatkaracil 2 жыл бұрын

Another great video. Thank you Mynda. As far as I Know FILTER function available for Office 2021 also besides Office 365.

@MyOnlineTrainingHub 2 жыл бұрын

Yes, it should also be in the new Office release.

@chrism9037 2 жыл бұрын

Awesome video Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much, Chris 😊

@_sootyfoot Жыл бұрын

I had no idea this was possible in excel, you've saved me about 30mins every Monday! 😂

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@spoulis19 2 жыл бұрын

Thanks for that great tutorial. Have you a lesson that handles the situation of filter a list based on criteria on a range list. Or a name range? Even better incorporate a filter function in a sumproduct in order to sum a table based on a criteria range? In one or more columns?

@MyOnlineTrainingHub 2 жыл бұрын

Hi Spiros, FILTER can't take more than one item in the logical test i.e. it can't do something like this: =FILTER(B11:B19,B11:B19=J11:J12) You would have to do this instead: =FILTER(B11:B19,(B11:B19=J11)+(B11:B19=J12)) You can certainly wrap FILTER in aggregation functions like SUM. Why don't you have a play around with it and see what you discover.

@sureshrouniyar3482 2 жыл бұрын

It looks awesome.Would you filter the result including header instead of manually typing for header.

@MyOnlineTrainingHub 2 жыл бұрын

If you include the header in the filter range it probably wouldn't satisfy the criteria and if you want to sort the data, then the headers will be somewhere random in the resulting data.

@tommyharris5817 2 жыл бұрын

Thanks for another great video. My question is can you do all this with Xlookup?

@MyOnlineTrainingHub 2 жыл бұрын

No, XLOOKUP doesn't return multiple columns and rows. XLOOKUP only spills one way, either across or down.

@Seftehandle 6 ай бұрын

I am impressed great job

@MyOnlineTrainingHub 6 ай бұрын

Thanks so much 🙏

@gilbertosegoviano7701 Жыл бұрын

You are amazing 😻 . Thanks 🙏

@MyOnlineTrainingHub Жыл бұрын

Thank you! 😃

@manissery1956 Жыл бұрын

Really fantastic tips

@MyOnlineTrainingHub Жыл бұрын

Thanks so much!

@angebeda6739 11 ай бұрын

This video is great, really helpfull. Do you know how to SORTBY the output result please ?

@MyOnlineTrainingHub 11 ай бұрын

Glad to hear 🙏 SORTBY tutorial here: www.myonlinetraininghub.com/excel-sortby-function If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@GeorgeAJululian Жыл бұрын

Many thanks helpful information

@MyOnlineTrainingHub Жыл бұрын

My pleasure, George!

@GeorgeAJululian Жыл бұрын

@@MyOnlineTrainingHub Please I need your help, the formula works sooo good but is it possible for the table header to show in the result of this formula =FILTER(INDEX(A3:G25,SEQUENCE(ROWS(A3:G25)),{1,2,3,4,6,7}),(F3:F25>0)*(G3:G25="Credit"))

@vebe7109 2 ай бұрын

Great examples! Very insightful and detailed! Thanks! Would like to how can apply the examples presented if the data is located in tree or more sheets( 1 mill per sheet)? I am using nested xlookup- hoing to find a fater way?

@MyOnlineTrainingHub 2 ай бұрын

Thank you! I would use Power Query to consolidate the data in the 3 sheets and then do your lookup. Way more efficient. www.myonlinetraininghub.com/power-query-consolidate-excel-sheets

@dougmphilly 2 жыл бұрын

FILTER UNIQUE and SORT - learn it, love it, live it

@MyOnlineTrainingHub 2 жыл бұрын

Definitely, Doug :-)

@yvlove77 2 жыл бұрын

Thanks Mynda! Great tutorial! I am a huge fan of the filter function - I use it pretty much for every report that I run now. I am curious about the extra parentheses, though. It hasn't been my experience that you need to enclose the criteria in another set of parentheses: ((B11:B19="Sales")*(C11:C19="Stamps")) - it has worked for me this way: (B11:B19="Sales")*(C11:C19="Stamps"). What is the reason the extra set of parentheses is needed? Thanks again!!

@MyOnlineTrainingHub 2 жыл бұрын

Probably just habit for me from using it with SUMPRODUCT!

@deepakrtweets 2 жыл бұрын

Thank you very much, This made my day

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@parahiamin6765 2 жыл бұрын

I have not used the FILTER function yet, however, I can see where it could be really useful. Great tutorial as always! PS "It's not case sensitive but I like to be proper" I do too, it's a pet hate of mine when people aren't and it's really annoying as it only takes a second!

@MyOnlineTrainingHub 2 жыл бұрын

😊 Glad you enjoyed it, Parahi!

@MaydayAggro Жыл бұрын

I now use FILTER, UNIQUE, and SORT more than any other functions because they are so useful, especially when used together. (They can create an impromptu range acting as a PivotTable that auto-updates as new data are added to the source and is much easier to reference than the PT in subsequent formulas.)

@oracle998 2 жыл бұрын

wow, great function, may i know how to set the filter can select more than one department?

@MyOnlineTrainingHub 2 жыл бұрын

Glad you like it! If you want OR operators in the FILTER criteria you use + between them e.g. =FILTER(range, (criteria1)+(criteria2))

@minamishail_Tech2024 4 ай бұрын

Really good, what if I need to filter a specific column based on a list of data or a named range where it's value change from time to time ?

@MyOnlineTrainingHub 4 ай бұрын

Glad you liked it. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@ShyamAnand24 2 жыл бұрын

Thank you. Great to know filter function based on data validation on various department. Can we include data validation list to select particular column (drop down list for Item, quantity and price).

@MyOnlineTrainingHub 2 жыл бұрын

Not sure what you mean, Shyam. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum