Solved

DSUM with Criteria in 2 different non-contigous cell ranges

Posted on 2011-09-23
5
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

688 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