❓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
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.
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.
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.😍
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 👌🏽🙏🏼
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!
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). 😊
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.
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.
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.
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.
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.
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
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.
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
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.
@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 😁
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.
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.
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)
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.
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.
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: ruclips.net/video/JvnezVM2uP4/видео.html
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.
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 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.
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.
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')
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...
@@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.
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.
@@adamnealis Excel's handling of dates is a human rights violation if you ask me at the wrong time. My colleagues smile on a regular basis when my biweekly rant about k#lling 100's of MS programmers for their basic failures happens. After 30 years they still have not mastered a copy of word perfect 5.1 level 'underwater screen' with all the formatting/codes/styles in a simple overview.
❓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!
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!
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! 😁
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!
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!
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.
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!
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 😉
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!
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!
Excellent documentation with the installation steps .Thanks a lot
Glad it was helpful!
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!
Through this video I have learned great formulas, thank you very much
Great to hear!
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!
Fantastic video Mynda, great content! Thank you.
🙏 Glad you enjoyed it, Dave!
This is really a hidden gem. Thanks for revealing it 👏
My pleasure!
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.
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!
Excellent video. I always wondered why and when I’d use these formulas.
Glad you liked it 🙏
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.
Great video! Great breakdown. Thank you.
Glad you enjoyed it!
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.
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!
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.
Another great video Mynda!
Thanks so much!
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.
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.
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.
Wow and amazing! Thanks a ton for this video!
Thank you! Glad you liked it.
This is so awesome, thank you!
My pleasure!
I have checked it yesterday. It speed up my report a lot.
Awesome to hear!
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
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.
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.
Thank You Very Much. Excellent
My pleasure!
@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 😁
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.
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.
Great 👍 thanks!
Glad you liked it!
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.
Very nice Video thank you for your hard work
Glad you liked it 🙏
Simply Awesome
Thanks a lot 😊
thank you, you wise and beautiful lady.
You're welcome!
Highly aprreciated
Great to hear 🙏
My Excel super star !
🥰
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)
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.
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.
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
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.
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: ruclips.net/video/JvnezVM2uP4/видео.html
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.
Thank you so much !
Thanks for watching, Rajesh!
Probably, the best 😊
Thanks so much!
Thanks Mynda.🙏😊
Thanks for watching!
wow what amazing video, love it, than you
Glad you enjoyed it!
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.
Just saved me hours of work
Awesome to hear, Caleb!
Wow, thanks for sharing. And I thought I’m an expert in Excel, but looks like I’m not 😂😂
😁 glad you discovered something new.
So coool!❤
Glad you liked it!
What versions of Excel are these functions available for?
All versions.
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.
Thank you.
You're welcome!
Excellent
Thank you so much 😀
Wow. This is dope
Glad you like 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.
I used these in lotus 123 to build dashboards in the 90s!
They're the OG 😁
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.
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.
2hours ago this would have saved me an hour of sum products!
🤦♀️you'll know for next time!
Does this work with horizontal data?
It doesn't. It's designed to work with data in a tabular layout.
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 😁
Very nice
Thanks for watching!
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
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')
2:11 Is there a DMEDIAN()?
Thanks.
No.
3:11 So the criteria is similar to Advanced Filtering!
Yes, absolutely. 👍
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 😅😅😅
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.
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.
SUM(FILTER)
Yep, or SUMPRODUCT for earlier versions of Excel.
at this point just use python environment in excel and pandas to filter and describe data
Sure, if you have Python and the skills. 👍
Python is a PITA, and for complex queries, pandas is too.
@@adamnealis no its not
@hyperadapted yes it is... It's subjective.
@ for me it’s not. See, pointless argument
👍
🙏
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.
Its not clear vedio
What do you have questions on?
Your dates are not formatted 'wonky' but 'correctly'. Day/month/year should be mandatory. IMNSHO.
why not so humble? :D
I agree, dd/mm/yyyy should be mandatory 😁
Excel's handling of dates is and always has been awful.
@@adamnealis Excel's handling of dates is a human rights violation if you ask me at the wrong time. My colleagues smile on a regular basis when my biweekly rant about k#lling 100's of MS programmers for their basic failures happens. After 30 years they still have not mastered a copy of word perfect 5.1 level 'underwater screen' with all the formatting/codes/styles in a simple overview.
no good