Excel Database Functions - BETTER than SUMIFS, COUNTIFS etc.! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Guillaume
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.
reply
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.
reply
Kenneth
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 -
reply
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 -
reply
Zdzis-aw
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?
reply
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?
reply
DEEPAKRAJ
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?
reply
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?
reply
Ronnie
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?
reply
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?
reply
Sonoftoid
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.
reply
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.
reply
Alan
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!!
reply
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!!
reply
Stephen
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...
reply
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...
reply
Dave
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)?
reply
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)?
reply
Maciej
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 -
reply
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 -
reply
Add a review, comment
Other channel videos
