DSUM with multiple criteria

MattyH

New Member
Joined
Dec 8, 2005
Messages
3
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. :)
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
just yesterday I responded to a question very much like this one. Do a search for SUMPRODUCT.
 

MattyH

New Member
Joined
Dec 8, 2005
Messages
3
right you are. Thanks a million

One more thing - Am I right in thinking that if I replace all my array formulas with these sumproducts, then the spreadsheet will work a lot faster? Will dynamic ranges slow it down much

The database only has around 400 lines, but 30 columns

Thanks again
Matt
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
MattyH said:
right you are. Thanks a million

Wrong he is... :LOL: for:

One more thing - Am I right in thinking that if I replace all my array formulas with these sumproducts, then the spreadsheet will work a lot faster?

the answer is no.

Will dynamic ranges slow it down much

Enough...

The database only has around 400 lines, but 30 columns...

Consider using the pivot table approach.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,638
Members
412,334
Latest member
ExcelForLifeDontHate
Top