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.
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.
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.
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
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.
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 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.
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.
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!
"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.
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..
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
I'm an excel power user, filter now and then but DSUM is much more useful for repetitive analysis or repetitive data sets where you know the structures, common filters etc. This system analysis is more common than people realise!
Yeah, but what is you actually want TO SEE THE DATA - and not just see the output, which is the sum, count or average. If you are filtering only to see those basic stats, then that is such an inefficient use of filtering. Filtering has other purposes.
@@JJ_TheGreat Agreed - but generally, when you're dealing with data you know your way around or testing, the outputs from a high level are more important before you *consider* whether or not you might do any further analysis and need to see the data. The comfort of seeing the data or the feeling to need to see it doesn't mean it needs to be seen :)
Elegant. You can also do this using Boolean logic and Boolean math. I do not know whether this works with table references. It likely does. The equivalent form in Boolean would go like this. For convenience, let’s call the results data column array “results”, and each criteria data column ‘array1’, ‘array1’ …. And each criteria ‘criteria’, ‘criteria’, …. Then the formula becomes =sum((results)*(array1=criteria1)*(array2=criteria2)) … I used “=“. Use the appropriate evaluator “=“, “” …. How this works is as follows. The whole formula uses matrix math. Sum((A)*(B)*(C)). The Boolean part mixes matrix math with a Boolean test. E.g. (array1=criteria1). For each instance in the array the test is carried out and the array becomes a truth table of values. A “1” for each true instance, 0 for false. Multiplying these amounts to asking are they all tue. Sums would ask if any are true. In the Boolean array math represented by an equality equation inside parentheses, any value equates to 1, so simply putting the whole in parentheses becomes an array of ones and zeros. Then multiplying that using matrix math times the array with the values desired becomes an array having only those results matching the criteria. Apply a simple sum (or other formula) to that yields the desired result. You can also easily find the most recent date that has a value in a table of dates this way. Say column A is the dates. Column B is the value desired with some having values and some being empty. The most recent date which has a value is then =max((ArrayA)*(ArrayB>0)). This is difficult to do any other way. Where dsum() is limited to conditions where all criteria are met, Boolean matrix logic math can handle vastly more complex criteria conditions. In this it is equivalent to ladder logic (or relay logic) as is used in programmable controllers - expressed as an equation rather than as a ladder logic program. Btw - to return the most recent value in my date example you can use xlookup or vlookup using the max equation as the criteria. Or you can feed that back to another formula. Say the result of the max equation is called ‘maxresult’. Then the latest value =(Array1=maxresult)*(Array2). Simple. I should add…. This works provided that none of the array values trigger errors. That can happen if array values cannot be evaluated. E.g. they cannot be tested, they aren’t numbers …. The formulas can be rewritten if this is possible in the data arrays to test for or convert such errors. Unfortunately excel does not make that as simple. For each instance in this equation Result = (Array1=value)*(Array2) … it my be necessary to add testing conditions such as for Array1 …. Substitute something like IF(iserror(Array1=result),0,(Array1=result)). However, care is required in thinking through the logic to ensure that the results of the test don’t themselves trigger errors. And that can complicate matters. The simple way to ensure this does not happen is to create a smaller test worksheet with fake data which include the various possible conditions (values, text, blanks, =na(), =1/0, =1+qqqq, which will trigger the potential errors. Doing this allows you to see what various data anomalies might do to the formula results. You may then also need to set up a resizes of columns on this test data to evaluate each of these individual cases to figure out what logical changes are needed to overcome Excels limitations.. once you know which formulations will overcome Excel short circuiting to Error results, you can then change the formula for the array/Boolean math tests. This is less simple and begins to look more like a program than a math equation.
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?
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
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. 👍
CAN YOU Tell ME ? How to make videos like this? Are you using a single software or you record screen and video separately then merge them? I would be very grateful.
ı have a question. we can bring a information from other file with vlookup according to one variable. but, whatıf ı want to use two variable to bring this information? which formula should ı use? thank you.
Hi Ibrahim - there are a few different approaches, but none of them easy. I would probably use VBA to do this - we have many beginner videos on the channel if you'd like to learn.
@@TigerSpreadsheetSolutions Yeah, you are correct. And this DSUM is not very commonly used, so it is always good to have options, as long as the end result is correct. 😀
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?
Hi - I have a spreadsheet with 13 sheets. Each sheet has a table which tracks weekly data. Each week I manually add a new row to the top of the table for the newest weeks data and update the formatting and formula in the new row, for each sheet. I then manually update formulas about the table which tracks MIN, MAX, SUM and AVG. Is it possible to automate these steps for all 13 sheets?
Hi there - why do you have to separate the data across sheets? I would be tempted to keep it all on 1 sheet then use data analysis formulae (such as DSUM) to pick out the data I want to analyse. This would massively simplify overall structure. If the multiple sheet structure is unavoidable then this could be a job for VBA - plenty of tutorial videos on the channel about that. Good luck!
@@TigerSpreadsheetSolutions thanks for the reply. I will look into creating a master sheet then updating from the master sheet to each of the 13 product sheets. I use 13 sheets because I am tracking results for 13 different products. It's time consuming each week to manually add a new row to the table then apply the formats and update formula. Thanks.
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.
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.
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.
Another cheat... Look great. But my data is not too complicated. Lol. Happy to have a new formula. I think im gonna use it. I will make my data more complicated... 😄
Hi there SUMIF checks single criteria and sums up rows that conform to that criteria SUMIFS checks multiple criteria DSUM checks multiple criteria AND uses the user-friendly setup that I demo in this video
Thank you, A question, I have formulas for 2000 rows and data for 150 rows, but when I apply any filter all the 2000 rows are hidden as I filter, can I ensure I don't filter cells with formula, please. I have a problem, users are using from 20001 after filter and there is no formulas :-( so my effort is gone. Users cannot drag the formula as sheet is protected
Hhhmm ... it sounds like you have values and formulae mixed together in the same column? This is best avoided. You could convert the formulae to values for the purpose of filtering?
@@TigerSpreadsheetSolutions Thank you very much for the response but this formula is auto number generator based on concatenation of some business rule. Any advice sir?
Hey Tiger, How do i draw,or create a range of cells , loaded with macros, buttons, in example for inventory , but input data in each cell ,like add 1 or less 1, change color as quantity decreases, example cells are green while they are full , but if i decrease 1 on any cell it would turn yellow i need my cells numbered ,and and the date and inventory change pop in a home page Cells ,or inventory shelve model size would be 4 towers ,and each tower has 4 tiers,or levels Each level has 100 compartments,or cells ,each cell has a number ,like 1a,1b,1c,1d, and so forth ,and when i select the cell ,it pops up with as a bigger cell for info input purposes,like add notes ,date, buttons , Just cant find anyone that has the knowledge for this any ideas
I need some help. Have an excel sheet that my department log important information on and someone applied a filter looking for a particular piece of information, but the filter that they applied rearranged the entire sheet and messed up the order of the information imputed. Can someone tell me if it's possible to undo the filter even though the person saved and closed off the document and can you advise me as to how I can get back the original order that my document was in? Thanks.
i have two spared sheets one is called stock with price and another is called calculator in a stock sheet there is 3 columns category and sub category and price that shows in calculator sheet in list form i am not able to get a specific price of category and subcategory wise ex fruit is category and apple is sub category and 100 is price in calculator sheet fruit and apple are showing but how can i get the price of it please help me............ looking to hear from you..
Typically in the same example I'd be using a sumifs formula with the criteria as dropdowns from a list if I wanted to make it dynamic. What's the main benefit of using dsum instead? I'm guessing if you have many criteria..?
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?
@@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.
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
I'm a little newer to excel, so I'm having trouble thinking of examples where we might need to use this. Obviously in the one listed above, but what about in the business/non profit sector? Could someone give me an example?
Just curious - differences between Alt, A, T vs. Ctrl+Shift+L. They both add filters but the latter doesn't invoke sticky keys. Is it for flash and flair to make your video look more impressive, or is there a subtle purpose? Also, why select the whole data range before applying filters. Neither method requires this. If this is integral to the process, why not make the data range a table, which would automatically apply filters: Ctrl+L, Enter Final thought - Why all the keyboard arrow work. You're freely moving the cursor around, so you have either a mouse or thumbpad. Is clicking the filter button in the column, then clicking the text field with mouse/thumbpad somehow easier than: "arrow over to column, Alt+DownArrow, down, down, down, down, down, down, down, down (yes, there are 8, I counted)???
Thanks for the comments. These are just the techniques that have worked for me through my career. I prefer to use the keyboard rather than the mouse, even if more 'clicks' are required. It's only my view and you are quite welcome to disagree. Good luck!
@@TigerSpreadsheetSolutions I can completely understand user preference. It's actually encouraged. I can't help but realize though, you didn't answer any of my other questions.
So, here's what I see. Someone who has set themselves up as an expert, but either can't or won't answer basic questions, other that "it's my preference". Here's the thing though: If you choose to do things the hard way and have found a supposed easier way to do something, doesn't mean you should suggest to the rest of the world that they shouldn't do something as simple as filters. In your own video, even using a more difficult method, you were way quicker at enabling filtering than setting up a simple DSUM function. Even after it's setup the DSUM function as it was laid out here is only marginally quicker than using the normal Filters. (maybe a second or two using the keyboard navigation, if that long, and not at all with the mouse navigation inherently quicker method). Not to completely discount the original idea of DSUM however, there is a rather useful method that perhaps wasn't though of. Let's say that you wanted to compare stats for two or more different sports teams as in your demo. Creating multiple DSUM functions for each team would be a quicker way to get a visual glance at what the better stats were per team. Mult-ifiltering can be done with out of the box filters, however the Sum column would be a measure of both or all teams combined. With multiple DSUM filters as was created in the video, would give statistics for all teams individually.
Both can be used to do this job. SUMIFS - longer formula, favoured by many but I'm not absolutely clear why DSUM - shorter formula, some additional setup required as shown in this video We also have a video on SUMIFS - try both and see which works best for you, DSUM is my recommendation. Good luck!
I notice the difference is the cell in which excel leaves you. Using Ctrl + Shift + L you stay on your original cell. Whereas Ctrl* puts you in col1row1 of the selected range.
@@TigerSpreadsheetSolutions shorter once 4 cells are used for criteria range! DSUM also a nightmare with the criteria cells being over two rows. you can't copy the formula down a sheet.
@@TigerSpreadsheetSolutions Ahhh! How should I go about doing that? I can't seem to figure out how to make it work. Like, I thought of doing each scenario in a new column but it seems for DSUM I'd need to select the criterias table in NxN dimensions, I'd not be able to specify it column by column... Help!
So I tried if( istext(or( x, y )) and for some reason it isn't working. I have to use is text twice within or to get it to work... but it complicates an already ridiculously complex formula. Is there something I'm doing wrong??
I USE SUMIFS AND STARTED DSUM AS WELL AFTER THIS VIDEO, YOU DON'T NEED TO MODIFY AND EDIT THIS FORMULA ONCE ITS SETUP. BUT TO YOU NEED TO EDIT SUMIFS IF YOU NEED TO ENTER MORE CRITERIA. ONE CAN EASILY SWITCH TO DIFFERENT COLUMNS AS CRITERIA AND MULTILPLE CRITERIA IN DUSM, THANKS FOR THE FORMULA
🔥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 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?
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?
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
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!
Chris, first time viewer, excellent video. Thank you
Thank you and welcome
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!
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.
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 :)
Appreciate your patience in explaination. Made it easy.
Thank you and welcome to the channel!
I really appreciate your video training which is simple and comprehensible.
Thank you my friend - do check out the other videos!
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 ...?
Just found your channel. Excellent tutorial! Thanks for sharing this Gem! Subbed.
Welcome, my friend!
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!
Thank you for the video Mr. Chris
Thank you, my friend!
Very nice Sir. Thanks a lot
Welcome to the channel Prakash
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
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 ...
Could you cover Power Query in the future?
Hi John - I'm no expert but we did cover the basics in a recent video:
th-cam.com/video/8s6Bvpt7-50/w-d-xo.html
Wow! That's great. Thanks as always.
Thanks Peter! Do check our other videos ...
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!
kinda like power pivot, sumx nested with filter, like it, thanks.
I love this one ...
Nice one. Great tool that is. Thank you
He lives! Thanks Rik and welcome to the channel.
Thanks for sharing this. But we can do it way faster with slicer table and subtotal.
It's a matter of opinion and you always benefit from having more tools in your toolkit.
@@TigerSpreadsheetSolutions sure 👍
"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!
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!
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!
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
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"
I'm an excel power user, filter now and then but DSUM is much more useful for repetitive analysis or repetitive data sets where you know the structures, common filters etc.
This system analysis is more common than people realise!
Yeah, but what is you actually want TO SEE THE DATA - and not just see the output, which is the sum, count or average. If you are filtering only to see those basic stats, then that is such an inefficient use of filtering. Filtering has other purposes.
A power user! We're honoured. Thanks for watching.
@@JJ_TheGreat Agreed - but generally, when you're dealing with data you know your way around or testing, the outputs from a high level are more important before you *consider* whether or not you might do any further analysis and need to see the data.
The comfort of seeing the data or the feeling to need to see it doesn't mean it needs to be seen :)
DSUM can be really useful... didnt knew about this formula before, thanks for the explanation.
You're welcome Faiz - I hope it helps you!
nice tutorial. good job. thanks
Thanks Ade, and welcome
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!
Elegant. You can also do this using Boolean logic and Boolean math. I do not know whether this works with table references. It likely does. The equivalent form in Boolean would go like this. For convenience, let’s call the results data column array “results”, and each criteria data column ‘array1’, ‘array1’ …. And each criteria ‘criteria’, ‘criteria’, …. Then the formula becomes =sum((results)*(array1=criteria1)*(array2=criteria2)) … I used “=“. Use the appropriate evaluator “=“, “” …. How this works is as follows. The whole formula uses matrix math. Sum((A)*(B)*(C)). The Boolean part mixes matrix math with a Boolean test. E.g. (array1=criteria1). For each instance in the array the test is carried out and the array becomes a truth table of values. A “1” for each true instance, 0 for false. Multiplying these amounts to asking are they all tue. Sums would ask if any are true. In the Boolean array math represented by an equality equation inside parentheses, any value equates to 1, so simply putting the whole in parentheses becomes an array of ones and zeros. Then multiplying that using matrix math times the array with the values desired becomes an array having only those results matching the criteria. Apply a simple sum (or other formula) to that yields the desired result. You can also easily find the most recent date that has a value in a table of dates this way. Say column A is the dates. Column B is the value desired with some having values and some being empty. The most recent date which has a value is then =max((ArrayA)*(ArrayB>0)). This is difficult to do any other way. Where dsum() is limited to conditions where all criteria are met, Boolean matrix logic math can handle vastly more complex criteria conditions. In this it is equivalent to ladder logic (or relay logic) as is used in programmable controllers - expressed as an equation rather than as a ladder logic program. Btw - to return the most recent value in my date example you can use xlookup or vlookup using the max equation as the criteria. Or you can feed that back to another formula. Say the result of the max equation is called ‘maxresult’. Then the latest value =(Array1=maxresult)*(Array2). Simple.
I should add…. This works provided that none of the array values trigger errors. That can happen if array values cannot be evaluated. E.g. they cannot be tested, they aren’t numbers …. The formulas can be rewritten if this is possible in the data arrays to test for or convert such errors. Unfortunately excel does not make that as simple. For each instance in this equation Result = (Array1=value)*(Array2) … it my be necessary to add testing conditions such as for Array1 …. Substitute something like IF(iserror(Array1=result),0,(Array1=result)). However, care is required in thinking through the logic to ensure that the results of the test don’t themselves trigger errors. And that can complicate matters. The simple way to ensure this does not happen is to create a smaller test worksheet with fake data which include the various possible conditions (values, text, blanks, =na(), =1/0, =1+qqqq, which will trigger the potential errors. Doing this allows you to see what various data anomalies might do to the formula results. You may then also need to set up a resizes of columns on this test data to evaluate each of these individual cases to figure out what logical changes are needed to overcome Excels limitations.. once you know which formulations will overcome Excel short circuiting to Error results, you can then change the formula for the array/Boolean math tests. This is less simple and begins to look more like a program than a math equation.
Interesting stuff, Palmer - thanks!
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 ...
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
Amazing! Will come again.
See you again soon, Paul!
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
Thank you for this instructive presentation. I plan to experiment with DSUM instead of filters.
Would a drop down list from Data validation work?
Absolutely - and it would speed up the selections ...
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
I'm giving up on filters. Cool instructions.
Welcome to the channel!
DSUM vs Sumifs - what's the difference !!
Both looks same to me while placing the criteria in dynamic cell
Great question - watch this space for a video soon
@@TigerSpreadsheetSolutions yup 👍
Main thing I can see is that sumifs requires a bunch of nested pairs for multiple criteria, here it's just a range. Pretty cool, I'll use it.
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
CAN YOU Tell ME ? How to make videos like this? Are you using a single software or you record screen and video separately then merge them? I would be very grateful.
Hi Kunal - I use software called Streamyard for this one. There are many good options out there these days. Good luck!
@@TigerSpreadsheetSolutions thank you. I am a teacher. Can you suggest me a suitable alternative?
Use Ctr + Shift + L for filter on / off on selected areas, where the top row is your header.
Love these shortcuts!
Have you ever tried using conditional formattting, Mr. Excel Consultant?
Errr ...
ı have a question. we can bring a information from other file with vlookup according to one variable. but, whatıf ı want to use two variable to bring this information? which formula should ı use?
thank you.
Hi Ibrahim - there are a few different approaches, but none of them easy. I would probably use VBA to do this - we have many beginner videos on the channel if you'd like to learn.
You can do same way using =SUMIFS(T12:T771,C12:C771,C5,J12:J771,D5). You can also add more criteria.
Indeed you can, but the formula is much shorter using DSUM. This makes is easier to use in my view.
@@TigerSpreadsheetSolutions Yeah, you are correct. And this DSUM is not very commonly used, so it is always good to have options, as long as the end result is correct. 😀
Great Tip, I have a xls challenge I am loosing sleep over... can you please help.. how do I email you the problem?
Thanks - email address on the website ...
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.
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?
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
Have you tried advanced filter? That usually works faster and neater than DSUM or filters.
Will check it out ...
Nice work
Thanks Rajesh and welcome to the channel
What a great video! Thanks for sharing!
Thanks Frank and welcome!
Ctr+Shift+L is the easiest way to enable filter compare to Alt+a+t
Plus if you do it again, on the selected line, then you remove filters
I think the easiest way to enable filters is to add it to the Quick Access Toolbar." One click
yeah, since we are selecting range using Ctrl+Shift+Arrow key, all we have to do is press L
Hi - I have a spreadsheet with 13 sheets. Each sheet has a table which tracks weekly data. Each week I manually add a new row to the top of the table for the newest weeks data and update the formatting and formula in the new row, for each sheet. I then manually update formulas about the table which tracks MIN, MAX, SUM and AVG. Is it possible to automate these steps for all 13 sheets?
Hi there - why do you have to separate the data across sheets? I would be tempted to keep it all on 1 sheet then use data analysis formulae (such as DSUM) to pick out the data I want to analyse. This would massively simplify overall structure. If the multiple sheet structure is unavoidable then this could be a job for VBA - plenty of tutorial videos on the channel about that. Good luck!
@@TigerSpreadsheetSolutions thanks for the reply. I will look into creating a master sheet then updating from the master sheet to each of the 13 product sheets. I use 13 sheets because I am tracking results for 13 different products. It's time consuming each week to manually add a new row to the table then apply the formats and update formula. Thanks.
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
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!
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.
Incredible technique!!! Thanks alot Sir for sharing this :)
Welcome, Hassan!
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?
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
Well, this is useful for summing values, but filters have way more uses than that, mainly, filter what you see....
True
Had to scroll too far down to find this. Seems like such a niche use of filters.
Can DSUM be used to replace long drawn out SUMPRODUCT() stings?
Michael - I'm not sure, I'm not an expert on SUMPRODUCT. I am sure there is an alternative ...
Another cheat... Look great. But my data is not too complicated. Lol. Happy to have a new formula. I think im gonna use it. I will make my data more complicated... 😄
Good luck with it!
Hi, could you tell us what is the diference between DSUM and SUMIFS or SUMIF.
Thanks in advance.
Hi there
SUMIF checks single criteria and sums up rows that conform to that criteria
SUMIFS checks multiple criteria
DSUM checks multiple criteria AND uses the user-friendly setup that I demo in this video
@@TigerSpreadsheetSolutions And SUMIFS work on multiple criteria
Many thanks - another piece of learning for me !
Good to hear, Rob!
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? :-)
Hi
Can you please upload a video for
Converting multiple pdf in a folder into JPG OR PNG and save in a different folder via VBA
Is it working with multiple criterias in one collumn from criteria table?
I understand this is possible, but I have never got it working smoothly myself ...
I was interested in this, but kind of missed the point why I should abandon SUMIFS and move to DSUM.
I'm not suggesting you abandon anything - I'm suggesting you try to broaden your toolkit whilst appreciating the pros / cons of each technique
Thank you, A question, I have formulas for 2000 rows and data for 150 rows, but when I apply any filter all the 2000 rows are hidden as I filter, can I ensure I don't filter cells with formula, please. I have a problem, users are using from 20001 after filter and there is no formulas :-( so my effort is gone. Users cannot drag the formula as sheet is protected
Hhhmm ... it sounds like you have values and formulae mixed together in the same column? This is best avoided. You could convert the formulae to values for the purpose of filtering?
@@TigerSpreadsheetSolutions Thank you very much for the response but this formula is auto number generator based on concatenation of some business rule. Any advice sir?
Hey Tiger,
How do i draw,or create a range of cells , loaded with macros, buttons, in example for inventory , but input data in each cell ,like add 1 or less 1, change color as quantity decreases, example cells are green while they are full , but if i decrease 1 on any cell it would turn yellow i need my cells numbered ,and and the date and inventory change pop in a home page Cells ,or inventory shelve model size would be 4 towers ,and each tower has 4 tiers,or levels
Each level has 100 compartments,or cells ,each cell has a number ,like 1a,1b,1c,1d, and so forth ,and when i select the cell ,it pops up with as a bigger cell for info input purposes,like add notes ,date, buttons , Just cant find anyone that has the knowledge for this any ideas
I do sum(filter(sum array, criteria1*criteria2..). It’d do the same but might be a bit longer.
Interesting idea, thanks!
I need some help. Have an excel sheet that my department log important information on and someone applied a filter looking for a particular piece of information, but the filter that they applied rearranged the entire sheet and messed up the order of the information imputed. Can someone tell me if it's possible to undo the filter even though the person saved and closed off the document and can you advise me as to how I can get back the original order that my document was in? Thanks.
Can dsum or is there anyway to include multiple criteria with and/or conditions?
AND is possible with the basic setup. OR I believe is possible with a slightly different setup - check out Microsoft's resources on DSUM.
i have two spared sheets one is called stock with price
and another is called calculator
in a stock sheet there is 3 columns category and sub category and price
that shows in calculator sheet in list form
i am not able to get a specific price of category and subcategory wise
ex fruit is category and apple is sub category and 100 is price
in calculator sheet fruit and apple are showing but how can i get the price of it
please help me............
looking to hear from you..
Thanks, that made sense
You're welcome - please check out our other videos
Typically in the same example I'd be using a sumifs formula with the criteria as dropdowns from a list if I wanted to make it dynamic.
What's the main benefit of using dsum instead?
I'm guessing if you have many criteria..?
There is no benefit of DSUM... He is just showing off... Sumifs is way better and simpler for this
You could do the same thing with SUMIFS. I find the DSUM formula easier to set up since it is shorter then SUMIFS.
🤣
SUMIFS can work on Table references. DSUM won't.
Setting up dsum involves more step and complexity. Like getting the column name.. whereas in sumifs it can be done at one go
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!
I love stumbling across excel cheat sheets, thank you :)
Welcome to the channel, Bryan!
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
So, how different from SUMIFS is this beside that it looks easier to setup for the criterias.
Easier setup is the main advantage for me
It is possible to use pivot instead formula like yhat?
It is, but personally I try to avoid pivot tables where possible
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
Liking the keyboard shortcuts...
Me too! Welcome ...
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!
I'm a little newer to excel, so I'm having trouble thinking of examples where we might need to use this. Obviously in the one listed above, but what about in the business/non profit sector? Could someone give me an example?
Business - suppose you have a list of transactions, DSUM would allow you to sum up income from a particular product or service
Just curious - differences between Alt, A, T vs. Ctrl+Shift+L. They both add filters but the latter doesn't invoke sticky keys. Is it for flash and flair to make your video look more impressive, or is there a subtle purpose?
Also, why select the whole data range before applying filters. Neither method requires this. If this is integral to the process, why not make the data range a table, which would automatically apply filters: Ctrl+L, Enter
Final thought - Why all the keyboard arrow work. You're freely moving the cursor around, so you have either a mouse or thumbpad. Is clicking the filter button in the column, then clicking the text field with mouse/thumbpad somehow easier than: "arrow over to column, Alt+DownArrow, down, down, down, down, down, down, down, down (yes, there are 8, I counted)???
Thanks for the comments. These are just the techniques that have worked for me through my career. I prefer to use the keyboard rather than the mouse, even if more 'clicks' are required. It's only my view and you are quite welcome to disagree. Good luck!
@@TigerSpreadsheetSolutions I can completely understand user preference. It's actually encouraged. I can't help but realize though, you didn't answer any of my other questions.
So, here's what I see. Someone who has set themselves up as an expert, but either can't or won't answer basic questions, other that "it's my preference". Here's the thing though:
If you choose to do things the hard way and have found a supposed easier way to do something, doesn't mean you should suggest to the rest of the world that they shouldn't do something as simple as filters. In your own video, even using a more difficult method, you were way quicker at enabling filtering than setting up a simple DSUM function. Even after it's setup the DSUM function as it was laid out here is only marginally quicker than using the normal Filters. (maybe a second or two using the keyboard navigation, if that long, and not at all with the mouse navigation inherently quicker method).
Not to completely discount the original idea of DSUM however, there is a rather useful method that perhaps wasn't though of. Let's say that you wanted to compare stats for two or more different sports teams as in your demo. Creating multiple DSUM functions for each team would be a quicker way to get a visual glance at what the better stats were per team. Mult-ifiltering can be done with out of the box filters, however the Sum column would be a measure of both or all teams combined. With multiple DSUM filters as was created in the video, would give statistics for all teams individually.
You could also get that with sumifs, and it will return the same. I am not sure though, if sumifs is not more calculation power greedy...
I do plan to investigate this, thanks for the comment ...
What's the difference between DSUM and SUMIFS? Really didn't get.
Both can be used to do this job.
SUMIFS - longer formula, favoured by many but I'm not absolutely clear why
DSUM - shorter formula, some additional setup required as shown in this video
We also have a video on SUMIFS - try both and see which works best for you, DSUM is my recommendation. Good luck!
From the future here, It seems SUMIFS do that in a more dynamic fashion ?
It depends what you mean by 'dynamic'. DSUM does it with a much shorter formula. Thank you for watching!
Not bad, I'll probably stick to Ctrl+Shft+L, but I'm gonna subscribe to the channel because you remind me of David Tennant
😂😂😂Welcome!
I notice the difference is the cell in which excel leaves you. Using Ctrl + Shift + L you stay on your original cell. Whereas Ctrl* puts you in col1row1 of the selected range.
There's no need to go through the hassle of DSUM since the introduction of SUMIFS. Also, filters are often used on the fly,
Hassle? It's a shorter formula
@@TigerSpreadsheetSolutions but a bigger setup.
@@TigerSpreadsheetSolutions shorter once 4 cells are used for criteria range! DSUM also a nightmare with the criteria cells being over two rows. you can't copy the formula down a sheet.
Why not use pivot?
How do I DSUM multiple scenarios though? E.g. scenario 1 with beagle >1.54 scenario 2 beagle > 1.7 etc
Great question - consider combining it with a data table
@@TigerSpreadsheetSolutions Ahhh! How should I go about doing that? I can't seem to figure out how to make it work.
Like, I thought of doing each scenario in a new column but it seems for DSUM I'd need to select the criterias table in NxN dimensions, I'd not be able to specify it column by column... Help!
Kerry Thong I’ve put it on my list of videos ‘to do’. Check out our videos on data tables.
So I tried if( istext(or( x, y )) and for some reason it isn't working. I have to use is text twice within or to get it to work... but it complicates an already ridiculously complex formula. Is there something I'm doing wrong??
Sumifs is easy/efficient alternative for this i think.. anyway thanks for dsum.. vl definitely try this
It's definitely worth a try
I USE SUMIFS AND STARTED DSUM AS WELL AFTER THIS VIDEO, YOU DON'T NEED TO MODIFY AND EDIT THIS FORMULA ONCE ITS SETUP. BUT TO YOU NEED TO EDIT SUMIFS IF YOU NEED TO ENTER MORE CRITERIA. ONE CAN EASILY SWITCH TO DIFFERENT COLUMNS AS CRITERIA AND MULTILPLE CRITERIA IN DUSM, THANKS FOR THE FORMULA
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 ...