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
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.
- Click on the upper left cell again, to include the heading cells in the selection. The Refers to box will change to:
- 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.
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:
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 calculates the total units for orders where:
- Jones is the rep, AND the item name begins with Pen
- Gill is the rep, AND the item name begins with Binders
- 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:
The formula in F2 is:
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:
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!
Download the DSUM Sample File
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.