Excel Database Functions - BETTER than SUMIFS, COUNTIFS etc.!

Подписаться 632 тыс.

Просмотров 123 тыс.

50% 1

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

Наука

Опубликовано:

8 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 209

@lodingwai 2 года назад

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!

@martinargimon730 2 года назад

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!

@vijayarjunwadkar 2 года назад

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!

@darrylmorgan 2 года назад

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

@blessedowo1958 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad to hear 😊

@JOJO-og2qc Год назад

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

@MyOnlineTrainingHub Год назад

Wow, thanks so much, Jojo!

@maciejnejman5126 2 года назад

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!

@lesleywilliams3947 2 года назад

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!

@roywilson9580 10 месяцев назад

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 10 месяцев назад

Yes, great points about transparency 🙏

@utubeAgape 2 года назад

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!

@wayneedmondson1065 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Thanks so much, Wayne!

@mariaalcala5159 2 года назад

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!

@bulliethepirate 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Thanks so much 😊

@chrism9037 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Cheers, Chris!

@diannedrechsler4792 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you think so!

@gregbernard7861 Год назад

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

@MyOnlineTrainingHub Год назад

Glad you liked it!

@MrNaquevedo 2 года назад

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

@rachaelhanna8523 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Thanks so much 😊

@tudor771 Год назад

thank you for explaining so well!

@MyOnlineTrainingHub Год назад

Glad you liked it!

@ag3037 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you liked it 😊

@jeromeastier462 Месяц назад

Excellent! Exactly what I needed. Cheers.

@MyOnlineTrainingHub Месяц назад

Great to hear!

@hemanttrivedi898 2 года назад

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 😊

@mohammadj.shamim9342 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Many thanks, Mohammad!

@guillaumecourtens9594 2 года назад

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!

@silversolver7809 2 года назад

Excellent, very well explained Mynda, thanks!

@MyOnlineTrainingHub 2 года назад

Thanks so much!

@a.achirou6547 Год назад

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

@MyOnlineTrainingHub Год назад

Glad it was helpful!

@sachinrv1 Год назад

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.

@emilat_laccd630 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you liked it, Emil!

@isaacmwabila5830 2 года назад

Thank you Mynda for this interesting video! 👏

@MyOnlineTrainingHub 2 года назад

My pleasure, Isaac 😊

@reginaregi3184 2 года назад

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

@MyOnlineTrainingHub 2 года назад

You are most welcome :-)

@abdulhaseeb8027 2 года назад

Amazing video and content. Really appreciate your help.

@MyOnlineTrainingHub 2 года назад

Thanks, Abdul 😊

@Seawolf159 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you like them!

@johnborg5419 2 года назад

Thanks Mynda, that was very helpful. :)

@MyOnlineTrainingHub 2 года назад

Great to hear, John!

@mickaell3451 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you liked it, Mickael!

@ExcelDude 2 года назад

Great video, thanks for sharing!

@MyOnlineTrainingHub 2 года назад

My pleasure 😊

@mattschoular8844 2 года назад

Super handy...Thanks Mynda

@MyOnlineTrainingHub 2 года назад

Great to hear, Matt!

@tomoo79 2 года назад

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

@hamidsh4789 2 года назад

Thanks, Excellent.

@MyOnlineTrainingHub 2 года назад

Glad you liked it!

@Jahspecs1 2 года назад

Extremely powerful!!!!

@MyOnlineTrainingHub 2 года назад

Glad you liked it 😊

@drewbbc7074 2 года назад

Nicely done.

@MyOnlineTrainingHub 2 года назад

Cheers 😊

@indzara 2 года назад

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 👍

@EliotPowell 2 года назад

again, really quite excellent!

@MyOnlineTrainingHub 2 года назад

Cheers, Eliot!

@mdexcelpro8495 2 года назад

Good finding it's very useful for me 👍

@MyOnlineTrainingHub 2 года назад

Great to hear 😊

@ericlei2207 2 года назад

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

@ataurrehman483 2 года назад

Thanks will learn this.my Dear Mentor

@MyOnlineTrainingHub 2 года назад

Great to hear :-)

@tomfontanella6585 Год назад

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!

@sidharthmohanty6434 2 года назад

Thanks

@MyOnlineTrainingHub 2 года назад

Welcome :-)

@DaveSlavens 2 года назад

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

@jeremyjcobb 2 года назад

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?

@zaydarendse2812 2 года назад

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

@MyOnlineTrainingHub 2 года назад

My pleasure 😊

@brianzappacosta6689 2 года назад

Thanks so much love

@MyOnlineTrainingHub 2 года назад

Glad you liked it 😊

@gagansingh3481 2 года назад

Awesome Mam 👌

@MyOnlineTrainingHub 2 года назад

Thank you 😊

@daniyalbaghi9395 2 года назад

this is great

@MyOnlineTrainingHub 2 года назад

Glad you liked it!

@ApteryxRex 2 года назад

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 😊

@aibelrapheal5176 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you liked it 😊

@ymn76ymn Год назад

It is easy to use.

@MyOnlineTrainingHub Год назад

Glad you think so 😊

@rexbk 3 месяца назад

Terimakasih

@MyOnlineTrainingHub 3 месяца назад

You're welcome 😊

@ronniemodigh7438 2 года назад

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

@taahatube1 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Great to hear 😊

@skatez69 2 года назад

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.

@shenry25 2 года назад

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.

@rory1653 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Thanks so much, Rory!

@kennethdy5650 2 года назад

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.

@nightmarehvs 2 года назад

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.

@SothearithKONGMrMuyKhmer 6 месяцев назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5qtSioTE2wY.html

@SothearithKONGMrMuyKhmer 6 месяцев назад

@@MyOnlineTrainingHub Thanks so much! 🥰

@user-kr4rk4pn2e 10 месяцев назад

good video

@MyOnlineTrainingHub 10 месяцев назад

Glad you enjoyed it 🙏

@Erni760803 9 месяцев назад

Awesome

@MyOnlineTrainingHub 9 месяцев назад

Cheers 😊

@bhqld628 Год назад

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.

@juanantonioherediajimenez2828 2 года назад

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.

@SarahChurch95 2 года назад

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.

@jared_musicSTC 2 года назад

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

@physnoct 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Must be an Australian saying 😁

@catalystinfotech960 Месяц назад

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

@MyOnlineTrainingHub Месяц назад

There are lots of ways to select data e.g. for contiguous ranges CTRL+A or CTRL+SHIFT+arrow keys. HTH.

@Cookstein2 2 года назад

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.

@afhlmd 2 года назад

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.

@zdzislawkes 2 года назад

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.

@paulwarren3738 2 года назад

Never heard of these! I thought I knew excel 😅

@MyOnlineTrainingHub 2 года назад

Hope you can make use of them, Paul!

@arunachaleswaran 2 года назад

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

@excel-businessinexcel5181 2 года назад

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!

@donjuan3887 2 года назад

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

@mahmudurrahman9052 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Not that I know of, Mahmud.

@tommyharris5817 2 года назад

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

@MyOnlineTrainingHub 2 года назад

Glad you liked it, Tommy!

@deepakrajmano950 2 года назад

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.

@dannyluu8140 2 года назад

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

@republikadugave420 5 месяцев назад

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 5 месяцев назад

You can use CUBE functions themselves: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Wvajqz7uBbs.html

@andrewsegawa7221 2 года назад

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.

@johnyoung9649 2 года назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ZCQAweoAdOw.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.

@hadireg 2 года назад

👍👍

@MyOnlineTrainingHub 2 года назад

Glad you liked it :-)

@laiheechow9216 2 года назад

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.

@ClausL1957 2 года назад

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.

@irynasavchenko91 Год назад

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

@markjones1337 2 года назад

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!

@raulcisneros7086 2 года назад

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

@sonoftoid8702 2 года назад

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.