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!!

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear, Alan!

Thanks will learn this. :) I cannot express how thankful I am for your work.

@MyOnlineTrainingHub

2 жыл бұрын

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 :)

@MyOnlineTrainingHub

2 жыл бұрын

Thanks, Darryl! 😊

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 😀

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear!

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

@MyOnlineTrainingHub

2 жыл бұрын

Fantastic to hear, Martin!

Very nice tutorial Mynda! Excel has so many hidden gems and thank you for getting them to us! Stay blessed! 👌👍

@MyOnlineTrainingHub

2 жыл бұрын

My pleasure, Vijay!

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!

@MyOnlineTrainingHub

2 жыл бұрын

Fantastic to hear, Lesley!

Great video! I didn’t know about those formulas until I saw your video! Thanks Mynda

@MyOnlineTrainingHub

2 жыл бұрын

Glad you’ll find them useful, Maria!

Hi Mynda. Thanks for this great summary of these less well know and used Database functions. Thumbs up!!

@MyOnlineTrainingHub

2 жыл бұрын

Thanks so much, Wayne!

the most clear and full explanation of D~ function on youtube thank you so much

@MyOnlineTrainingHub

Жыл бұрын

Wow, thanks so much, Jojo!

I did not know about these and will certainly think about how & when to use them - thank you for the great instruction!

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful!

Great video Mynda, I rarely use them so this was very helpful!

@MyOnlineTrainingHub

2 жыл бұрын

Cheers, Chris!

Another good one, I’ll have to check these out more.

@MyOnlineTrainingHub

Жыл бұрын

Glad you liked it!

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.

@MyOnlineTrainingHub

9 ай бұрын

Yes, great points about transparency 🙏

Great video, fully and clearly explains all the features and rules. Nice one .

@MyOnlineTrainingHub

2 жыл бұрын

Thanks so much 😊

Excellent! Exactly what I needed. Cheers.

@MyOnlineTrainingHub

Ай бұрын

Great to hear!

Clearly explained and well presented. Thank you very much for this.

@MyOnlineTrainingHub

2 жыл бұрын

Thanks so much 😊

thank you for explaining so well!

@MyOnlineTrainingHub

Жыл бұрын

Glad you liked it!

Very useful! This is a lot easier than writing large sumifs with many criteria.

@MyOnlineTrainingHub

2 жыл бұрын

Glad you think so!

Excellent, very well explained Mynda, thanks!

@MyOnlineTrainingHub

2 жыл бұрын

Thanks so much!

Thank you. Great explanation. I didn't know those functions before.

@MyOnlineTrainingHub

Жыл бұрын

Glad it was helpful!

As usual, professor thank you very much. You are great.

@MyOnlineTrainingHub

2 жыл бұрын

Many thanks, Mohammad!

Awesome stuff. I think I love you, in an Excel kind of way.

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it 😊

Nicely explained Mynda.. I was using database functions for sometimes but there were some confusions which sorted out by this video. Thanks for sharing.

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear, Hemant 😊

Amazing video and content. Really appreciate your help.

@MyOnlineTrainingHub

2 жыл бұрын

Thanks, Abdul 😊

Thanks Mynda, that was very helpful. :)

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear, John!

Thank you - I didn't use these before but seems to be very handy

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it, Emil!

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!!!!

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear 😊

@sharienger7223

2 жыл бұрын

Me too!!! I can’t wait to use these functions!!

Thank you Mynda for this interesting video! 👏

@MyOnlineTrainingHub

2 жыл бұрын

My pleasure, Isaac 😊

Thanks I think I need to look on these function, they could be interesting. Cheers

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it, Mickael!

Thank You. Very much informative & useful video...

@MyOnlineTrainingHub

2 жыл бұрын

You are most welcome :-)

Great video, thanks for sharing!

@MyOnlineTrainingHub

2 жыл бұрын

My pleasure 😊

Wow, i've indeed never seen these before. These could be very useful.

@MyOnlineTrainingHub

2 жыл бұрын

Glad you like them!

Nicely done.

@MyOnlineTrainingHub

2 жыл бұрын

Cheers 😊

Super handy...Thanks Mynda

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear, Matt!

again, really quite excellent!

@MyOnlineTrainingHub

2 жыл бұрын

Cheers, Eliot!

Thanks, Excellent.

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it!

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.

@MyOnlineTrainingHub

2 жыл бұрын

Great points!

Extremely powerful!!!!

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it 😊

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.

@MyOnlineTrainingHub

Жыл бұрын

Yes, nice idea.

Good finding it's very useful for me 👍

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear 😊

Thanks will learn this.my Dear Mentor

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear :-)

Very useful tips 👍 hopefully this will be implemented in the future in the normal excel filter tho lol

@MyOnlineTrainingHub

2 жыл бұрын

Not sure what you mean by 'the normal Excel filter'.

This is great, not something I’ve ever stumbled across, i will need to consider when this is more appropriate than sumifs!

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear, Tom! 😊

Thanks so much love

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it 😊

this is great

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it!

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

@MyOnlineTrainingHub

Жыл бұрын

Yes. A lot of early Excel features are the same as Lotus 1-2-3!

I always learn something when watching your vids. Keep up the great work.

@MyOnlineTrainingHub

2 жыл бұрын

Thanks so much, Rory!

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)?

@MyOnlineTrainingHub

2 жыл бұрын

Yes, it sure can 😊

@nelsonvasconcelos5409

Жыл бұрын

Td to dx to😮 No h

Awesome Mam 👌

@MyOnlineTrainingHub

2 жыл бұрын

Thank you 😊

Thanks

@MyOnlineTrainingHub

2 жыл бұрын

Welcome :-)

How did i miss these functions?!! :( thanks for sharing :)

@MyOnlineTrainingHub

2 жыл бұрын

My pleasure 😊

Awesome

@MyOnlineTrainingHub

8 ай бұрын

Cheers 😊

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.

@MyOnlineTrainingHub

2 жыл бұрын

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 😊

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.

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear! There’s no compatibility issues 👍

First I thought this is same as filter or countif functions ... But this is better ♥️

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it 😊

good video

@MyOnlineTrainingHub

10 ай бұрын

Glad you enjoyed it 🙏

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.

@MyOnlineTrainingHub

2 жыл бұрын

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.

@jeremyjcobb

2 жыл бұрын

@@MyOnlineTrainingHub Perhaps I missed it. Where did you show the hard-coding syntax?

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?

@MyOnlineTrainingHub

2 жыл бұрын

In some rare instances, as explained here: www.decisionmodels.com/calcsecretsi.htm

Thank you! Can we use dget function to get multiple rows or columns? Like a substitute to pivot table?

@MyOnlineTrainingHub

2 жыл бұрын

Only with multiple DGET formulas, because remember DGET can only return a single value and it must be the only match in the table.

Terimakasih

@MyOnlineTrainingHub

2 ай бұрын

You're welcome 😊

"Donkey's years" didn't know that one!

@MyOnlineTrainingHub

2 жыл бұрын

Must be an Australian saying 😁

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?

@MyOnlineTrainingHub

2 жыл бұрын

They’re not volatile. I haven’t speed tested them, but I’ve also not heard of them being slow.

It is easy to use.

@MyOnlineTrainingHub

Жыл бұрын

Glad you think so 😊

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...

@MyOnlineTrainingHub

2 жыл бұрын

Hi Stephen, filtering with the column drop down buttons is a manual process, filtering with formulas is dynamic/automatic.

@shenry25

2 жыл бұрын

@@MyOnlineTrainingHub Many thanks.

Hi Mynda! Great video. Is there anything similar in power query?

@MyOnlineTrainingHub

2 жыл бұрын

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.

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?

@MyOnlineTrainingHub

2 жыл бұрын

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.

@roryarchibald8538

2 жыл бұрын

@@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?

@MyOnlineTrainingHub

2 жыл бұрын

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 😛

@roryarchibald8538

2 жыл бұрын

@@MyOnlineTrainingHub Shame - I thought they were about to become a lot more useful to me! :)

Thank u i will watch this video in my exam for a 100 % 😀😀😀😀😀

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear 😊

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 "

@MyOnlineTrainingHub

2 жыл бұрын

Yep, I'm using dd/mm/yyyy, so you're right to change the format of the filter critieria.

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!

@MyOnlineTrainingHub

6 ай бұрын

I have this video on Project Management that might be of use to you: kzread.info/dash/bejne/Z6WotcuohKiYp7w.html

@SothearithKONGMrMuyKhmer

6 ай бұрын

@@MyOnlineTrainingHub Thanks so much! 🥰

Mynda, as always thanks for sharing. Why do you say that they´re more powerful than sumifs and countifs? I didn´t get it!

@MyOnlineTrainingHub

2 жыл бұрын

Because with database functions you can have both AND and OR criteria, but with the ...IFS functions all criteria is considered AND.

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?

@MyOnlineTrainingHub

2 жыл бұрын

Good question. These functions are designed to work with tabular data. Having headers like that tells me that it's not tabular.

Please how did you select the table data? or you used a shortcut?

@MyOnlineTrainingHub

25 күн бұрын

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 ?

@MyOnlineTrainingHub

2 жыл бұрын

You mean like this: www.myonlinetraininghub.com/excel-factor-15-the-lazy-lookup

@arunachaleswaran

2 жыл бұрын

@@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.

@MyOnlineTrainingHub

2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Can you use a nested OFFSET for the criteria, so that you always have the full grid selected without Excel assuming you want "all"?

@MyOnlineTrainingHub

Жыл бұрын

Probably.

Never heard of these! I thought I knew excel 😅

@MyOnlineTrainingHub

2 жыл бұрын

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

@MyOnlineTrainingHub

2 жыл бұрын

No, the database functions don't work on closed workbooks.

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.

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear!

How does it compare to sum(filter(...)), using filter you can turn any function into a database function? Eg, percentile(filter(...))

@MyOnlineTrainingHub

2 жыл бұрын

FILTER is amazing. These functions are old. They don't compete with FILTER, but they're great alternatives to the IFs functions.

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?

@MyOnlineTrainingHub

2 жыл бұрын

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.

Can you somehow reference a database loaded into a datamodel? Tried by referencing it by a cubemember inside dsum formula but it doesnt work ...

@MyOnlineTrainingHub

4 ай бұрын

You can use CUBE functions themselves: kzread.info/dash/bejne/iaqVzNOzZ9ioktY.html

Hi , is there any function similar to Query in Google sheet for Excel ? Thanks

@MyOnlineTrainingHub

2 жыл бұрын

Not that I know of, Mahmud.

Can this be use to count unique values?

@MyOnlineTrainingHub

2 жыл бұрын

No, but if you have Microsoft 365 then there is a UNIQUE function: www.myonlinetraininghub.com/excel-functions/excel-unique-function

Hi Mynda, can we use formula in the criteria field?

@MyOnlineTrainingHub

2 жыл бұрын

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.

@dannyluu8140

2 жыл бұрын

@@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?

@MyOnlineTrainingHub

2 жыл бұрын

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

👍👍

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it :-)

Brilliant. Why even use Sumifs when DSUM is much quicker?

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it, Tommy!

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

@MyOnlineTrainingHub

2 жыл бұрын

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

@andrewsegawa7221

2 жыл бұрын

@@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.

Just to confirm, each database row is included in the summation only once even if it matches multiple rows of the criteria, Correct?

@MyOnlineTrainingHub

2 жыл бұрын

Yes, the DGET function returns the first match. If you want multiple matches, you can use the FILTER function: kzread.info/dash/bejne/jHeFo9men6TKf9o.html

@johnyoung9649

2 жыл бұрын

@@MyOnlineTrainingHub Actually, I meant like DSUM would only include rows once even if they matched multiple criteria rows.

@MyOnlineTrainingHub

2 жыл бұрын

Ah, no it will double count them if the criteria is on separate rows like in the OR scenario.

How come i can't download the workbook???

@MyOnlineTrainingHub

2 жыл бұрын

Probably a browser issue. Try a different browser and be sure to follow the instructions on the page.

Unfornutately I can´t download the excel file :-(

@MyOnlineTrainingHub

2 жыл бұрын

Hmmm, not sure why that would be. Give it a try again now please, Claus.

One of life's quirks. Man talking about a database......boring Woman talking about a database......not boring.

@MyOnlineTrainingHub

2 жыл бұрын

Glad you enjoyed it, Mark!

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?

@MyOnlineTrainingHub

Жыл бұрын

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

And just like that… now I think I am using more pivot tables than I actually need.

@MyOnlineTrainingHub

2 жыл бұрын

:-) 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.

@raulcisneros7086

2 жыл бұрын

@@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!!

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.

@MyOnlineTrainingHub

2 жыл бұрын

If you use the filters on the table and do the math you'll see what I mean.

Ach jo, škoda že to není český :-(

@MyOnlineTrainingHub

2 жыл бұрын

Hopefully you can follow the file.

@pavolsvorc7272

2 жыл бұрын

@@MyOnlineTrainingHub Thank you for answer.

Excellent

@MyOnlineTrainingHub

Жыл бұрын

So pleased you liked it 🙏