Solved

DSUM with Criteria in 2 different non-contigous cell ranges

Posted on 2011-09-23
5
206 Views
Last Modified: 2012-05-12
I want to use a DSUM where my creiteria are in 2 different cell ranges, is it possible?
0
Comment
Question by:fitaliano
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36590124
I think that might be difficult.....

Can you describe a little more - what data you have and how the criteria will be split....does it need to be DSUM?

regards, barry
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36590363
fitaliano,

Can you explain what you are trying to do?  ANything you can do with DSUM, you can do with other approaches...

Patrick
0
 

Author Comment

by:fitaliano
ID: 36590805
Sure I have data in the following format:

Ref_Period      Region      Board Group      Office      SURVE_GRP      Count
11-03      EMEA      CMS Europe      Frankfurt      EMEA ABS      54
11-03      EMEA      CMS Europe      Frankfurt      EMEA RMBS      28
11-03      EMEA      CMS Europe      London      EMEA ABCP      23
11-03      EMEA      CMS Europe      London      EMEA ABS      99
11-03      EMEA      CMS Europe      London      EMEA CMBS      169
11-03      EMEA      CMS Europe      London      EMEA OPCO      2
11-03      EMEA      CMS Europe      London      EMEA RMBS      677
11-03      EMEA      CMS Europe      Madrid      EMEA ABS      36
11-03      EMEA      CMS Europe      Madrid      EMEA RMBS      229
.
.
.
11-04      EMEA      CMS Europe      Frankfurt      EMEA ABS      54
11-04      EMEA      CMS Europe      Frankfurt      EMEA RMBS      28
11-04      EMEA      CMS Europe      London      EMEA ABCP      23
11-04      EMEA      CMS Europe      London      EMEA ABS      99
11-04      EMEA      CMS Europe      London      EMEA CMBS      169
11-04      EMEA      CMS Europe      London      EMEA OPCO      2
11-04      EMEA      CMS Europe      London      EMEA RMBS      677
11-04      EMEA      CMS Europe      Madrid      EMEA ABS      36
11-04      EMEA      CMS Europe      Madrid      EMEA RMBS      229



The list continues with other Regions/Offices and other Ref_Periods

My Goal is to  build a trend of Counts for each Ref_Period for each Region/Board Group/Office/SURVE_GRP

So my data has to look like

Region      Board Group      Office      SURVE_GRP      11-03  11-04 etc.
EMEA      CMS Europe      Frankfurt      EMEA ABS      54        54
EMEA      CMS Europe      Frankfurt      EMEA RMBS      28        28
etc.

For this I could use a DSUM or DGET but I need to build a lot of criteria... one for each

Office/ SURVE_GRP/Ref_Period Combination

So once I build all the combinations Office/SURVE_GRP I have to repeat them times the Ref_Periods

It would be great If I could reference 2 non contigous ranges I could just build one range for Office/SURVE_GRP combination and one range for Ref_Periods.

If you have other ways to do it I'll be glad to know but please don't suggest pivot tables as they take too much memory, I have huge files to deal with

I hope it is more clear now, thank you for your help


0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36592455
Seems to me to be exactly what PivotTables were made for.  Use these as Row fields:

Region      Board Group      Office      SURVE_GRP      

Use this as the column field:

Ref_Period

Use this as the data field, aggregating by sum.

And if you'd like to learn about tricks to automatically keep a PivotTable up to date:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
Q-27325223.xls
0
 

Author Comment

by:fitaliano
ID: 36593316
Thanks I implemented these tricks already but when my boss tried to use my worksheet it was a nightmare... The file becomes too big, given the amount of data.  He asked me to stay away from pivots.

Any other solution?
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question