Don't Use Excel Filters! Use This Incredible Excel Formula Instead ...
ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- Are you using filters in Excel?
👉FREE: YOUR EXCEL CHEATSHEET DOWNLOAD👈
www.tigerspreadsheetsolutions...
There is no doubt filters can be useful for data analysis in Excel, and they are often one of the first techniques beginners learn. But, they can be ‘onerous’, to say the least!
💻DOWNLOAD FILES LINK💻
tinyurl.com/yrk3h9nv
What about the excessive number of clicks required to get them working? Clicks to select the dataset, to apply the filter, then to configure whatever criteria might be required. Oh, and to change the criteria too!
All this left me thinking, ‘There must be a better way …’
My Excel ‘Secret Weapons’ are tools that few seem to talk about, but that deliver ‘next level’ analytical power. To this day, I still don’t understand why more people don’t use them. Perhaps they are simply not fashionable. I have used them dozens of times on real world projects to help people get the analyses they need ‘at the click of the button’. Time and again I have seen my customers’ jaw drop when they see what is possible …
What if I told you that one of my ‘Secret Weapons’ allows you to filter data … using an Excel formula? Rather than clicking through the filter menus, we might be able to enter a value in a cell to get the answer we need … and quickly change that cell for a different analysis. Sound good?
Don’t get me wrong, this formula is not easy to set up. A degree of preparation is required. We have to set up a ‘criteria’ area where we designate the columns by which we want to filter, and the values we want to filter by. And there are other considerations, too.
👉FREE: YOUR EXCEL CHEATSHEET DOWNLOAD👈
www.tigerspreadsheetsolutions...
Perhaps this is why many people give up with DSUM. It takes some precision to set up it up. I point out a few of the pitfalls in the video - column header names must be entered with 100% precision, for example, with a single spelling mistake messing everything up. Oh, Excel!
So, are you up for the challenge? I take you through it step-by-step in this video, so go ahead, download the file and work along with me.
Not only will DSUM (and its friends DCOUNT and DAVERAGE) allow you to stop using filters for good, but it might just open up a world of possibilities for your Excel data analyses.
Let me know how you get on. - บันเทิง
🔥WATCH NEXT: Another Powerful Excel Formula:
th-cam.com/video/O56lIznjYdg/w-d-xo.html
Hello Tiger... What does "DSUM" stand for?
CTRL + SHIFT + L to enable filters, you don’t need to select the whole range either
Yes, usually i am go on that method
Yes but if for some reason there are empty rows in your table, the data after won’t be taken into the filters if you don’t select your entire table first.
@@aynino agreed, if you are using the filtering to cleanse the data
I like it!
What about the shortcut key to remove the table?
Ctrl Shift L enables filters in all columns without having to select the column.
Alt ⬇️ drops the filter menu, pressing E after that takes you directly to the search box.
Not exactly a replacement though, like with all analytical issues, it depends on the scenario you're testing.
Absolutely
Thx
Or just click on one of the headers then enable the filter?
Thank you for this instructive presentation. I plan to experiment with DSUM instead of filters.
Wow! That was a great help. Thank you so much, man! You saved a lot of my time
Great to hear Abdul - nice job!
I can’t wait to checkout your Excel Cheat sheet. I just found your channel and subscribed. I need to become proficient at work with Excel. Thank you this will be so helpful!
Welcome to the community!
I really appreciate your video training which is simple and comprehensible.
Thank you my friend - do check out the other videos!
Appreciate your patience in explaination. Made it easy.
Thank you and welcome to the channel!
I love stumbling across excel cheat sheets, thank you :)
Welcome to the channel, Bryan!
I just learned some amazing shortcuts and tricks that I know I will use often. Well earned subscribe my man. Thanks!
thanks Travis and welcome to the channel!
Another great prospective, thanks a lot for sharing..enjoy your presentations.
You're welcome, Pete! Thanks and do check out the other videos on the channel.
HA! I was expecting something 'new' but this is a blast from the past....I'm happy to see your audience so excited. I've been using DSUM, etc. since before Excel had filters. Before Pivot Tables were in Excel, I used to build pivot tables from 100,000 lines of data using five or six criteria DSUM, DGET, DCOUNT, DMIN, etc.
Happy Mikey likes it too! Welcome ...
Excel 2.0
Love this formula! It's like a simplified form of the SUMIFS formula 😊
I couldn't agree more - thanks!
Many thanks - another piece of learning for me !
Good to hear, Rob!
Never knew this formula before, great tool, thanks.
I hope you get value out of it ...
Wow! That's great. Thanks as always.
Thanks Peter! Do check our other videos ...
Thank you for the video Mr. Chris
Thank you, my friend!
Excel-lent short tutorial! I love Excel!
👏👏👏
Just found your channel. Excellent tutorial! Thanks for sharing this Gem! Subbed.
Welcome, my friend!
I'm giving up on filters. Cool instructions.
Welcome to the channel!
Chris, first time viewer, excellent video. Thank you
Thank you and welcome
I love creating userforms and using VBA. I am going to start saving my favorite formulas and this one is pretty cool!!! Thanks a bunch!!
You're very welcome MIke - we cover lots of cool formulae on the channel, enjoy!
VBA is almost never needed. The worksheet is more powerful than most people understand
@@nigeltufnel4031 - It has not failed me yet. I am learning typescript too and I love Python...wait, I shouldn't enjoy it ? I am confused by your lack of contribution to my post.
@@michaelthomashill never said you shouldn't enjoy it. Have fun
Chris I have been using VBA for years, written some complex automations and everytime I click one of your videos I learn something new. This is great stuff, thank you
Great to hear Christiaan!
Really useful and some great tips, thanks! I usually use sumif(s) and reference criteria cells so may try a comparison.
Thanks John - I will put a comparison video together someday, given that many viewers are comparing to SUMIFS. Thanks for the comment!
Incredible technique!!! Thanks alot Sir for sharing this :)
Welcome, Hassan!
REALLY AMAZING channel, why I find it so late...
Thank you.
Welcome - do you check out the other videos!
@@TigerSpreadsheetSolutions , sure, i need more time, checking one by one... really useful!!!!
you are so great!!!!! helps me a lot.
you are the excel guru!
Very helpful, thank u so much.
Thank you and welcome to the channel!
DSUM can be really useful... didnt knew about this formula before, thanks for the explanation.
You're welcome Faiz - I hope it helps you!
This looks perfect for my database, got a few ideas how to expand on it. Thank you.
Will look to re-sub again when time allows!
Great to hear, Craig!
Just discovered this video / channel. Awesome stuff, thanks!! I might reflect on this and change my view, but my first thought is that VSUM is very similar to Sumifs or an array formula, where you build the search criteria into cells that the formula references to, rather than into the formula itself. Cool stuff though, and I’ll definitely give it a try.
Hi Iain - yes, it's certainly similar. But, there are many ways to perform this function in Excel. DSUM is my preferred way because of the conciseness of the formula. Others may agree or disagree, try to form your own view over time - good luck!
After 'Alt+Down Arrow' key, if you press 'E', you reach directly to the search box instead of going down using down arrow key.
I love this one ❤
Thank you!
What a great video! Thanks for sharing!
Thanks Frank and welcome!
Thanks!!! It is very useful!
Welcome to the channel!
I’m a kid in a candy store. Keep sharing these please!
Welcome, Leonardo!
I second this.. if only I could find a video simplified as this to have my order guide spreadsheet automatically update the prices of products at the same time as my venders site online does. For example, I order all food for my restaurant from Reinhart food service. Which is online and I have to log into my account to place orders and or get current prices on every ingredient I have in my inventory. Making me have to update every item manually which with over 500 items is VERY time consuming. However it is worth it seeing soon as I update all my costs per ingredient/item I have it set to automatically update what my cost is per menu item/recipe as well as what my food cost is based on my menu pricing based off of whatever I decide to have my markup set as.
Nice video. I liked and subscribed to get more of these in my recommendation
thank you and welcome to the channel!
Nice one. Great tool that is. Thank you
He lives! Thanks Rik and welcome to the channel.
Awesome video bro!!!
Welcome to the channel my friend!
Interesting. I've used DSUM a few times but never really been too keen with how the criteria are defined and prefer the more interactive method of getting the filter output to match the results cells. I've tended to use =SUBTOTAL(... ,9) to add up what's visible at the time, or alternatively use array formulae in the style of {=SUM(basevalues*(filtervalues=filtercondition))}, where anything that matches produces a multiplicand of 1 and anything that doesn't, a multiplicand of 0, so only basevalues in line with a matching condition on the same row get multipled by 1 and added.
Hi Mark - thanks for the ideas. My objection to subtotal is that you have to go through the filter menu to make it work. That's time-consuming ...
Very nice Sir. Thanks a lot
Welcome to the channel Prakash
Amazing! Will come again.
See you again soon, Paul!
Thank you sir. Please explain how to use standard formula for totalling date-wise debit and credit columns and balance in next line, like cash book in tally.
Nice explanation, but i am still prefering sumifs, even if with harder syntax. Because i normal want to results for more variants, problem of sdum is the header of criteria, you have to have criteria value directly below the header, you cant tou use one criteria table with more rows... For more variants on one shot you have to use every time new criteria table for each dsum (you cant to have only table of criteria variants)
You should check out using the SUMPRODUCT function for trying to sum when there are numerous criteria. I used to use sumif quite a bit but eventually my "criteria" ran into 5+ "ifs" and SUMPRODUCT just became easier to write.
When you first started, I was like this is stupid..I love filters. But at the end I ate my words. Great video.
Many thanks, James! Do check out the other videos ...
@@TigerSpreadsheetSolutions I plan to watch them all, they are a bit long winded for me but they are awesome! Glad I found you!
James Pyle x2 speed? Good luck!
@@TigerSpreadsheetSolutions Haha maybe so!
Nice video. Thank you :)
It's a pleasure Rejhan and welcome ...
Thank You!
You're welcome!
Thanks, that made sense
You're welcome - please check out our other videos
Whoa....I have to let that soak in a for a minute. I have done similar in the past, but it has been a complicated group of "SumIf"s. This looks much better!
Great to hear, Dan!
Loved it.....👏👏👏🙏👍
Thanks for watching 🙏
Wow...!
Enjoyed watching this. I'll use this method going forward.
Thank you.
This is amazing.
I agree!
good job!
Thank you!
You're welcome!
I find Alt, A, T is bit of a handful to press sequentially. Someone showed me Ctrl+Shift+L to do the same thing, no need to get the sequence right, just mash the keys at the same time and it's toggled the filter.
Also press E once you're in the filter box to get the cursor in the filter text box straight away.
Solid tips!
that last tip about pressing 'E' could save lives ! ty
Nice solution will use it. One observation you were labouring away at filter set up. In windows PC, assuming you have a proper data set, shortcuts ctrl + shift + l (L not case sensitive) for filter handles on the top row or ctrl + t for conversion to a table with automatic filter handles. I know the point is to not filter but some time you need them. 👍
'laboured' = best adjective for describing my videos :-) thanks for the tip but I generally avoid Excel tables
nice tutorial. good job. thanks
Thanks Ade, and welcome
This is really amazing😳
Thanks Sherwin and welcome
More than one way to skin a cat. This seems pretty nifty and would become more useful, the greater the number of different fields in your criteria.
If you wanted to see all teams side by side, you could create a "helper" column to test if the 1.54 was true and then insert a pivot table to sum up the desired value by team where the helper column is True. Super quick to setup as well.
If you want to use pivot tables that is ... thanks for the comment!
Never heard of this. I use a fancy index match array formula to live filter. You can do what your doing with sumifs though.
True, but not as elegantly as with DSUM
For show / remove filter, go to first heading and type ALT, D, F,F all one after another, not together. Many ways to do one thing.
When you changed the name and the total changed looked like magic. Need to understand how is it doing calculation and is the order of the content of small 4 cell table important.
Very powerful formula. Thank you
Thanks Sujal - correctly set up, it does feel like magic. I hope you enjoy this one!
Never heard of this! Well done . Subscribed
Welcome the community!
Now that's working smarter and not harder :) Thanks for sharing !!👌
Thank you and welcome - do check out the other videos!
Nice work
Thanks Rajesh and welcome to the channel
Thanks, i have been using sumifs for years in which suits perfectly fine until now.
Great to hear - for me, DSUM is different and easier to use
"It's so tiring, it takes so long, so many mouse clicks" :) then takes no mouse clicks to set all filtering data. in 1 minute and 3 seconds.
Then you need, to add fields in cells, setup the formula, be aware that everything is exactly correct. in 5 minutes (without the exra explaination it woudl still be longer and more effort).
Yes DSUM is powerfull, but dont say its a replacement for faster way of using filters. The one has nothing to do with the other.
It's a replacement for using filters to sum filtered rows, as I explain in the video. Thanks for the comment!
Crtl+A is a great alternative shortcut to the ctrl+shift+right then ctrl+shift+down that you were using to select the data you want filtered. Also, you don’t have to have the top left most cell selected, it could be any cell within the dataset.
Another great alternative is ctrl+shift+L to enable filters vs the alt+A then T. This one is minor but it’s a more direct command. You can actually see the difference in speed if you compare the two.
nice shortcuts!
I'd normally use pivot table or subtotal. definitely will try dsum next
Highly recommended...
Also, countifs is pretty similar.
@@joejj6251 You can use DCOUNT for counting, just like DSUM
Thanks 👍👍
You're welcome - do check out the other videos on the channel
You're doing the long way....just right click on the data and filter by selected cell, it auto adds the filters and all....cheers mate
Thanks ... you did watch the rest of the video, right? :-)
Great Video.. Thank-you. However you should note that the column that you wish to see the Sum (i.e."J" in your example) must be formatted as "Number" else your results will be in error. :)
I'm this situation I typically use a sumifs formula with reference ("*"&cell&"*") to text contained from my desired cells. This gives me a lot of flexibility in how i use the drag features to build a reference table.
For example I can pivot out a complicated patient interview, then sum or count mentions of topics that I'm looking for depending on the variables.
This seems like a similar solution? Can you go over how this formula differs from sumifs?
It's similar but I prefer the shorter formula for one thing. I will do a video on the differences one day ...
As an Access & Excel Developer, it's interesting to see the DSum() function in Excel. However, I'm curious as to why use it when a SumIF() or SumIFS() can accomplish the same thing?
It depends on your mindset.
If your mindset is:
'I have a tool to do that, I don't need another one'
You might not get value.
If you mindset is:
'I need a range of tools so I can pick the best for the job'
You'll get value, even if you know SUMIFS
My experience has driven me towards mindset 2.
In terms of direct comparison, I prefer the conciseness of DSUM vs. SUMIFS.
The only advantage of DSUM what I can see is that it specifies the criteria header by the content of it thus searches for it automatically. If the column order of the database would change then the formula does not have to be updated, while in case of SUMIF if a column would be placed elsewhere next time then you have to update the formula. It is quite minor advantage for me though as database column order changes are quite rare in my practice, thus I also stick to SUMIF :)
I thought I knew Excel until I started looking for help on TH-cam. Good stuff.
Welcome to the channel, Mickey!
CTRL+SHIFT+L to filter and you can be anywhere in the data set
ALT+DOWN then E to search, and with dates you can go into the year, hit RIGHT to expand..
Nice ideas, thanks
Very convinient )))
Thanks, Jasper!
kinda like power pivot, sumx nested with filter, like it, thanks.
I love this one ...
Nicely done... new subscriber!
Welcome, Norman!
@Tiger Spreadsheet Solutions - Thanks for your video! How would you approach using DSUM to sum all but beagles and dachshunds as home breeds? How would you approach using it to sum over only those two breeds? I'm assuming you would have multiple instances of the "Home" column in the criteria range, but thought there may be some way to combine them.
Michael - the Microsoft resources say that 'OR' logic is possible with DSUM by adding more rows to the criteria table, and adjusting the criteria range to include them. So, you wouldn't need a repeated column header to do that, in theory. I have never got this working consistently, however.
You can use repeated headers for 'AND' logic.
You can something like "Text" to exclude certain values.
Good luck with it!
Array formulas or pivot table
Pretty nifty for What If scenarios. It's a pity that the criteria box trick doesn't work for formulae like filter and sort.
I'm not sure Mike - perhaps you could develop this mechanism to something more sophisticated, and incorporate the above functions ...?
First of all I wish to thank you. This example shows a standard "AND" condition filter. But there's also the advanced filter which allow us combining "AND" and "OR" conditions. Is there any function like DSUM in this situation? Or maby the SUMIFS function is the only way?
Thanks
I have read that DSUM allows OR operations - but I have never got this working personally ...
That’s great. How would you use wildcards in the criteria?
Thank you - various options are possible, check out Microsoft's learning resources on this formula
I use Numbers’ Categories in Mac/iPad, instead of Excel, for files the heavily use Filter. Much much better.
What aspects are better in your view?
Thank you, great video! What if there are multiple criteria under HOME?
Thanks Sandra - I would say check out Microsoft's resources on this formula, I understand this is possible but I have never got it working consistently myself. Good luck!
You can use a nested SUMIFS formula:
=SUM(SUMIFS($T$11:$T$711,$C$11:$C$771,{"Beagle","Poodle"},$J$11:$J$771,">1.54")).
If you select a cell within your data range CTRL+A will select the current region, CTRL+T turns it into a table (in my example I named it MyTbl)
=SUM(SUMIFS(MyTbl[Return Home Wins],MyTbl[Home],{"beagle","poodle"},MyTbl[1],">1.54")).
2 issues with DSUM:
1) DSUM doesn't recognize tables correctly
2) According to Microsoft from Excel 2007 you should avoid using DFUNCTIONS and use their counterparts SUM/COUNT/AVERAGE(IFS)
Link docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions
Woww never heard anyone talking about this , forget about using..
❤️❤️
Welcome to the channel, Amit!
I can see the power of this but the problem showed could have been solved by a sumifs statement. The question I have which is better in terms of processing speed. I know that the columns being interrogated can be changed easily but you can do this with dynamic ranges and use of indirect. I can see this being really beneficial in VBA though
Great question - I do plan a follow-up video, I would like to know about efficiency vs. SUMIFS too. Watch this space!
Because the only time you use filters is when you want to sum certain rows 🤔 (and in those cases you could use SUMIF).
Personally I find DSUM much easier to put together. Thanks for comment!
Hi, Chris! I was wondering if there's a way to automate an excel file that will allow me to ploy training classes for a bpo company?
Hi John - with VBA the only limit really is your own creativity
Omg... i just know it. This was a year ago... so much helpfull this formula.
Great to hear - I find it to be the most useful Excel formula for data analysis ...
This could be handy. Is there a way to apply multiple values for a given criteria? Also, if you format your dataset and criteria as a table, you can reference something like this:
=DSUM(tblResultsData[#All],tblResultsData[[#Headers],[Returns Home Win]],tblFilterCriteria[#All])
This reference style makes the formula a bit more dynamic and less error prone, as you can leverage the auto-complete functionality that comes with tables.
Jason - the Microsoft resources say that 'OR' logic is possible with DSUM by adding more rows to the criteria table, and adjusting the criteria range to include them. So, you wouldn't need a repeated column header to do that, in theory. I have never got this working consistently, however. Great that it also works with the table notation though I personally try to avoid tables. I use INDIRECT or similar to dynamically define the data range.
@@TigerSpreadsheetSolutions Yes, that does work. It is a little nuanced though in that, if one of the rows in the criteria table is null, it will sum the entire table's results. I suspect there's a logical reason in the documentation, I'll need to take a look. But, the formula does work and the criteria auto expands with the table, which is nice. You just have to be mindful not to have a blank row. Out of curiosity, why do you avoid tables?
great!
Welcome!
In my workbook I've been working on I use it to double as an inventory guide as well as an order guide. To get total $ on hand broken down between liquor, beer, and soda I use very similar formula which is a sumif. It is =SUMIF(Sheet1!$k$4:$k363,"beer",Sheet1!$J$4:$j$363) then a cell for liquor and Pepsi as well. Can you tell me the difference between sumif and dsum?
Hi Tyler - DSUM does the same thing with a shorter formula
For small jobs, Filter. For large jobs, VBA. For a time-killer while waiting patiently for a phone call, or an email, or a pizza delivery, DSUM. Makes sense to me. But that's just me. And I'm the guy that has to work with me, every day.
Why do use VBA for large jobs? I thought it was less efficient than excel formulas
I would say VBA and formulae do different things - formulae for data analysis and modelling, and VBA for automating processes
Nice. I work on large files and I see a lot of possibilities opening up.
Good to hear Kevin - hope it helps!
Use Ctr + Shift + L for filter on / off on selected areas, where the top row is your header.
Love these shortcuts!
I usually do SUMIFS or INDEX-MATCH... that way I can view multiple filter sets at the same time (derive each filter set as its own column next to the existing data), and then alt+e+v if I do want to view (filter data) just one filter value at a time.
I don't like adding rows or repurposing columns (at the top of the worksheet) just for filtering (especially when you'd need to add multiple columns up top to add multiple filters on a single source column...sloppy and inflexible)
..but for a straightforward "worksheet UI" this method is perfect!
Good stuff - you could put the DSUM formula on a different sheet and build a 'Dashboard' there
Well, I used to use DSUM a lot in the 90's, but then when SUMIFs came up, I realized it was much more simplified with the SUMIF's. You can still use the variables as criteria like this: ">"&1.54
This is the simplest formula. I've it. This is the best than dsum.
make sure it's ">1.54"
Hi, Professor, you have provide in a lot of effort for these videos, thank you, I have a question if we have for example in cell "a1" the number 10.00 m, how to have this number with the same format in cell "b1 "using the text function or some other function, thank you very much.😃👍
I was going to suggest the TEXT function. Basic cell formatting?
When navigating down the autofilter options you can just press E to go straight to the text box to type in your filter.
I like this one!
Hi I need one on one connect on one of my requirements in Excel form creation and backend database to run reports
Please email in if you'd like to discuss projects
Harika ❤️
Have you tried advanced filter? That usually works faster and neater than DSUM or filters.
Will check it out ...