DSUM with Criteria in 2 different non-contigous cell ranges

I want to use a DSUM where my creiteria are in 2 different cell ranges, is it possible?
fitalianoAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
barry houdiniCommented:
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
 
Patrick MatthewsCommented:
fitaliano,

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

Patrick
0
 
fitalianoAuthor Commented:
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
 
fitalianoAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.