Can DSUM filter values be combined into a single range?

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
I've got the following table that I'm using to perform DSUM and DCOUNT calcs:
Book1
CDEFGHIJKL
4RegionCurrentRegionSelected:
5-CentralDivision--CentralDivision-
6GreatLakes
7MidCentralExample1--Wanttoperformcalcsonallregionswithinselecteddivision
8MidSouthRegionNameTransactionTypeDateApprovedDateApprovedDCOUNTDSUMDSUMShares
9NorthCentral-CentralDivision-Sale>=1/1/2005<=12/31/20050$-0
10Southwest
Sheet2


Can I set my sheet up so that when "-Central Division-" is selected by the user, the DSUM actually calculates the underlying regions in the Central Division? I basically want to add the market values for Great Lakes, Mid Central, Mid South, etc. but want to keep my sheet so the user (and underlying table) only look at one row when filtering. I'd prefer not to have to list each of the 5 regions to perform this calc.

THanks,
Mike
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Mike, when you use the term, "filtering", are you referring to Excel's AutoFilter functionality? If so, are you intending to use an Advanced AutoFilter? And, what range are you filtering? What is the Database range?
 
Upvote 0
Sorry... not using an advanced filted.

I have one worksheet with my data -- range is called 'Database' and it is Columns A through O, rows 1 through x with a header row.

Three of the database columns are listed in the box shown in my code... 'Region Name', 'Transaction Type', and "Date Approved'. The user has combo boxes where they select the desired region (combo box is linked to the Region list in column C of my code). They can also choose the quarter and year for the reporting period. These selections are tied to code that translates the selection into the appropriate calendar dates, i.e. Selecting '1st Quarter' and '2006' from the combo boxes results in 1/1/06 and 3/31/06 being set as the quarter start and end dates.

Columns E through L in my code show how I'm restricting the data for purposes of the database calculations. The user-selected region is populated in E9, and quarter start and end dates are placed in G9 and H9, effectively telling the DSUM/DCOUNT calcs to only include rows of data with that specified region and where the date falls within that range (for all transaction types that are "Sale").

However, I'd also like to run this same calc at a divisional level. As you can see from my code, the Central Division includes 5 regions. I'd like to set the sheet so when the user selects '-Central Region-' from the combo box, the DSUM and DCOUNT calculations done on row 9 actually perform the calcs on all 5 regions in the selected division. What I'm trying to avoid is creating new database calculations and multiple IF formulas to switch between the table for regional level stats and divisional level stats. That may be the only way to do it though...looking for some guidance.

Thanks!
Mike
 
Upvote 0
Mike, is there a reason why you've chose not to employ PivotTables in your application?

Have you considered creating a separate Criteria ranges for each of the 5 regions, and summing the individual Dfunctions associated with each of these "new" Criteria ranges to get the overall Divisional total?
 
Upvote 0
Mark,

Just added some pivots, and slowly learning how to use them effectively. Can you explain yourself in a bit more detail though... I don't completely follow.

Thanks,
Mike
 
Upvote 0
Mike, PivotTables allow you to summarize a data list using summary functions (eg., Count, Sum). You could put your Region/Division fields in the ROW or COLUMN positions, and the use the GETPIVOTDATA Excel function to extract the PivotTable results and position them anywhere in your workbook. Unlike an Excel Dfunction, a PivotTable will need to be refreshed (manually, or programmatically using VBA) when your data changes.

Take a look at these online tutorials on PivotTables...

http://office.microsoft.com/en-us/excel/results.aspx?qu=pivottables&av=ZXL110

http://office.microsoft.com/en-us/excel/results.aspx?qu=getpivotdata&av=ZXL110
 
Upvote 0
Interesting... I know pivot tables, but was unfamiliar with the GETPIVOTDATA function. I think this may be a more streamlined way to extract the data that is currently handled by the Dfunctions.

Mark- Would you be willing to take a look at the report I've been working on and offer your thoughts? It has macros in it, so I don't know if you're concerned with security at all...

Thanks!



Mike, PivotTables allow you to summarize a data list using summary functions (eg., Count, Sum). You could put your Region/Division fields in the ROW or COLUMN positions, and the use the GETPIVOTDATA Excel function to extract the PivotTable results and position them anywhere in your workbook. Unlike an Excel Dfunction, a PivotTable will need to be refreshed (manually, or programmatically using VBA) when your data changes.

Take a look at these online tutorials on PivotTables...

http://office.microsoft.com/en-us/excel/results.aspx?qu=pivottables&av=ZXL110

http://office.microsoft.com/en-us/excel/results.aspx?qu=getpivotdata&av=ZXL110
 
Upvote 0
Re-opening this as I still haven't found a solution and need it in a pinch. Please see below...

Essentially, I have 5 region names that are all part of one division. My report allows me o extract data using DSUM for each region. I want to use the same design to extract data when a division is selected, but the division should really just be a total of the 5 regions it encompasses. I want to be able to do this with my existing DSUM table, and not have to have one table for each region, following by SUM functions.

Thoughts?

I've got the following table that I'm using to perform DSUM and DCOUNT calcs:
Book1
CDEFGHIJKL
4Region*Current*Region*Selected:*******
5-Central*Division-*-Central*Division-*******
6Great*Lakes*********
7Mid*Central*Example*1*--*Want*to*perform*calcs*on*all*regions*within*selected*division
8Mid*South*Region*NameTransaction*TypeDate*ApprovedDate*Approved*DCOUNTDSUMDSUM*Shares
9North*Central*-Central*Division-Sale>=1/1/2005<=12/31/2005*0*$************-***0
10Southwest*********
Sheet2


Can I set my sheet up so that when "-Central Division-" is selected by the user, the DSUM actually calculates the underlying regions in the Central Division? I basically want to add the market values for Great Lakes, Mid Central, Mid South, etc. but want to keep my sheet so the user (and underlying table) only look at one row when filtering. I'd prefer not to have to list each of the 5 regions to perform this calc.

THanks,
Mike
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top