Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DSUM with Criteria in 2 different non-contigous cell ranges

Posted on 2011-09-23
5
Medium Priority
?
212 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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

618 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