Excel Database Functions - BETTER than SUMIFS, COUNTIFS etc.!
Вставка
- Опубліковано 31 лип 2024
- Excel Database Functions are similiar to the *IFS functions, except better. Download the Excel file here: www.myonlinetraininghub.com/e...
0:00 Introduction
0:22 Database Function Syntax
1:27 Database Function Example - Multiple AND Criteria
3:33 Database Function Example - Multiple AND OR Criteria
4:22 Database Function Example - Multiple OR Criteria, Same Field
4:52 Database Function Example - Multiple OR Criteria, Different Field
5:45 Database Function Example - Wildcards
6:39 Database Function Example - DGET Function
7:37 Database Function Rules
CAUTION: The multiple OR criteria on different fields example (4) will aggregate all data which is level 1, including level 1 for Atkins (because the name field is blank on the first criteria row), plus all other levels except those that are for Atkins (because the Type field is blank on the second criteria row). Caution: it will double count Level 1 not equal to Atkins and this is probably not what you want.
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy - Наука та технологія
Thanks for sharing this! I've seen these "D" functions around and have been using Excel for years (for work) without being curious enough to look into them (getting by with SUMIFS and COUNTIFS etc). Now I can add one more tool to the Excel toolbox - thanks again!!
Great to hear, Alan!
Very nice tutorial Mynda! Excel has so many hidden gems and thank you for getting them to us! Stay blessed! 👌👍
My pleasure, Vijay!
Thanks will learn this. :) I cannot express how thankful I am for your work.
Glad to hear 😊
Hi Mynda!Great Run Through Of Database Functions,I Learnt These Powerful Functions While Working With Access Back In The Day...Thank You :)
Thanks, Darryl! 😊
Thanks Mynda. I use database functions ALL the time . I have created a whole DATA model with various tables fro my business that tracks orders, customers, invoices and so on, so the Database functions are a must.,.. Nice tutorial .Hope more people start using Excel's Data Model and these functions... Regards and thanks once again for you excellent tutorials. Bye for now
Fantastic to hear, Martin!
Hi Mynda. Thanks for this great summary of these less well know and used Database functions. Thumbs up!!
Thanks so much, Wayne!
This is great. Up to now I used some other functionalities of Excel like tables, Power Query, sumifs and similar, array formulas, advanced filtering but only heard about database functions. Now I will harness them to the work 😀
Great to hear!
Great video! I have never used these functions but as I watched I was thinking of all the ways I can use a number of them in my reports. Thank you!
Fantastic to hear, Lesley!
the most clear and full explanation of D~ function on youtube thank you so much
Wow, thanks so much, Jojo!
Great video! I didn’t know about those formulas until I saw your video! Thanks Mynda
Glad you’ll find them useful, Maria!
Great video Mynda, I rarely use them so this was very helpful!
Cheers, Chris!
I did not know about these and will certainly think about how & when to use them - thank you for the great instruction!
Glad it was helpful!
Great video, fully and clearly explains all the features and rules. Nice one .
Thanks so much 😊
Great video. The D functions also have the great bonus of having the criteria visible so no trying to work out what has been filtered in or out of the calculation. I also love the advanced filter for the same reason.
Yes, great points about transparency 🙏
Clearly explained and well presented. Thank you very much for this.
Thanks so much 😊
Excellent! Exactly what I needed. Cheers.
Great to hear!
Excellent, very well explained Mynda, thanks!
Thanks so much!
Another good one, I’ll have to check these out more.
Glad you liked it!
Very useful! This is a lot easier than writing large sumifs with many criteria.
Glad you think so!
Nicely explained Mynda.. I was using database functions for sometimes but there were some confusions which sorted out by this video. Thanks for sharing.
Great to hear, Hemant 😊
As usual, professor thank you very much. You are great.
Many thanks, Mohammad!
thank you for explaining so well!
Glad you liked it!
Awesome stuff. I think I love you, in an Excel kind of way.
Glad you liked it 😊
Thank you. Great explanation. I didn't know those functions before.
Glad it was helpful!
Thank you Mynda for this interesting video! 👏
My pleasure, Isaac 😊
Amazing video and content. Really appreciate your help.
Thanks, Abdul 😊
Thanks Mynda, that was very helpful. :)
Great to hear, John!
I’m in the middle of preparing budgets now, this will come in very handy, looks much better than using sumifs, etc.. Thank you so much!!!!
Great to hear 😊
Me too!!! I can’t wait to use these functions!!
Super handy...Thanks Mynda
Great to hear, Matt!
Thank You. Very much informative & useful video...
You are most welcome :-)
Thank you - I didn't use these before but seems to be very handy
Glad you liked it, Emil!
again, really quite excellent!
Cheers, Eliot!
I used those a few years ago for KPI calculations. It was an elegant way to show the criteria to the customer without digging into formulas.
One thing in the criteria is that the order of the columns doesn’t matter, since it’s based on matching labels. In the example from the video, the criteria on level could be between the two dates and it would work the same. Which is great because if some columns are added or the order changed in the data(base), you don’t have to rewrite the formula 🤠
Anyway to keep it more easy to read, I use the same columns order in data and in criteria.
Great points!
Wow, i've indeed never seen these before. These could be very useful.
Glad you like them!
Great video, thanks for sharing!
My pleasure 😊
Nicely done.
Cheers 😊
Thanks I think I need to look on these function, they could be interesting. Cheers
Glad you liked it, Mickael!
Thanks for D useful video. We can use a drop-down list and MATCH to return a number which intern feeds to column number in DSUM.
Yes, nice idea.
Extremely powerful!!!!
Glad you liked it 😊
Thanks, Excellent.
Glad you liked it!
Good finding it's very useful for me 👍
Great to hear 😊
Thanks will learn this.my Dear Mentor
Great to hear :-)
I always learn something when watching your vids. Keep up the great work.
Thanks so much, Rory!
Thanks so much love
Glad you liked it 😊
Awesome Mam 👌
Thank you 😊
This is great, not something I’ve ever stumbled across, i will need to consider when this is more appropriate than sumifs!
Great to hear, Tom! 😊
Very useful tips 👍 hopefully this will be implemented in the future in the normal excel filter tho lol
Not sure what you mean by 'the normal Excel filter'.
this is great
Glad you liked it!
How did i miss these functions?!! :( thanks for sharing :)
My pleasure 😊
Thanks
Welcome :-)
Thanks! I've seen the functions but I've never dug into them yet. This was a good primer for getting started. One question I have, can the criteria reference be to a Criteria Excel Table (with matching headers for the columns you want to filter on)?
Yes, it sure can 😊
Td to dx to😮 No h
Never used these. Thanks for sharing. The criteria table allowing formulas and the functions handling OR criteria are promising. We can have user control the output via inputs that have multiple conditions. Will give it a try.
Any version compatibility issues with these functions? Thanks.
Great to hear! There’s no compatibility issues 👍
Thanks so much Mynda. These used to be my go to formulas before sumifs etc came along. It’s been almost 20 years since I last used the database functions. Were these called Dimensional Aggregates at one point?
Setting criteria this way can be far more legible that long complex sumifs or averageifs
I can’t remember though, can the field value be a cell reference with the column name as a string? If so the column being aggregated could by dynamic with use of data validation or even a slicer/pivot table on list result.
Thanks, Justin! I don’t remember them being called dimensional aggregates 🤔 yes, the field can be a cell reference and the column name a string 😊
I did not know about these. Many years ago I taught database functions in Lotus 1-2-3 where the criteria were structured this same way. I guess Microsoft developers knew a good idea when they saw it
Yes. A lot of early Excel features are the same as Lotus 1-2-3!
Awesome
Cheers 😊
good video
Glad you enjoyed it 🙏
First I thought this is same as filter or countif functions ...
But this is better ♥️
Glad you liked it 😊
Terimakasih
You're welcome 😊
Hello Mynda and thanks for the tutorial, i saw there was another question on volatility, but I would like to expand on that. I read somewhere that Sumifs and Countifs become volatile when referencing another worksheet, is that the case also for the database formulas?
In some rare instances, as explained here: www.decisionmodels.com/calcsecretsi.htm
Good stuff. I would like to see the video address (1) the syntax of the function without using a separate table for conditions and (2) how the function handles double counting when a value meets both filtering conditions.
Thanks, Jeremy! Good suggestions. I showed how you can hard key the conditions (criteria) into the formula in the video. The criteria are considered AND and therefore you shouldn't get double counting.
@@MyOnlineTrainingHub Perhaps I missed it. Where did you show the hard-coding syntax?
what is the efficiency like for database functions as apposed to *IF functions when dealing with large data sets? Also, are they fully volatile or only calculate when something in the relevant range is updated?
They’re not volatile. I haven’t speed tested them, but I’ve also not heard of them being slow.
Thank you!
Can we use dget function to get multiple rows or columns? Like a substitute to pivot table?
Only with multiple DGET formulas, because remember DGET can only return a single value and it must be the only match in the table.
It is easy to use.
Glad you think so 😊
Hi Mynda, Thank you.
Just a note. I was working on the file from your page and the formula was returning zero. I realized that it's because the date in your file is set to dd/mm/yyyy. I suspect (not sure) that each computer has a default setting for reading formulas, and mine requires dates to be in mm/dd/yyyy to be properly read. So when I changed the date on the criteria table from "
Yep, I'm using dd/mm/yyyy, so you're right to change the format of the filter critieria.
"Donkey's years" didn't know that one!
Must be an Australian saying 😁
Thank u i will watch this video in my exam for a 100 % 😀😀😀😀😀
Great to hear 😊
As always, another interesting, clear video. Many thanks.
In this particular one, what is the difference with using the column filters? The filters seem to be much easier to use. I fear the answer is in another video that I have yet to watch...
Hi Stephen, filtering with the column drop down buttons is a manual process, filtering with formulas is dynamic/automatic.
@@MyOnlineTrainingHub Many thanks.
Can you use a nested OFFSET for the criteria, so that you always have the full grid selected without Excel assuming you want "all"?
Probably.
Hi Mynda! Great video. Is there anything similar in power query?
In Power Query? You can use Group By to summarise the data, but Power Query isn't really the tool for database type formulas, that's more the domain of Excel.
Hi. I just wanted to ask what is Your relationship to the function of type D... when the database contains columns with text (eg: A100, A1000, A10000 ...) and You provide A100 in the criterion...
The results are the same as for the A100* criterion.
What do You think about it?
Good question. These functions are designed to work with tabular data. Having headers like that tells me that it's not tabular.
Database functions are a very powerful tool. I have been using it for many years. I even developed a special application with an interface to organize any kind of queries to the database. I recommend you to listen to this lecture carefully, you will definitely need it! Mynda, I apologize for the harsh words I said before.
Great to hear!
This is awesome thanks! Is there any way to code criteria into formula bar? Or do they have to be setup in a table like you have?
Cheers, Jared! You can hard code the criteria, but I don’t usually recommend doing that for any formulas because it can result in a lot of maintenance if you want to update multiple formulas that all have the same criteria.
@@MyOnlineTrainingHub How do you hardcode criteria? AFAIK, it has to be a range (which is the biggest drawback to these functions generally). Have I missed something?
Hi Rory, I thought I tested this =DSUM(DataTable[#All],"Amount",{"Type";"Level 1"}) back when I created the video, but I must have imagined it because it doesn't work 😛
@@MyOnlineTrainingHub Shame - I thought they were about to become a lot more useful to me! :)
Thanks. Do you have a video about creating a database with a nice dashboard for any projects management (i.e. Contract management or inventory management). Thanks in advance for your sharing!
I have this video on Project Management that might be of use to you: ua-cam.com/video/5qtSioTE2wY/v-deo.html
@@MyOnlineTrainingHub Thanks so much! 🥰
How to change the font size of drop down lists? Actually i am using 75% screen fit size so that i can view all columns in one screen, In that if i select the drop down list cell it’s content not at all visible & font size is so small. Can you please help me to work on this?
Hi Deepakra, you can't change the font size in a drop down, so I suggest you change your zoom to 100% and instead change the font size of the data in the columns to fit it better.
How does it compare to sum(filter(...)), using filter you can turn any function into a database function? Eg, percentile(filter(...))
FILTER is amazing. These functions are old. They don't compete with FILTER, but they're great alternatives to the IFs functions.
Mynda, as always thanks for sharing. Why do you say that they´re more powerful than sumifs and countifs? I didn´t get it!
Because with database functions you can have both AND and OR criteria, but with the ...IFS functions all criteria is considered AND.
Never heard of these! I thought I knew excel 😅
Hope you can make use of them, Paul!
Will dsum work to if you are linking it to an external workbook and don't have it open
No, the database functions don't work on closed workbooks.
Hi , is there any function similar to Query in Google sheet for Excel ? Thanks
Not that I know of, Mahmud.
Can you somehow reference a database loaded into a datamodel? Tried by referencing it by a cubemember inside dsum formula but it doesnt work ...
You can use CUBE functions themselves: ua-cam.com/video/Wvajqz7uBbs/v-deo.html
Please how did you select the table data? or you used a shortcut?
There are lots of ways to select data e.g. for contiguous ranges CTRL+A or CTRL+SHIFT+arrow keys. HTH.
Superb Minda… Are there any ways to do intersection sum of tables ?
You mean like this: www.myonlinetraininghub.com/excel-factor-15-the-lazy-lookup
@@MyOnlineTrainingHub Not like this .. Having 2 different tables with a single key column & intersecting it to find the sum of rows present in both columns. Like a power query merge.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
👍👍
Glad you liked it :-)
Can this be use to count unique values?
No, but if you have Microsoft 365 then there is a UNIQUE function: www.myonlinetraininghub.com/excel-functions/excel-unique-function
When are these Database functions needed? If I have a dataset with 10’s of millions of records, will these still come in handy because the table would seize to be helpful since we Excel table can’t contain over 1.048 M
If you have that much data you should use Power Pivot and then you'll have access to the DAX Function library. More on Power Pivot here: www.myonlinetraininghub.com/power-pivot-course
@@MyOnlineTrainingHub thanks for the content. I have started reading about DAX but I am considering enrolling for a course in Power Pivots. I am a big follower of your channel.
One of life's quirks.
Man talking about a database......boring
Woman talking about a database......not boring.
Glad you enjoyed it, Mark!
Hi Mynda, can we use formula in the criteria field?
Hi Danny, no, you can only reference cells in the worksheet. You can put formulas in those cells. You just can't nest a formula in the database functions.
@@MyOnlineTrainingHub
Thanks Mynda, I am currently using SUMIFS formula with 2 criteria’s, the 1st criteria I m using the wildcard, but the 2nd criteria is depending on the row number. Is there another way to solve this other than SUMIFS formula?
Maybe with SUMPRODUCT. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How come i can't download the workbook???
Probably a browser issue. Try a different browser and be sure to follow the instructions on the page.
Brilliant. Why even use Sumifs when DSUM is much quicker?
Glad you liked it, Tommy!
Just to confirm, each database row is included in the summation only once even if it matches multiple rows of the criteria, Correct?
Yes, the DGET function returns the first match. If you want multiple matches, you can use the FILTER function: ua-cam.com/video/ZCQAweoAdOw/v-deo.html
@@MyOnlineTrainingHub Actually, I meant like DSUM would only include rows once even if they matched multiple criteria rows.
Ah, no it will double count them if the criteria is on separate rows like in the OR scenario.
Unfornutately I can´t download the excel file :-(
Hmmm, not sure why that would be. Give it a try again now please, Claus.
I swear I am doing everything exactly the way you're showing it, but it returns me nothing but the minus sign! Why it is so?
Hard to say without seeing your file and formula. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I may very well be wrong but I believe your comment about double counting in example (4) is incorrect. What will happen is that all Level 1 invoices will be included as well as any invoices from all other Levels not equal to "Atkins". This is not double counting.
If you use the filters on the table and do the math you'll see what I mean.
And just like that… now I think I am using more pivot tables than I actually need.
:-) glad you like the database functions, Raul! I don't think you can ever use PivotTables more than you need. They're more robust than any formula and easier to create.
@@MyOnlineTrainingHub Thanks! True, but sometimes I create report out of pivot tables (using a Getpivot function where arguments are referenced in the reporting table, so I can scroll formulas) and I need Totals by both Region and Product. So if the pivot has Region totals (and each region has almost the same Products) when I want totals for Products I just don’t’ have that available in that pivot and need a second Pivot.. perhaps for that reporting row I can use Database Functions (that is, for some aggregations)? Thanks!!
Ach jo, škoda že to není český :-(
Hopefully you can follow the file.
@@MyOnlineTrainingHub Thank you for answer.
Excellent
So pleased you liked it 🙏