DSUM Function in Excel: Explained
In this article, you will learn how to use the DSUM formula in Excel.
What is the DSUM function in Excel?
The DSUM function in Excel is a function that sums selected database entries based on the criteria(s) specified. DSUM stands for "Database Sum," and it's advantageous when you're dealing with large databases in Excel.
When is the DSUM function in Excel beneficial?
The DSUM function in Excel is particularly beneficial when dealing with large sets of data, databases, or tables where you need to perform a sum operation based on specific conditions. A few examples include:
- Filtering Data: When you have a huge dataset and you're interested in summarizing data that meets certain criteria. For example, if you have a sales dataset and want to find the total sales for a specific region or product, you can use DSUM to get this information.
- Multiple Criteria: The DSUM function can evaluate multiple criteria across multiple columns, unlike other functions which are limited to one column.
Understanding the syntax of the DSUM function in Excel
The syntax for the DSUM function in Excel is the following:
Database: This is a required argument that is the range of cells that makes up the database. The database should be a full table, including headers.
Field: This is a required argument that is the column in the database that the function will be used on. This can be either the column label in quotation marks or the column number (of the data set, not the sheet) without quotation marks (the first column being 1, the second column being 2, and so on).
Criteria: This is a required argument that is the range of cells that contains the conditions you specify. The criteria range should include at least one column label (that must match a database column header) and at least one cell below the column labels for specifying the condition.
How to use the DSUM function in Excel
Let's say you have a table of sales data with columns for "Region", "Product", "Salesperson", and "Sales" and you want to know the total sales made by specified conditions, for this you could use the DSUM function. For instance, suppose you wanted to find out how many sales were made in just the North region or how many sales were made by the salesperson James for product B. Below is a screenshot of using the DSUM function for those examples.
As seen by the annotations in the screenshot above, the database argument is an entire array including headers. Like mentioned earlier, the field argument can be input as the column header in quotation marks or as the column number. In the example above we want to find the sum of the amount of sales and so we want the sales column to be our field argument. We can input this argument by either writing “Sales” into the function or noticing that the Sales column is the 4th column in the database and could instead write “4”; these inputs are interchangeable and will return the same output. Both types of inputs are seen in the example above. Then, the criteria argument is an array separate from the database that specifies the condition you want applied to your sum calculation such as the second formula which takes two conditions so we have a small table with the headers we want (Product and Salesperson) and specific values beneath them (B beneath Product and James beneath Salesperson).
What's the difference between Excel’s DSUM, SUMIF, and SUMIFS functions?
Excel's DSUM, SUMIF, and SUMIFS functions are all used for summing data based on certain criteria, but they are used in different contexts and have different capabilities.
- The DSUM function is a database function, and it's used when dealing with large databases or tables in Excel. The DSUM function allows you to specify multiple criteria across multiple columns. It is especially useful when dealing with large databases where you want to summarize data that meets certain criteria across different fields.
- The SUMIF function is used to sum values in a range that meets the criteria that you specify. It's a simple conditional summing function that allows for one criterion and one range to sum.
- The SUMIFS function is an extension of SUMIF and is used when you have multiple conditions to meet. It sums the values in a range that meets multiple criteria that you specify. Unlike the DSUM function, the SUMIFS function is better suited for less complex and non-dynamic sets.
So, depending on the specific requirements of your data analysis task, you might find one function to be more suitable than the other.
Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.
