MrExcel Publishing
Your One Stop for Excel Tips & Solutions

DSUM only allows one criteria to be created?

Posted by Anne Huang on November 22, 2001 7:00 AM

I've been trying to figure out the DSUM function. From what I understand, you need to type in the criteria in table format in your worksheet with the column heading and the criteria in the cell underneath.
Where A1 would be column heading (i.e. Trees) and A2 would be criteria (i.e. Apples) and in the DSUM function A1:A2 would be the criteria cell range. However, I would like to be able to "list" all my criteria under A1 (Trees), for example A2=Pears, A3=Cherries etc.
How do I account for this when defining my criteria cell range? The reason I need to do this is because I'm trying to sum numbers related to certain cost centres in the organization and there are over 95 cost centres to take into account.
Any ideas?

Posted by M Walker on November 22, 2001 10:49 AM

use this: dsum($a$1:$b$1000,"Cost",criteriapage A1:C2)
where "cost" is the the title of the column you want to sum. $a1$:$b$1000 is the entire table where your data resides.

example of criteria:
date Location Client
01/10 Toronto Sun Life
date Location Client
02/10 Toronto Sun Life

for the criteria type in the first 2 rows and then for the following rows, either fill down or
set the lower rows = to the rows above ie =A1+1 or =B1
If you wanted the location to change (assuming you your report is columns not rows, you would place another set of criteria in the next 3 columns, changing the location or client to whatever you wish.

Then in the report, you fill your dsum formula down (keep in mind that you can only use every second row because the criteria has 2 rows for each day. Or you can fill across (keep in mind that every 2nd or 3rd column will not work because the criteria could take up more than one column)

Remember to SAVE. Dsums and criteria pages tend to cause EXCEL to crash.

Keep in mind that in my example, the report looks for info for different dates while yours would look for different cost centre names.

There is also an easier way to do this without critiera:
you must press ctl shift enter after entering this formula to make it work
this is called an array formula
The drawback to the array formula is that the worksheet takes longer to calculate the file size is larger.
I hope my explanation was clear!

where the b column may contain the word north and the c column may contain the word east and the a column contains the numbers you wish to sum