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.
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.
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.
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.
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..
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.
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
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
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 :)
"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.
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.
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.
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 :)
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.
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!
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
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!
As an alternative solution of this specific requirement, I would rather : - change my range of cells into Table because... it's a good habit when dealing with this kind of datasource (for instance, if you add lines or columns, every related formulas or pivot tables keep up, embedded formulas are automatically applied on each line, etc. Never trust a range collection of cells...) - generate Pivot Table from the table - summarize '1' value by criteria 'Home' - Add a pivot table computed column : '1' - 1.54 and call it 'Returns Home Win' And I would obtain every results for every 'Home' displayed at once, witch would be easier for comparing, sorting, further filtering, etc. You even can add some slicers and timeline features in order to ease the interaction with the Pivot Table, visualize the data through charts However, good illustration of the possibility and use of DSUM formula, I did not know it...
@@TigerSpreadsheetSolutions I have the opinion too, table would helps as when my data expanded, you can easily paste the additional data and all absolute reference will follow as well. Without table everytime you add on more data, you have to adjust your dsum database.. But can you explain the cons of using a table?
@@channul4887 Err.. We must have been talking about a different thing here surely.. I'm not sure what constitute as annoying to you, but I just pressed Ctrl+T and table is created. I want to refer to the table for any formula, vlookup for example? I don't have to select row and column, just type the table name and row and column header. I want to add to the rows with exact same formatting? Just paste below it and the table automatically extended further down. I have no idea which part of excel table is annoying..
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.
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.
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!
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. 👍
Hi, thanks for teaching me the DSUM formula, I just have a couple of comments regarding the shortcuts you're using, First of all, for the first part of the video, Ctrl+A should select the whole dataset, I would then do Ctrl+T so a table is created and filters too, Ctrl+Shift+T to add total row so when you apply your filter you get to see the totals right away without needing to select the column. For the second part, I would use a data validation list for the Home Column (so the user can select with no typos [taking in consideration that it is already a table, even better so new rows will be added automatically to the range), a data validation list would also be useful for the column you want to be returned, (so instead of hard coding the name of the column in the DSUM function, I would reference a cell that has a list of all headers). Thanks anyway.
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?
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... 😄
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 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
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?
@@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. 😀
@@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.
@@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.
2:10 Cheater doesnt know that to select a range you can use Ctrl+* (current region without empty rows and columns) and for filter enough to select headers only and to switch on filter you can simply press Ctrl+Shift+L. :)
@@TigerSpreadsheetSolutions You dont need even select whole range if you want to have filter on each column. Just put the mouse inside a table and press shortcut. You need to select some headers if you have wide table and you dont need to have filter on each column.
You can minimise spelling errors by creating a list using data validation. Also, if the data set is frequently updated, you can convert it to a table and get DSUM to reference the table, the range will automatically update as you update/add more data
That can easily be done using SumIfs. You can even use Indirect function if you want the parameters in a separate table as you did. Not sure about processing time though.
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.
BTW, ctrl-shift-L anywhere in the dataset range will activate autofilters. **slightly** quicker than your "ctrl-shift-right, ctrl-shift-down, alt-a-t" *shortcut*..... FWIW.
Ctrl+shift+L is much easier because you don't need to select a contiguous area and you just hit it twice to clear the filter, and (bonus) it works on Mac. ...BUT alt+down FTW! Where have you been all my career?
Didnt know about this, but it doesn't seem to be the best tool for the job. Why not have a proper table and a VB function that loops through combinations of relevant filters and for each you then print the sum of a specific column when the table is filtered? That way you get all the output you could ever want from a particular dataset and the VB function should ofcourse take column names as arguments so you can resuse it on any dataset. Or, why not use an Index/Match function? Just multiply criterias so that all needs to be true for it to return anything. That way you get an array of cells from a particular column who all belong to rows where all criterias are true. Feed that array to a sum function and you are done. That way there is no setup needed and you just need to modify a word in the function to fet new criterias
Even with this formula, you still have to filter the data if you only want to see the rows that match your criteria. Filtering is most useful for visually hiding and sorting data. This is a good explanation of DSUM, but it is not a replacement for filters as the title suggests.
or without any setup: =SUMPRODUCT((League_Table!C12:C771="Beagle")*(League_Table!J12:J771>1.54)*(T12:T771)) or =SUMIFS(T12:T771,League_Table!C12:C771,"Beagle",League_Table!J12:J771,">1.54")
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!
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?
🔥WATCH NEXT: Another Powerful Excel Formula:
ua-cam.com/video/O56lIznjYdg/v-deo.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?
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!
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
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 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.
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
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!
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
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 ...
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!
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"
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 :)
"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!
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!
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? :-)
I really appreciate your video training which is simple and comprehensible.
Thank you my friend - do check out the other videos!
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
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
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.
Appreciate your patience in explaination. Made it easy.
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!
I'm giving up on filters. Cool instructions.
Welcome to the channel!
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 :)
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!
Love this formula! It's like a simplified form of the SUMIFS formula 😊
I couldn't agree more - thanks!
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!
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!
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'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
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!
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
As an alternative solution of this specific requirement, I would rather :
- change my range of cells into Table because... it's a good habit when dealing with this kind of datasource (for instance, if you add lines or columns, every related formulas or pivot tables keep up, embedded formulas are automatically applied on each line, etc. Never trust a range collection of cells...)
- generate Pivot Table from the table
- summarize '1' value by criteria 'Home'
- Add a pivot table computed column : '1' - 1.54 and call it 'Returns Home Win'
And I would obtain every results for every 'Home' displayed at once, witch would be easier for comparing, sorting, further filtering, etc.
You even can add some slicers and timeline features in order to ease the interaction with the Pivot Table, visualize the data through charts
However, good illustration of the possibility and use of DSUM formula, I did not know it...
Thanks for explaining your view. Personally I find tables bring some cons too that I prefer to avoid.
@@TigerSpreadsheetSolutions I have the opinion too, table would helps as when my data expanded, you can easily paste the additional data and all absolute reference will follow as well. Without table everytime you add on more data, you have to adjust your dsum database.. But can you explain the cons of using a table?
@@MrAZulfadzli "But can you explain the cons of using a table?"
it's fucking annoying.
@@channul4887 Err.. We must have been talking about a different thing here surely.. I'm not sure what constitute as annoying to you, but I just pressed Ctrl+T and table is created. I want to refer to the table for any formula, vlookup for example? I don't have to select row and column, just type the table name and row and column header. I want to add to the rows with exact same formatting? Just paste below it and the table automatically extended further down. I have no idea which part of excel table is annoying..
I love stumbling across excel cheat sheets, thank you :)
Welcome to the channel, Bryan!
Thank you for this instructive presentation. I plan to experiment with DSUM instead of filters.
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.
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!
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!
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
Hi, thanks for teaching me the DSUM formula,
I just have a couple of comments regarding the shortcuts you're using,
First of all, for the first part of the video, Ctrl+A should select the whole dataset, I would then do Ctrl+T so a table is created and filters too, Ctrl+Shift+T to add total row so when you apply your filter you get to see the totals right away without needing to select the column.
For the second part, I would use a data validation list for the Home Column (so the user can select with no typos [taking in consideration that it is already a table, even better so new rows will be added automatically to the range), a data validation list would also be useful for the column you want to be returned, (so instead of hard coding the name of the column in the DSUM function, I would reference a cell that has a list of all headers).
Thanks anyway.
Great ideas - I agree there are plenty of minor details that could be improved, but obsessing about the details can mean no video is created.
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.
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?
Excel-lent short tutorial! I love Excel!
👏👏👏
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
Use Ctr + Shift + L for filter on / off on selected areas, where the top row is your header.
Love these shortcuts!
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 for the video Mr. Chris
Thank you, my friend!
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 ...?
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!
Chris, first time viewer, excellent video. Thank you
Thank you and welcome
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 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!
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?
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. 😀
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
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
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?
I am using Excel for years. And this blew my mind!
Welcome to the channel!
Hw can we improve excel ?
2:10 Cheater doesnt know that to select a range you can use Ctrl+* (current region without empty rows and columns) and for filter enough to select headers only and to switch on filter you can simply press Ctrl+Shift+L. :)
Nice shortcuts!
@@TigerSpreadsheetSolutions You dont need even select whole range if you want to have filter on each column. Just put the mouse inside a table and press shortcut.
You need to select some headers if you have wide table and you dont need to have filter on each column.
also entire range selection works with CTRL+SHIFT+SPACE which are already under your fingers
Very nice Sir. Thanks a lot
Welcome to the channel Prakash
kinda like power pivot, sumx nested with filter, like it, thanks.
I love this one ...
Amazing! Will come again.
See you again soon, Paul!
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.
Never knew this formula before, great tool, thanks.
I hope you get value out of it ...
You can minimise spelling errors by creating a list using data validation. Also, if the data set is frequently updated, you can convert it to a table and get DSUM to reference the table, the range will automatically update as you update/add more data
Who knew?
@@TigerSpreadsheetSolutions not you. Except you pretend to know with the smug comment 🤣🤣🤣🤣🤣
That can easily be done using SumIfs. You can even use Indirect function if you want the parameters in a separate table as you did. Not sure about processing time though.
Sorry, I don't have time to write the lengthy SUMIFS formula
@@TigerSpreadsheetSolutions but you do have time to fanny about creating this solution 🤣🤣🤣🤣🤣. Sumif is so simple. Suggest you relook.
Me, recently changed from a python heavy role to excel heavy role: *pauses video at ctrl-shift-down and will be back in a few weeks*
Welcome to the channel, Steve!
But... Why? I'm an Excel guy and am looking for a Python+SQL job to shift to.
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 👍
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.
Liking the keyboard shortcuts...
Me too! Welcome ...
Wow! That's great. Thanks as always.
Thanks Peter! Do check our other videos ...
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
Many thanks - another piece of learning for me !
Good to hear, Rob!
BTW, ctrl-shift-L anywhere in the dataset range will activate autofilters. **slightly** quicker than your "ctrl-shift-right, ctrl-shift-down, alt-a-t" *shortcut*..... FWIW.
Would have been funnier if you'd but shortcut in speech marks, but pretty snarky, I like it
Ctrl+shift+L is much easier because you don't need to select a contiguous area and you just hit it twice to clear the filter, and (bonus) it works on Mac.
...BUT alt+down FTW! Where have you been all my career?
Wow! That was a great help. Thank you so much, man! You saved a lot of my time
Great to hear Abdul - nice job!
Didnt know about this, but it doesn't seem to be the best tool for the job.
Why not have a proper table and a VB function that loops through combinations of relevant filters and for each you then print the sum of a specific column when the table is filtered? That way you get all the output you could ever want from a particular dataset and the VB function should ofcourse take column names as arguments so you can resuse it on any dataset.
Or, why not use an Index/Match function? Just multiply criterias so that all needs to be true for it to return anything. That way you get an array of cells from a particular column who all belong to rows where all criterias are true. Feed that array to a sum function and you are done. That way there is no setup needed and you just need to modify a word in the function to fet new criterias
This sounds a bit complicated to me, I always look for a non-VBA-based solution first ...
Just found your channel. Excellent tutorial! Thanks for sharing this Gem! Subbed.
Welcome, my friend!
Could you cover Power Query in the future?
Hi John - I'm no expert but we did cover the basics in a recent video:
ua-cam.com/video/8s6Bvpt7-50/v-deo.html
Have you tried advanced filter? That usually works faster and neater than DSUM or filters.
Will check it out ...
Even with this formula, you still have to filter the data if you only want to see the rows that match your criteria. Filtering is most useful for visually hiding and sorting data. This is a good explanation of DSUM, but it is not a replacement for filters as the title suggests.
It's a replacement for filtering if you're using filtering and SUBTOTAL. Check out the video later in this series on the =FILTER formula ...
Why not pivot tabel instead? It's so much faster and versatile.
Sure - if you are building the file for yourself. If others might use the file then ...
@@TigerSpreadsheetSolutions pfff, should I use ms word for "others"? Pivot tables are 20+ years old. OK, keep this "expert" feature for yourself ;)
It’s people like you that help me realize that some people invest the time to truly learn shortcuts - with which they use to win the game. Well done.
Thank you very much - I hope this saves you some time
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
Thanks, that made sense
You're welcome - please check out our other videos
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!
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 ...
I thought I knew Excel until I started looking for help on UA-cam. Good stuff.
Welcome to the channel, Mickey!
Instead of using tab to get to the search field, you can just hit the letter E right after you've alt tab down arrow to open up the box
I love this one and am now using it in my practice ... thanks!
Thank you!
You're welcome!
or without any setup:
=SUMPRODUCT((League_Table!C12:C771="Beagle")*(League_Table!J12:J771>1.54)*(T12:T771))
or
=SUMIFS(T12:T771,League_Table!C12:C771,"Beagle",League_Table!J12:J771,">1.54")
(edited)
Ctrl + shift + space to select all the table
I like it!
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!
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
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.
I do sum(filter(sum array, criteria1*criteria2..). It’d do the same but might be a bit longer.
Interesting idea, thanks!
Incredible technique!!! Thanks alot Sir for sharing this :)
Welcome, Hassan!
I recommend you to take a course from real expert about shortcuts and formulas!
Power query has changed my life
Awesome!
Nice one. Great tool that is. Thank you
He lives! Thanks Rik and welcome to the channel.