What is DSUM in Excel?
DSUM in excel is also known as DATABASE Sum function in excel which is used to calculate the sum of the given data base based on a certain field and a given criteria, this function takes three arguments as inputs and they are the range for database an argument for field and a condition and then it calculates the sum for it.
Below is the DSUM Formula in Excel
- Database: It is simply the data table along with headers.
- Field: The column you would like to sum in the data table. i.e., Column Header.
- Criteria: List of criteria’s in the cells containing the conditions specified by the user.
How to Use the DSUM Function in Excel?
The below image shows the sale of stationery items, and we have some requirements to calculate. We have 5 different situations to calculate the revenue. Each situation is not a separate situation but rather conditions that we need to implement.
DSUM is criteria based function, which can return the value based on the criteria you give. It will give you the Sum of the column based on multiple requirements. For example, Total Sales for the salesperson in the region west for the product Desk. It is a bit of a SUMIFS kind of function. Based on certain criteria, it will return the value.
How DSUM Works?
The word Database is not a stranger to excel user. In Excel for the database, we use the word range of cells or cells or tables, etc. As I mentioned earlier in the article, DSUM is a database function based on the criteria we express in a range of cells following the same structure of the database or table. We can give criteria to each column by mentioning it as a header and the given criteria under it.
If you already know how SUMIF & SUMIFS works, then DSUM should not be a complex thing to understand for you.
Let us the go-ahead to get an idea about the functionality of the DSUM function.
Practical DSUM Examples
Look at the below image where we have sales data from A1 to G38. Answer all the questions from the below table.
Set up the Data: Since we already have our criteria, we need to first set up the data table. Select the data and make it a table format. Click the Ctrl + T and select the data >
And name the table as Sales_Data.
Create Your Criteria: After setting up the table, we need to create our criteria. Our first criteria will be like the below one.
Q1 – Our First Question is to calculate the revenue for the region Central.
Apply the DSUM formula to get the total of the region Central.
The output is 11,139.
Part 3: In order to sum, we specify the criteria as the Central region.
Part 2: It specifies which column you need to sum, i.e., the Revenue column in the table.
Part 1: It is taking the range of databases. We name our database as Sales_Data.
Note: For the criteria column, all the characters should be the same as in the data table.
Q-2: Calculate total revenue for the region East for the item PEN.
Now, we need to calculate the total revenue for the region East but only for Pen in the item column.
The output is 4,501.
Q-3: Sum of sales for all the Rep other than Gill.
Now, we need to calculate the sum for all rep except the rep Gill. For this, we need to give criteria under rep as <>Gill.
“<>” means not equal to.
The output is 15,391.
Q-4: Sum of sales for units greater than 25.
Now, the equation is to calculate the total revenue for all the units, which are greater than 25 units are.
For this, set the criteria as >25.
The output is 15,609.
Q-5: Sum of sales from 18th Oct 2014 to 17th Oct 2015
Now, we need to get the total revenue from 18th Oct 2014 to 17th Oct 2015. In order to do this, we need to set two criteria for one column.
The output is 8646.
Q-6: Sum of sales for Rep Smith, for item Binder, for Region Central
Now, we have to match 3 different criteria to get the total.
Design the criteria as shown in the below table and apply the DSUM function
Things to Remember
- If you are writing a field name, it has to be in double quotes and should be as same as in the table header.
- First, identify the criteria requirement and make a list of all the criteria.
- Create a table for your data. If the data size increases, it will be a dynamic range and need not worry about the range.
- The #Value error occurs due to wrong field names included in the database argument.
- If you do not give any specific criteria, it will just give you the overall sum for the column.
- In order to get different regions or different rep totals, create a drop-down list to your criteria. As soon as you change the drop-down list, it will show the results accordingly.
- The criteria table is not case-sensitive.
- It is an alternative formula for SUMIF & SUMIFS functions.
This has been a guide to DSUM in Excel. Here we discuss the DSUM Formula in excel and how to use the DSUM function in Excel along with excel example and downloadable excel templates. You may learn more about excel functions from the following articles –