❓Have you tried database functions before, or are they still on your "to-learn" list? Learn more about the Advanced Excel Formulas course: bit.ly/dfunctions24course Master Excel with my courses: bit.ly/dfunctions24courses
I’ve seen the database functions listed in the intellisense as I start typing in other functions but knew nothing about them. I didn’t even know they were called database functions, I just saw DSUM and DMAX and so on. I’ve learned something new, and very interesting today. Thank you.😍
Thank you for this!!! The dbase functions in Excel have an incredibly powerful use case. Your channel is amazing for not just uncovering the new (I'm on 2019) but the old that everyone can benefit!. A most powerful jedi you are.
Thank You! 1. Inspired from Your video . . I was exploring the DGET function . . and found, it can also get the value from a column on the left . . WoW! 2. In these functions, the way We write the criteria reminded Me of Advanced Filter. Best Wishes!
Madam! You just read my mind and have made my life so much easier! Grateful for you showing me that this doesn’t have to be intimidating. I have so many uses for this in my day to day work. Thank you very much 👌🏽🙏🏼
I cut my spreadsheet teeth on these database functions when I first learned Lotus 1-2-3 (showing my age here). So, I have a special place in my heart for them. However, I have found that if you use Excel's array functions (Control, Shift, Enter) with SUMIFS (and its variants COUNTIFS, MAXIFS, etc.) you can set up OR situations (within curly brackets { }) that work quite nicely.
Hi mynda. I’ve been using these ‘D’databsse function for a long time now, since I use ‘Power Pivot’ ( related tables a lot) and hence database functions are thff re best. Thanks fog introducing these functions to the rest of your subscribers. Kind regards. Martin ( South Africa). 😊
Hi Myna. Excellent video on these "hidden" functions. One thing I like with these is that in the criteria, you can make a "calculated" criteria, which allows you to use ALL of Excel's logical functionality (AND, OR, XOR, EXACT, ISBLANK etc.) in an excel formula. You make a new arbitrary criteria column, then in the cell below do a standard Excel logical test as if you were doing it for the first row of data and you were going to fill it down like a normal formula.
I have always found these D functions to be amongst the most useful in Excel. When I was working as an IT tutor I always presented the D functions immediately after the advanced filter function as the syntax is so similar. Unfortunately using these functions often renders the spreadsheet no-editable by anyone who may inherit the spreadsheet from you as very few people seem to have a good grounding in the basics of Excel these days.
Yes, I guess like everything, there is a learning curve with these functions. I think they're easier to learn than SUMIFS because the syntax is so basic, and the criteria are transparent.
These database functions (and Tables, in general), are amazing! Thank you for putting the spotlight on them. I have a worksheet that I thought would become a lot easier with these, but I'm stumped on the approach. My "raw data" rows have Month and Year columns. The Month column is filled with a "mmm" mask in the TEXT formula. Similarly, the Year column is created with the YEAR function from the original date column. I don't have an array of adjacent cells with 27x12 year and month values! How do I define the CRITERIA parameter of the DSUM function with some sort of literal YEAR:2023 MONTH:6 combination?
Because the month is a month name in text form, I would define the criteria based on the date column, not the separate year and month columns as per my examples.
Great video as always! I had a play with this the other day (I'm a royalties analyst) and was sooo disappointed that it can't be dragged down a table to be run for numerous rows of criteria. I tried using VSTACK to stack the column headers to the criteria within the formula but it doesn't seem to like that 😢 Do you know if there is another way? Thanks x
Good stuff. With the intro of tables, D functions are little more easy to follow. I didn’t know SUMIFs could not handle OR because I’m old school and SUM(IF array functions and SUMPRODUCT can handle OR.
@MyOnlineTrainingHub, 9:10, is it possible, then to use the column name each time instead of using the field name? Ex. "5" versus "Amount"? Great video and explanation on how to use this formula! Thank you!
Yes, you can use the column number or the column name, whichever you prefer. Of course, the name will make formula auditing easier when you come back to it months down the track and wonder what the formula does 😁
Hi Mynda, thanks for this. Would I be able to use this in the brilliant Learn to Track Your Money video? Would it make it easier and not have to use the pivot tables?
Many thanks for these videos. I presume all the 'database' functions need a 'criteria range' (which includes the columns and values) for the criteria part of the function unlike the counterpart functions where the criteria can be embedded in the function. It's like using the Advance Filter in Excel (with criteria and output range).
Yes, they all need the criteria range. It is very similar to advanced filter, except because it's a formula, it updates immediately if there are changes in the source or critieria table.
@@MyOnlineTrainingHub and the criteria work the same way as the advanced filter ones, which means that text criteria are "begins with" by default, so "B" would have returned the same results as "B*" and all the "Level 1" filters would have matched "Level 10", "Level 11", "Level 100" etc had they been in the data. It also means that you can use formula criteria which can be much simpler to set up when you have multiple AND/OR combinations.
If you download the example file you can see how I set it up. It's just hiding the text with a white font until there is a value in the DSUM formula cell. e.g. =ISBLANK(DSUM formula cell reference)
The reasons not to use the D*() functions is that they are slow, and can yield very complex unintended results from using the criteria range. Sumifs are straight forward and much faster. Still, kinda cute to see these old lotus 123 functions again
Thanks for sharing, Jan! I think they're good for beginners because the syntax is simple, and the criteria table is transparent...and most beginners aren't working with huge datasets.
@@MyOnlineTrainingHub Not sure why you think database functions are better than the sumifs, countifs, averageifs, minifs, maxifs etc functions. Database functions are incredibly slow and are all but non-usable on very large datasets if you use complex criteria. The ifs functions are just as easy to understand as the database functions and much faster. If you have criteria too complex for an ifs functions or an aggregate function that is not available in an ifs format, I’d use filter and then use the aggregate function on the filtered results before using database functions. I don’t think I’ve used a database function in 15 years. I consider them deprecated functionality that has been left in for no other purpose than backward compatibility.
Yes, Advanced Filter is great for one off extractions. Alternatively, if you need a more dynamic solution, you can use the FILTER function: www.myonlinetraininghub.com/excel-functions/excel-filter-function
For past five plus years I parted my ways away from Excel to Access. However, these functions are trying to pull me back to Excel. As a side note - Access does have similar functions available called as Domain aggregate functions and have similar syntax. Sounds like Microsoft is planning to cannibalize Access. Great video. Thanks a lot!!!
Hm....looks nice... The only thing is that you have to have special section of sheet to handle criteria while most of the time you use sumif in structured reports. If you need fast calculation for your own pivot will be better in most cases
How do you set the criteria without using a criteria table? I have a list of items in col A, then use a Unique formula to get column headers across row 1. How do i set the dsum to use the value from col A (Activity) and the value from row 1 (date). The sum column is Duration.
By all means, use what you're comfortable with. These are great functions for beginners because the syntax is straightforward, and the criteria are transparent.
Use the custom formula for conditional formatting, set the font color to the cell background color (white in this case) and set the formula to =$A$1="" or =isempty($A$1) if A1 is the cell you fill manually. Of course you can use other criteria as well.
@@milind_joshisorry I just translated from my language. If Excel is set to English it actually is "ISBLANK" and not "ISEMPTY". If you use a different language you might have to look it up.
I went down a long rabbit hole trying to use DSUM and getting no where only to find out that DSUM is apparently not yet fully supported on a Mac. It would be helpful if you could provide some warning if/when there are limitation of Excel functions when using a Mac.
Nice video explanation but, SUMIFS is generally more CPU and memory-efficient compared to DSUM. Here's why: 1. Performance Considerations SUMIFS: It directly processes data using ranges and built-in criteria within a formula, which minimizes the need for intermediate steps or complex calculations. Typically faster and more efficient, especially for large datasets, as it uses optimized algorithms for conditional summation. DSUM: Requires a separate criteria range, which can add complexity and overhead in terms of calculation. Uses a database-like approach, which may be less efficient for summing operations if your data isn’t in a structured table format or if your criteria are complex. 2. Memory Usage SUMIFS: More efficient in memory usage since it processes data in a streamlined manner without additional data structures. DSUM: Can consume more memory because it might have to interpret and handle a larger set of structured data and criteria separately. For large datasets or when performance is a priority, SUMIFS is generally more efficient. It is more commonly used in Excel spreadsheets for better calculation speed and lower memory usage. DSUM is more appropriate for cases where you are working with structured database-like tables and need to apply complex criteria, but it may not perform as well in terms of CPU and memory efficiency.
Thanks, David! You raise some interesting points, so I did some speed tests on 110k rows of data with 3 criteria and the results showed SUMIFS is 11% faster than DSUM, but both are lightning fast: First Calc: DSUM 10.05 milliseconds SUMIFS 9.166 milliseconds Recalc: DSUM 0.014 milliseconds SUMIFS 0.013 milliseconds That's a calc time of around 0.01 of a second for both formulas. Based on this, I think it's fair to say you can use whichever function you're comfortable with.
What if I want to stack multiple tables in this formula? For example, I want to reference 2 Tables in my database Range. I have tried AND/OR/VSTACK but non of them are working. Use case: I want to be able to achieve below: Sheet 1 - Table 1: More than Million Rows Sheet 2 - Table 2: More than Million Rows I would like to reference both Tables so that I end up pulling data from both tables using this function once.
If you're dealing with > 2 million rows, then you should be using Power Query to get the data from wherever it is stored (preferably not in the current file), consolidate it into one table, and then load it to Power Pivot aka the data model. See my latest video: ua-cam.com/video/JvnezVM2uP4/v-deo.html
I tried the DSUM function in a table with Total Row activated, but when I filtered the table, the DSUM messed up and gave a wrong result by adding the value to the Total Row as well.
Sure, but then I do not really need these functions. Neat if I can, but also unnecessary for most applications. Also if you have access to a standing SQL database or python, creating a database is a matter of minutes.
@@MyOnlineTrainingHub , Do you have any videos on the IFS function where you used OR but the system has treated it as AND? I am truly confused about this topic. Could you please help? or some notes where i can go through and understand the logic
I wasn't referring to SUMIFS and the other 'IFS FUNCTIONS. I was referring to the 'IFS group of functions as in SUMIFS, AVERAGEIFS, MAXIFS, MINIFS etc. Sorry if this wasn't clear.
I suppose, kind of. Greater than and less than filters would require you to select a lot of date items in the Slicers. It all depends on the level of granularity as to whether Slicers will be user friendly.
Sorry to hear that. I just tested them and they work for me. Please reach out via email and we can help you further: website @MyOnlineTrainingHub.com (remove the space after 'website')
Unfortunately the database function can only handle single cells. You can't drag them down for other levels. The day they released FILTER I stopped using them.
I'm afraid, Mynda, you're trying to resurrect a zombie 🙂 a) The functions are not database functions (the name is historical, a bit ridiculous nowadays, bad even in English, not to mention the translations) b) Their parameters are inconsistent with modern structured Tables. c) Have you tested to what extent these functions are optimized for processing large amounts of data? d) For example, DGET has an improperly handled condition where there are multiple occurrences of a value in the table. e) In terms of topic continuity, yes, these functions follow the advanced filter, and I acknowledge they have an advantage there.
All your videos are of advance level which will take a few months to learn. Can you provide a site for freelance work for basic excel? Apart from fiver.. detail video, how to get it? how to apply.. any data collected from Google.
Cheers, Chris. I agree regarding data and criteria. However, these days Microsoft use ''criteria' for one or multiple criteria, so I go with that most of the time too. I don't recall saying 'data are'. I usually say, 'data is'.
@@UsmanAfzal-h1s When is it EVER "unneccessary" to remind people of correct English usage? That way, people might learn. Never mention it? No-one learns...
❓Have you tried database functions before, or are they still on your "to-learn" list?
Learn more about the Advanced Excel Formulas course: bit.ly/dfunctions24course
Master Excel with my courses: bit.ly/dfunctions24courses
OMG! I've always been put off database functions because I've never been shown how to use them properly. This is an absolute gamechanger!
Awesome to hear you'll be making use of them 😁
Same here. This is now going to be my go to.
Not often I watch a video about something I've never come across these days, and something so powerful too - thank you!!
So pleased you discovered something new!
I’ve seen the database functions listed in the intellisense as I start typing in other functions but knew nothing about them.
I didn’t even know they were called database functions, I just saw DSUM and DMAX and so on.
I’ve learned something new, and very interesting today. Thank you.😍
Awesome to hear!
Thank you for this!!! The dbase functions in Excel have an incredibly powerful use case. Your channel is amazing for not just uncovering the new (I'm on 2019) but the old that everyone can benefit!. A most powerful jedi you are.
Grateful, I am. Thank you! 😁
Excellent info Mynda! I rarely use the Dbase functions and need to start using them more often, very helpful!
Great to hear, Chris. They are super handy.
Thank You!
1. Inspired from Your video . . I was exploring the DGET function . . and found, it can also get the value from a column on the left . . WoW!
2. In these functions, the way We write the criteria reminded Me of Advanced Filter.
Best Wishes!
Awesome to hear! Yes, very similar to Advanced Filter. They are of the same era 😉
Madam! You just read my mind and have made my life so much easier! Grateful for you showing me that this doesn’t have to be intimidating. I have so many uses for this in my day to day work. Thank you very much 👌🏽🙏🏼
Awesome to hear!
ok, I had been going hard at array functions to make complex criteria and this streamlines everything so nicely! Great video!
Glad you can make use of it!
I cut my spreadsheet teeth on these database functions when I first learned Lotus 1-2-3 (showing my age here). So, I have a special place in my heart for them. However, I have found that if you use Excel's array functions (Control, Shift, Enter) with SUMIFS (and its variants COUNTIFS, MAXIFS, etc.) you can set up OR situations (within curly brackets { }) that work quite nicely.
Thanks for sharing!
I wish I could give you hundreds of thumbs up!!! Thank you for the video; this will come in very handy, thank you for making the video!
🥰thanks so much!
Very Nice explanantion. Much Thanks
So nice of you 🙏
Hi mynda. I’ve been using these ‘D’databsse function for a long time now, since I use ‘Power Pivot’ ( related tables a lot) and hence database functions are thff re best. Thanks fog introducing these functions to the rest of your subscribers. Kind regards. Martin ( South Africa). 😊
Great to hear, Martin!
This is a terrific introduction to Excel's powerful and versatile database functions. Thank you for sharing, as always!
My pleasure!
I learned something new today! Great video Mynda!
Awesome! Thank you!
Through this video I have learned great formulas, thank you very much
Great to hear!
i never bother much with the DSUM functions back in the day because very few, if any, instructors explained it as good as this.
Hope you can make use of them now.
Excellent documentation with the installation steps .Thanks a lot
Glad it was helpful!
Fantastic video Mynda, great content! Thank you.
🙏 Glad you enjoyed it, Dave!
Hi Myna. Excellent video on these "hidden" functions. One thing I like with these is that in the criteria, you can make a "calculated" criteria, which allows you to use ALL of Excel's logical functionality (AND, OR, XOR, EXACT, ISBLANK etc.) in an excel formula.
You make a new arbitrary criteria column, then in the cell below do a standard Excel logical test as if you were doing it for the first row of data and you were going to fill it down like a normal formula.
Thanks for sharing!
I have always found these D functions to be amongst the most useful in Excel. When I was working as an IT tutor I always presented the D functions immediately after the advanced filter function as the syntax is so similar. Unfortunately using these functions often renders the spreadsheet no-editable by anyone who may inherit the spreadsheet from you as very few people seem to have a good grounding in the basics of Excel these days.
Yes, I guess like everything, there is a learning curve with these functions. I think they're easier to learn than SUMIFS because the syntax is so basic, and the criteria are transparent.
@MyOnlineTrainingHub I totally agree but they rarely seem to be used or known about. Hopefully your excellent video will help make them more popular.
These database functions (and Tables, in general), are amazing! Thank you for putting the spotlight on them.
I have a worksheet that I thought would become a lot easier with these, but I'm stumped on the approach. My "raw data" rows have Month and Year columns. The Month column is filled with a "mmm" mask in the TEXT formula. Similarly, the Year column is created with the YEAR function from the original date column. I don't have an array of adjacent cells with 27x12 year and month values! How do I define the CRITERIA parameter of the DSUM function with some sort of literal YEAR:2023 MONTH:6 combination?
Because the month is a month name in text form, I would define the criteria based on the date column, not the separate year and month columns as per my examples.
Excellent video. I always wondered why and when I’d use these formulas.
Glad you liked it 🙏
Great video as always! I had a play with this the other day (I'm a royalties analyst) and was sooo disappointed that it can't be dragged down a table to be run for numerous rows of criteria. I tried using VSTACK to stack the column headers to the criteria within the formula but it doesn't seem to like that 😢 Do you know if there is another way? Thanks x
You can't nest criteria inside the database functions. They must be in a table. Maybe SUMIFS/COUNTIFS etc. would suit you better.
Thanks alot! It helps me in a very positive way! Keep up the good work!❤
Happy to hear that!
Excellent function which I started using at work after your first tutorial on this function about 3 years ago ❤🙏❤
Awesome to hear!
thank you for the content ^^
I usually do it with filter and average or filter with sum, but I enjoyed to learn about this formula, thank you ^^
Glad you enjoyed it!
Great lesson. On DSUM excl Atkins, how does it know not to double count level 1 if the blank in the levels means 'all'?
🤷♀️I guess it's built into the code for the function.
Great video! Great breakdown. Thank you.
Glad you enjoyed it!
This is really a hidden gem. Thanks for revealing it 👏
My pleasure!
Thank you very much for sharing such a powerful Functions.
It will definitely going to help
Thanks a lot ..
Great to hear you can make use of them!
Good stuff. With the intro of tables, D functions are little more easy to follow. I didn’t know SUMIFs could not handle OR because I’m old school and SUM(IF array functions and SUMPRODUCT can handle OR.
Thank you! Yes, SUM(IF and SUMPRODUCT are other handy ways to handle OR criteria. Thanks for sharing.
Another great video Mynda!
Thanks so much!
@MyOnlineTrainingHub, 9:10, is it possible, then to use the column name each time instead of using the field name? Ex. "5" versus "Amount"? Great video and explanation on how to use this formula! Thank you!
Yes, you can use the column number or the column name, whichever you prefer. Of course, the name will make formula auditing easier when you come back to it months down the track and wonder what the formula does 😁
Hi Mynda, thanks for this. Would I be able to use this in the brilliant Learn to Track Your Money video? Would it make it easier and not have to use the pivot tables?
Yes, you could but I would still use the PivotTables instead 😉
Wow and amazing! Thanks a ton for this video!
Thank you! Glad you liked it.
Sounds amazing. Did you by any chance tested the performance and speed compared to sumifs? Is it faster and if yes, then is it substantial?
It's comparable to SUMIFS. I've got a video on it coming out soon.
Many thanks for these videos.
I presume all the 'database' functions need a 'criteria range' (which includes the columns and values) for the criteria part of the function unlike the counterpart functions where the criteria can be embedded in the function. It's like using the Advance Filter in Excel (with criteria and output range).
Yes, they all need the criteria range. It is very similar to advanced filter, except because it's a formula, it updates immediately if there are changes in the source or critieria table.
@@MyOnlineTrainingHub and the criteria work the same way as the advanced filter ones, which means that text criteria are "begins with" by default, so "B" would have returned the same results as "B*" and all the "Level 1" filters would have matched "Level 10", "Level 11", "Level 100" etc had they been in the data. It also means that you can use formula criteria which can be much simpler to set up when you have multiple AND/OR combinations.
I have checked it yesterday. It speed up my report a lot.
Awesome to hear!
Do you have a video with the conditional formatting to produce the other formulas automatically?
If you download the example file you can see how I set it up. It's just hiding the text with a white font until there is a value in the DSUM formula cell. e.g. =ISBLANK(DSUM formula cell reference)
The reasons not to use the D*() functions is that they are slow, and can yield very complex unintended results from using the criteria range. Sumifs are straight forward and much faster. Still, kinda cute to see these old lotus 123 functions again
Thanks for sharing, Jan! I think they're good for beginners because the syntax is simple, and the criteria table is transparent...and most beginners aren't working with huge datasets.
@@MyOnlineTrainingHub Not sure why you think database functions are better than the sumifs, countifs, averageifs, minifs, maxifs etc functions. Database functions are incredibly slow and are all but non-usable on very large datasets if you use complex criteria. The ifs functions are just as easy to understand as the database functions and much faster. If you have criteria too complex for an ifs functions or an aggregate function that is not available in an ifs format, I’d use filter and then use the aggregate function on the filtered results before using database functions. I don’t think I’ve used a database function in 15 years. I consider them deprecated functionality that has been left in for no other purpose than backward compatibility.
This is so awesome, thank you!
My pleasure!
Advance filter will be a great option to return multiple values based on criteria.
Yes, Advanced Filter is great for one off extractions. Alternatively, if you need a more dynamic solution, you can use the FILTER function: www.myonlinetraininghub.com/excel-functions/excel-filter-function
At start of my excel carier I was using these function, but lately I've found more simple sums and others.😁
Great to hear you're using the more modern functions too.
Do database functions preserve text identities? For example with leading zeroes, are the following treated uniquely: 1, 01, 001?
Yes, as long as they're entered as text, they'll be treated as text.
For past five plus years I parted my ways away from Excel to Access. However, these functions are trying to pull me back to Excel. As a side note - Access does have similar functions available called as Domain aggregate functions and have similar syntax.
Sounds like Microsoft is planning to cannibalize Access.
Great video. Thanks a lot!!!
These are very old functions that were probably based on the Access ones, or vice versa.
Very nice Video thank you for your hard work
Glad you liked it 🙏
Hm....looks nice... The only thing is that you have to have special section of sheet to handle criteria while most of the time you use sumif in structured reports. If you need fast calculation for your own pivot will be better in most cases
Yes, there is a trade off with these functions.
Thank You Very Much. Excellent
My pleasure!
Very helpful as always, I'm afraid to reflect on how much time I wasted before knowing these kinds of functions.
Glad you can make use of these functions.
This is superpower 💪❤
Have fun with these functions 😁
How do you set the criteria without using a criteria table?
I have a list of items in col A, then use a Unique formula to get column headers across row 1. How do i set the dsum to use the value from col A (Activity) and the value from row 1 (date). The sum column is Duration.
These functions require the criteria table. There's no workaround. You'd therefore be better off using SUMIFS.
Thanks ❤ but what are the drawbacks?
You have to store the criteria in cells in the worksheet. This can be both a pro and a con, depending on your preferences.
Do the criteria ranges have to be hard-coded in somewhere, or could they be built within a database formula, e.g. by some HSTACKing/VSTACKing?
No, they must be hard coded in cells.
thank you, you wise and beautiful lady.
You're welcome!
You can use countifs with arrays cant you for "or" like functionality?, but this is great,
No, not for OR criteria.
Great 👍 thanks!
Glad you liked it!
What versions of Excel are these functions available for?
All versions.
Yes I agree , it’s easier to use than sumifs, but still frankly am used to sumifs, it comes to my mind first 😀
By all means, use what you're comfortable with. These are great functions for beginners because the syntax is straightforward, and the criteria are transparent.
I am more interested to see how you did that conditional formatting
Use the custom formula for conditional formatting, set the font color to the cell background color (white in this case) and set the formula to =$A$1="" or =isempty($A$1) if A1 is the cell you fill manually. Of course you can use other criteria as well.
@milind_joshi what @IsMirDochLattens said. Or you can download the example file and see how I set it up.
This is so much easier; so well explained! Thanks 😊
@@IsMirDochLattens thanks but isempty not working
@@milind_joshisorry I just translated from my language. If Excel is set to English it actually is "ISBLANK" and not "ISEMPTY". If you use a different language you might have to look it up.
So coool!❤
Glad you liked it!
I went down a long rabbit hole trying to use DSUM and getting no where only to find out that DSUM is apparently not yet fully supported on a Mac. It would be helpful if you could provide some warning if/when there are limitation of Excel functions when using a Mac.
What makes you think these functions aren't fully supported? I'm not aware of any issues with them for Mac Excel.
Just saved me hours of work
Awesome to hear, Caleb!
Nice video explanation but,
SUMIFS is generally more CPU and memory-efficient compared to DSUM. Here's why:
1. Performance Considerations
SUMIFS:
It directly processes data using ranges and built-in criteria within a formula, which minimizes the need for intermediate steps or complex calculations.
Typically faster and more efficient, especially for large datasets, as it uses optimized algorithms for conditional summation.
DSUM:
Requires a separate criteria range, which can add complexity and overhead in terms of calculation.
Uses a database-like approach, which may be less efficient for summing operations if your data isn’t in a structured table format or if your criteria are complex.
2. Memory Usage
SUMIFS: More efficient in memory usage since it processes data in a streamlined manner without additional data structures.
DSUM: Can consume more memory because it might have to interpret and handle a larger set of structured data and criteria separately.
For large datasets or when performance is a priority, SUMIFS is generally more efficient. It is more commonly used in Excel spreadsheets for better calculation speed and lower memory usage. DSUM is more appropriate for cases where you are working with structured database-like tables and need to apply complex criteria, but it may not perform as well in terms of CPU and memory efficiency.
Thanks, David! You raise some interesting points, so I did some speed tests on 110k rows of data with 3 criteria and the results showed SUMIFS is 11% faster than DSUM, but both are lightning fast:
First Calc:
DSUM 10.05 milliseconds
SUMIFS 9.166 milliseconds
Recalc:
DSUM 0.014 milliseconds
SUMIFS 0.013 milliseconds
That's a calc time of around 0.01 of a second for both formulas. Based on this, I think it's fair to say you can use whichever function you're comfortable with.
What if I want to stack multiple tables in this formula?
For example, I want to reference 2 Tables in my database Range. I have tried AND/OR/VSTACK but non of them are working.
Use case: I want to be able to achieve below:
Sheet 1 - Table 1: More than Million Rows
Sheet 2 - Table 2: More than Million Rows
I would like to reference both Tables so that I end up pulling data from both tables using this function once.
If you're dealing with > 2 million rows, then you should be using Power Query to get the data from wherever it is stored (preferably not in the current file), consolidate it into one table, and then load it to Power Pivot aka the data model. See my latest video: ua-cam.com/video/JvnezVM2uP4/v-deo.html
Simply Awesome
Thanks a lot 😊
wow what amazing video, love it, than you
Glad you enjoyed it!
Can I know how u created the table that you showed for multiple criteria with all the employees data.
If you mean example 5, then it's a PivotTable.
If you need all results individuelly for the which Dget or xlookup cant give you, you can use the Filter function and create a dynamic table :)
Yes! I love the FILTER function for exactly this: www.myonlinetraininghub.com/excel-functions/excel-filter-function
Probably, the best 😊
Thanks so much!
can this be applied on data that id located in another closed workbook?
No, you would need to open the workbook being referenced for it to calculate.
My Excel super star !
🥰
Thanks Mynda.🙏😊
Thanks for watching!
Thank you so much !
Thanks for watching, Rajesh!
I tried the DSUM function in a table with Total Row activated, but when I filtered the table, the DSUM messed up and gave a wrong result by adding the value to the Total Row as well.
If you use the Table structured references in your DSUM it should never include the total row.
2:11 Is there a DMEDIAN()?
Thanks.
No.
Thank you.
You're welcome!
Highly aprreciated
Great to hear 🙏
Does this work with horizontal data?
It doesn't. It's designed to work with data in a tabular layout.
Wow. This is dope
Glad you like it!
Excellent
Thank you so much 😀
Thanks good
Glad you liked it!
The name tells you really what really should happen. This data should not be in a spreadsheet, but in a real Database
Not really...sure in some cases, but for single tables of data, a database is overkill.
Sure, but then I do not really need these functions. Neat if I can, but also unnecessary for most applications. Also if you have access to a standing SQL database or python, creating a database is a matter of minutes.
I used these in lotus 123 to build dashboards in the 90s!
They're the OG 😁
D-Group (Aggregate) functions were always present in MS Access.
Likely where they originated. These are very old functions.
What is limitations of sumifs or other ifs
They cannot handle OR criteria. All criteria in the 'IFS functions are treated as AND.
@@MyOnlineTrainingHub , Do you have any videos on the IFS function where you used OR but the system has treated it as AND? I am truly confused about this topic. Could you please help? or some notes where i can go through and understand the logic
I wasn't referring to SUMIFS and the other 'IFS FUNCTIONS. I was referring to the 'IFS group of functions as in SUMIFS, AVERAGEIFS, MAXIFS, MINIFS etc. Sorry if this wasn't clear.
Wait, slicers can provide that info right instead of using a function? or am I wrong.
I suppose, kind of. Greater than and less than filters would require you to select a lot of date items in the Slicers. It all depends on the level of granularity as to whether Slicers will be user friendly.
@MyOnlineTrainingHub okay, got it. Thanks
Very nice
Thanks for watching!
None of your links given in the description are working. I have tried opening them multiple times.
Sorry to hear that. I just tested them and they work for me. Please reach out via email and we can help you further: website @MyOnlineTrainingHub.com (remove the space after 'website')
3:11 So the criteria is similar to Advanced Filtering!
Yes, absolutely. 👍
Wow, thanks for sharing. And I thought I’m an expert in Excel, but looks like I’m not 😂😂
😁 glad you discovered something new.
2hours ago this would have saved me an hour of sum products!
🤦♀️you'll know for next time!
Unfortunately the database function can only handle single cells. You can't drag them down for other levels. The day they released FILTER I stopped using them.
If you want to summarise the data by levels, then PivotTables are probably best, but FILTER is also useful, or the new GROUPBY and PIVOTBY functions.
I'm afraid, Mynda, you're trying to resurrect a zombie 🙂
a) The functions are not database functions (the name is historical, a bit ridiculous nowadays, bad even in English, not to mention the translations)
b) Their parameters are inconsistent with modern structured Tables.
c) Have you tested to what extent these functions are optimized for processing large amounts of data?
d) For example, DGET has an improperly handled condition where there are multiple occurrences of a value in the table.
e) In terms of topic continuity, yes, these functions follow the advanced filter, and I acknowledge they have an advantage there.
Nice analogy to a zombie, Petr 😁
All your videos are of advance level which will take a few months to learn. Can you provide a site for freelance work for basic excel? Apart from fiver.. detail video, how to get it? how to apply.. any data collected from Google.
Excellent vid. Just to note, "data" and "criteria" are both plurals. "Data are", and "criteria are". "One datum", "one criterion".
Unnecessary to point out
Cheers, Chris. I agree regarding data and criteria. However, these days Microsoft use ''criteria' for one or multiple criteria, so I go with that most of the time too. I don't recall saying 'data are'. I usually say, 'data is'.
@@UsmanAfzal-h1s When is it EVER "unneccessary" to remind people of correct English usage? That way, people might learn. Never mention it? No-one learns...
@@chrismoule7242 what an ass you are 😅😅😅
SUM(FILTER)
Yep, or SUMPRODUCT for earlier versions of Excel.
Its not clear vedio
What do you have questions on?
Just use the filter....
That's ok, if you only want one set of conditions, but if you want multiple, then the database functions are a great alternative.
👍
🙏