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.
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.
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.
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.
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.
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.
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
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
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.
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'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?
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 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.
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..
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 :)
@@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.
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.
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..
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... 😄
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.
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!
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?
@@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.
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. 👍
@@TigerSpreadsheetSolutions , sure, i need more time, checking one by one... really useful!!!! you are so great!!!!! helps me a lot. you are the excel guru!
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 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
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.
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.
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. :)
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..?
🔥WATCH NEXT: Another Powerful Excel Formula:
ua-cam.com/video/O56lIznjYdg/v-deo.html
Hello Tiger... What does "DSUM" stand for?
CTRL + SHIFT + L to enable filters, you don’t need to select the whole range either
Yes, usually i am go on that method
Yes but if for some reason there are empty rows in your table, the data after won’t be taken into the filters if you don’t select your entire table first.
@@aynino agreed, if you are using the filtering to cleanse the data
I like it!
What about the shortcut key to remove the table?
Ctrl Shift L enables filters in all columns without having to select the column.
Alt ⬇️ drops the filter menu, pressing E after that takes you directly to the search box.
Not exactly a replacement though, like with all analytical issues, it depends on the scenario you're testing.
Absolutely
Thx
Or just click on one of the headers then enable the filter?
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
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!
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!
Just found your channel. Excellent tutorial! Thanks for sharing this Gem! Subbed.
Welcome, my friend!
Very nice Sir. Thanks a lot
Welcome to the channel Prakash
Nice one. Great tool that is. Thank you
He lives! Thanks Rik and welcome to the channel.
Wow! That's great. Thanks as always.
Thanks Peter! Do check our other videos ...
Chris, first time viewer, excellent video. Thank you
Thank you and welcome
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!
nice tutorial. good job. thanks
Thanks Ade, and welcome
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!
"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!
Amazing! Will come again.
See you again soon, Paul!
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 :)
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 work
Thanks Rajesh and welcome to the channel
Thanks, that made sense
You're welcome - please check out our other videos
DSUM can be really useful... didnt knew about this formula before, thanks for the explanation.
You're welcome Faiz - I hope it helps you!
What a great video! Thanks for sharing!
Thanks Frank and welcome!
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.
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 ...?
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
I'm giving up on filters. Cool instructions.
Welcome to the channel!
Thanks 👍👍
You're welcome - do check out the other videos on the channel
kinda like power pivot, sumx nested with filter, like it, thanks.
I love this one ...
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
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 ...
Thank you for this instructive presentation. I plan to experiment with DSUM instead of filters.
Many thanks - another piece of learning for me !
Good to hear, Rob!
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
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
Never heard of this. I use a fancy index match array formula to live filter. You can do what your doing with sumifs though.
True, but not as elegantly as with DSUM
For show / remove filter, go to first heading and type ALT, D, F,F all one after another, not together. Many ways to do one thing.
When you changed the name and the total changed looked like magic. Need to understand how is it doing calculation and is the order of the content of small 4 cell table important.
Very powerful formula. Thank you
Thanks Sujal - correctly set up, it does feel like magic. I hope you enjoy this one!
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
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!
Incredible technique!!! Thanks alot Sir for sharing this :)
Welcome, Hassan!
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
Thank You!
You're welcome!
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 ...
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 👍
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
Excel-lent short tutorial! I love Excel!
👏👏👏
Very convinient )))
Thanks, Jasper!
Love this formula! It's like a simplified form of the SUMIFS formula 😊
I couldn't agree more - 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"
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
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.
Never knew this formula before, great tool, thanks.
I hope you get value out of it ...
Would a drop down list from Data validation work?
Absolutely - and it would speed up the selections ...
You're doing the long way....just right click on the data and filter by selected cell, it auto adds the filters and all....cheers mate
Thanks ... you did watch the rest of the video, right? :-)
Great 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 ...
I love stumbling across excel cheat sheets, thank you :)
Welcome to the channel, Bryan!
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
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.
Awesome video bro!!!
Welcome to the channel my friend!
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 :)
This is really amazing😳
Thanks Sherwin and welcome
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?
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.
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.
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!
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
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..
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!
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!
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.
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!
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
Liking the keyboard shortcuts...
Me too! Welcome ...
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 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!!! It is very useful!
Welcome to the channel!
Very helpful, thank u so much.
Thank you and welcome to the channel!
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.
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!
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
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.
REALLY AMAZING channel, why I find it so late...
Thank you.
Welcome - do you check out the other videos!
@@TigerSpreadsheetSolutions , sure, i need more time, checking one by one... really useful!!!!
you are so great!!!!! helps me a lot.
you are the excel guru!
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
Have you ever tried using conditional formattting, Mr. Excel Consultant?
Errr ...
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 ...
That’s great. How would you use wildcards in the criteria?
Thank you - various options are possible, check out Microsoft's learning resources on this formula
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 is possible to use pivot instead formula like yhat?
It is, but personally I try to avoid pivot tables where possible
Wow! That was a great help. Thank you so much, man! You saved a lot of my time
Great to hear Abdul - nice job!
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 ...
Use Ctr + Shift + L for filter on / off on selected areas, where the top row is your header.
Love these shortcuts!
Have you tried advanced filter? That usually works faster and neater than DSUM or filters.
Will check it out ...
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
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?
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?
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!
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. :)
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