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. :)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,051
Messages
5,835,137
Members
430,343
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top