Dsum queries your Excel data and provides answers based on the criteria that you provide to the dsum function.
More to the point in order to use dsum you have to decide to layout your spreadsheet using best practice spreadsheet design principles.
In a nutshell that is data that is tabular with row headings and you do not mix up data and reports i.e. all report sheets are fed by data sheets like the one below and reports never ever link to other reports.
The spreadsheet design above is perfect for a dsum function and is the way that I recommend that you lay out your data in general.
I explain the excel dsum function in detail in the youtube video further down on this page.
The dsum function in figure 2 is acting on the data in figure 1, this data has range name “StockData” and we are summing the “Volume” column using the criteria that has the range name “VolumeCriteria”.
Notice that the top row of the criteria in figure 2 has column headers with the second row having the criteria in a “query by example” layout.
I then verify (check digit) the result of the dsum by using a subtotal function.
Get the spreadsheet used in the tutorial above.
Also get updates when new blog or video tutorials are released.
Figure 1
The spreadsheet design above is perfect for a dsum function and is the way that I recommend that you lay out your data in general.
I explain the excel dsum function in detail in the youtube video further down on this page.
Dsum Case Study
I want the total volume of shares traded on the NYSE only on Fridays for the ticker “bac” for the data set in the spreadsheet. This is perfect for a dsum function because dsum can behave like a complex autofilter or sql query.Figure 2
The dsum function in figure 2 is acting on the data in figure 1, this data has range name “StockData” and we are summing the “Volume” column using the criteria that has the range name “VolumeCriteria”.
Notice that the top row of the criteria in figure 2 has column headers with the second row having the criteria in a “query by example” layout.
I then verify (check digit) the result of the dsum by using a subtotal function.
=subtotal(9,G:G)Subtotal column G (9 means use sum), This means that the function will only total the visible cells so that when you apply the autofilter the totals change to represent the filtered data.
Leave a Reply