fitaliano
asked on
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?
fitaliano,
Can you explain what you are trying to do? ANything you can do with DSUM, you can do with other approaches...
Patrick
Can you explain what you are trying to do? ANything you can do with DSUM, you can do with other approaches...
Patrick
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Any other solution?
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