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.
DSUM in Excel (Table of Contents)
What is DSUM Function in Excel?
How often you use database function in excel. I do not think you would have used this very often in your workplace. When you work with tabular data sets, database functions are extremely handy.
There are several complex excel Array Formulas, SUMPRODUCT, INDEX MATCH function can do the job for you. However, they are very complicated in nature. Database functions are easy and powerful in nature.
DSUM Formula in Excel
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?
DSUM: D stands for Database. 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 sales person in the region west for the product Desk. It is a bit of SUMIFS kind of function. Based on the certain criteria’s 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 table etc… As I mentioned earlier in the article DSUM is a database function based on the criteria’s 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 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’s 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’s: After setting up the table, we need to create our criteria’s. Our first criteria will be like the below one.
Q1 – Our First Question is to calculate the revenue for the region Central.
Apply 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. Revenue column in the table.
Part 1: It is taking the range of database. 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.
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.
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.
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.
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’s to get the total.
Design the criteria as shown in the below table and apply DSUM function
You can download this DSUM Function Excel template here – DSUM Function Excel Template
Things to Know about DSUM Function in Excel?
- 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’s.
- 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 region or different rep totals create a drop-down list to your criteria’s. 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 to SUMIF & SUMIFS functions.
This has been a guide to DSUM in Excel. Here we discuss the DSUM Formula in excel and how to use DSUM function in Excel along with excel example and downloadable excel templates. You may learn more about excel functions from the following articles –