# DSUM and Excel Tables: Sum With Multiple Criteria

If you need to get a total in Excel, based on criteria, there are a few different ways that you could do it. For example:

• SUMIF – total based on a single criterion
• SUMIFS – total based on multiple criteria
• SUMPRODUCT – total based on multiple criteria
• SUBTOTAL – total based on filtered data
• Pivot Table – summarize filtered data

The SUMIF, SUMIFS and SUMPRODUCT functions can be used in multiple rows, and refer to cells in their current row or column for criteria. Charles Williams recommends using SUMIFS whenever possible, to improve performance.

The SUMIF, SUMIFS and SUMPRODUCT functions can also be used at the top of a worksheet, or on a dashboard, to summarize data in a table, based on selected criteria.

Another method for summarizing results in a dashboard, based on criteria, is to use DSUM, which is one of Excel's database functions. Because DSUM uses a criteria range, it isn't suitable for use in multiple rows, but is a good choice for a single summary, and it can use complex criteria.

We'll see how to use the DSUM function, with Excel's named tables – a feature that was introduced in Excel 2007.

### Set Up the Workbook for DSUM

To use the DSUM function, you'll have to set up the following ranges, similar to what you would use for an Advanced Filter:

• A database range
• A criteria range

In this example, the database contains order information, and is in a table named tblOrders.

On another sheet, I'll set up a criteria range, using headings that exactly match headings in the database. In the cells directly below the headings, I'll type a sales rep's name, and an item that was sold.

The address of the criteria range is E1:F2

### Create the DSUM Formula

Next, I'll create a DSUM formula that calculates the total units sold, by summing the Units column in the database, where the Rep name is Jones, and the Item is Pen.

The arguments for the DSUM function are database, field and criteria.

My database is the table named tblOrders, I want to sum the Units field, and my criteria range is E1:F2

=DSUM(tblOrders,"Units",E1:F2)

Unfortunately, that formula returns a #VALUE! error, because DSUM doesn't recognize the named table. Apparently the database functions weren't updated when named tables were added in Excel 2007.

### Create a Named Range for the Database

The DSUM doesn't recognize the named table, so I'll create a named range – dbOrders – based on the table.

• On the Excel Ribbon, click the Formulas tab, and click Define Name.
• In the New Name dialog box, type a name for the range – dbOrders
• Click in the Refers To box, and on the worksheet, click on the upper left corner of the orders table, to select the entire table. The table name will appear in the Refers To box.
• =tblOrders

• Click on the upper left cell again, to include the heading cells in the selection. The Refers to box will change to:
• =tblOrders[#All]

• Click OK, to complete the name.

### Change the DSUM Formula

Finally, I'll change the DSUM formula, so it uses the named range, dbOrders, instead of the named table.

=DSUM(dbOrders,"Units",E1:F2)

With that change, the DSUM function happily calculates the number of pens that Jones sold.

### Creating Exact Criteria

In the example shown above, the DSUM function treats the text criteria as "begins with", rather than "equal to".

So, the total units sold would include any pen, pen sets, and pencils that were sold by Jones, because those items all begin with "Pen"

If you want to find only the items that are equal to "Pen", change the criterion to:

="=Pen"

With this criterion in cell F2, the total units  is 91, which only includes the Pen sales, not pencils, or pen sets.

For more information and examples for setting up the criteria range, please see criteria range setup section on the Advanced Filter page – it uses the same type of setup.

### Add More Criteria Rows

You're not just limited to one row in the criteria area. For example, you could add more rep names and items, and expand the Criteria Range in the DSUM formula.

In the example shown below, the criteria range is now E1:F4

=DSUM(dbOrders,"Units",E1:F4)

DSUM calculates the total units for orders where:

• Jones is the rep, AND the item name begins with Pen
• OR
• Gill is the rep, AND the item name begins with Binders
• OR
• Gill is the rep, AND the item name begins with Pen

### Use Formulas in Criteria Cells

If you want to use more than a few criteria rows, it can be confusing and cumbersome to create a large criteria range. Instead, you can use formulas in the criteria cells, similar to the criteria formulas that you can use for an Advanced Filter.

If you're using formulas in the criteria range, leave the heading cell blank, or use a heading that is NOT used in the database.

In the example shown below, the criteria headings have been changed to RepCount and ItemCount. At the left are named tables – tblRepSel and tblItemSel – where I have entered the reps and items that I want to include in the DSUM total.

### Enter the Criteria Formulas

In cells E2 and F2, we'll use COUNTIF formulas, to check if:

• the rep name from the database is in the tblRepSel table,
• the item name from the database is in the tblItemSel table.

In the criteria formula, we'll refer to a cell in the first row of data in the database, using a relative reference. We can use a named table reference for the range argument in the COUNTIF function, but must use a normal cell reference for the criteria argument.

The formula in E2 is:

=COUNTIF(tblRepSel[Rep],Orders!D2)

The formula in F2 is:

=COUNTIF(tblItemSel[Item],Orders!E2)

The criteria cells show the results for the first row in the database, and that row has "Jones" and "Pencil".

### Check the DSUM Formula

The DSUM formula is the same as in the previous example:

=DSUM(dbOrders,"Units",E1:F2)

The DSUM result is 377, and we can check that by filtering the database to show the same items.

If we had used a table reference in our COUNTIF formulas, for the Criteria argument, the DSUM would have been incorrect – it adds up all the rows!

To download the sample file, you can go to the Excel Examples page on my Contextures website.

In the Functions section, look for FN0024 – DSUM With Excel Tables.

__________________

### 6 comments to DSUM and Excel Tables: Sum With Multiple Criteria

• David Coop

Thanks for this post on DSUM, Debra.

I don't see how the DSUM formula is giving 299 as the answer for Pen sales by Jones. I get 91, and have confirmed this via filtering the table, and by creating a SUMPRODUCT formula.

I have tried several other combinations, and both formulae give the same answer - it's just the Jones/Pen result of 299 that I don't get.

What am I missing?

Regards,

David

• @David, thanks for asking about that total, and I've added an explanation in the blog post.

When you type text in a criteria cell, it's treated like a "Begins With" filter.

• Montrey

Is this faster than Sumproduct??! Please say it is! Sumproduct tends to lag an excel worksheet when there is a lot of them.

• OmarF

I think you missed ARRAY type formulas which can also be effective for multiple criteria.

I like using DSUM when the criteria get complicated, or if I want to change them at times. The DSUM formula is very simple looking and a criteria table is easier for less experienced people to follow.

However, I've found DSUM to be a challenge when there are quite a few of these formulas in a report. Each requires its own criteria table which can cause layout issues. An example would be a list of 40 sales people with gross sales, profit etc columns. Even tougher when you are including breakouts by product group. I normally use SUMIFS now for this type of scenario.

Whenever I use DSUM, I feel like I'm stepping back in spreadsheet history. Step back to the early 90's. Lotus 123R22 was my first introduction to spreadsheets. The only way back then to get answers based on multiple criteria was using DSUM and DCOUNT etc. Hey, even filtering was limited to what we now call the Advanced Filter.

• Jon

I am with Montrey... sumproduct if faster to implement (if confusing) but cripplingly slow on spreadsheets of any large size.

Have you had any experience comparing the 2 in terms of spreadsheet preformance

• Ross

For the countif formula, if the tbl is substituted by name ranges instead, the result is still 1887 and not 377.

Would you still be able to get the correct result using name ranges in the countif formula?