Hi All
Here's a puzzler for you:
I have a big old database, and from it I want to pull the total sales (sum of sales) for all records that meet certain criteria (3 criteria: salesperson, month and region).
The problem is that I want to produce a set of final tables, one for each region, showing salesperson down the left hand side and month along the top, with the total sales for each salesperson/month shown each of the cells of the table
Now, being a reasonably bright chappy, I have managed to do this with an array formula that uses a triple embedded IF in a SUM formula to pull out the relevant records from the database. However, it takes aaaaaaaaages to calculate, so I need to get cunning.
I am thinking it should be possible to do this with DSUM, which should be a lot faster, but at the moment, I can't work out how to enter the multiple criteria. I know it can be done by entering a range containing a column header and criteria in the CRITERIA part of the DSUM formula, but I can't make individual ranges for all my combinations of salesperson/month/region, as that would be a big old ugly table.
So my question is really this: How can I enter multiple criteria into a DSUM formula without having to refer to a range that indicates my criteria.
Oh, and avoid arrays that slow the old girl down.
Many thanks, be brave and good luck
Best
Matt
Using excel xp in xp.
Here's a puzzler for you:
I have a big old database, and from it I want to pull the total sales (sum of sales) for all records that meet certain criteria (3 criteria: salesperson, month and region).
The problem is that I want to produce a set of final tables, one for each region, showing salesperson down the left hand side and month along the top, with the total sales for each salesperson/month shown each of the cells of the table
Now, being a reasonably bright chappy, I have managed to do this with an array formula that uses a triple embedded IF in a SUM formula to pull out the relevant records from the database. However, it takes aaaaaaaaages to calculate, so I need to get cunning.
I am thinking it should be possible to do this with DSUM, which should be a lot faster, but at the moment, I can't work out how to enter the multiple criteria. I know it can be done by entering a range containing a column header and criteria in the CRITERIA part of the DSUM formula, but I can't make individual ranges for all my combinations of salesperson/month/region, as that would be a big old ugly table.
So my question is really this: How can I enter multiple criteria into a DSUM formula without having to refer to a range that indicates my criteria.
Oh, and avoid arrays that slow the old girl down.
Many thanks, be brave and good luck
Best
Matt
Using excel xp in xp.
