Solved

DSUM with Criteria in 2 different non-contigous cell ranges

Posted on 2011-09-23
5
204 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now