In cells E2 and F2, we’ll use COUNTIF formulas, to check if: At the right are named tables – tblRepSel and tblItemSel – where I have entered the reps and items that I want to include in the DSUM total. In the example shown below, the criteria headings have been changed to RepCount and ItemCount. 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. Instead, you can use formulas in the criteria cells, similar to the criteria formulas that you can use for an Advanced Filter.
If you want to use more than a few criteria rows, it can be confusing and cumbersome to create a large criteria range.
In the example shown below, the criteria range is now E1:F4ĭSUM calculates the total units for orders where: For example, you could add more rep names and items, and expand the Criteria Range in the DSUM formula. You’re not just limited to one row in the criteria area. With this criterion in cell F2, the total units is 91, which only includes the Pen sales, not pencils, or pen sets.įor 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. If you want to find only the items that are equal to “Pen”, change the criterion 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” In the example shown above, the DSUM function treats the text criteria as “begins with”, rather than “equal to”. With that change, the DSUM function happily calculates the number of pens that Jones sold. The Refers to box will change to:įinally, I’ll change the DSUM formula, so it uses the named range, dbOrders, instead of the named table.
On another sheet, I’ll set up a criteria range, using headings that exactly match headings in the database. In this example, the database contains order information, and is in a table named tblOrders. To use the DSUM function, you’ll have to set up the following ranges, similar to what you would use for an Advanced Filter:
#Excel how many rows in a table how to#
We’ll see how to use the DSUM function, with Excel’s named tables – a feature that was introduced in Excel 2007. 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. Using DSUMĪnother method for summarizing results in a dashboard, based on criteria, is to use DSUM, which is one of Excel’s database functions. 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. Charles Williams recommends using SUMIFS whenever possible, to improve performance. The SUMIF, SUMIFS and SUMPRODUCT functions can be used in multiple rows, and refer to cells in their current row or column for criteria. SUBTOTAL – total based on filtered data.SUMPRODUCT – total based on multiple criteria.SUMIFS – total based on multiple criteria.SUMIF – total based on a single criterion.Today, we’ll take a look at how DSUM and Excel Tables sum with multiple criteria.įirst, here are other ways that you can sum with single or 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.