Excel DSUM

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.

Syntax

Below is the DSUM Formula in Excel

DSUM Formula
  • 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 Function - Table

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 functionSUMIFS Kind Of FunctionThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range).read more. 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

You can download this DSUM Function Excel template here – DSUM Function Excel template

Look at the below image where we have sales data from A1 to G38. Answer all the questions from the below table.

DSUM Example 1

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 >

create table - ctrl+T - select table

And name the table as Sales_Data.

design - 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.
question #1

Apply the DSUM formula to get the total of the region Central.

DSUM Example 1-4

The output is 11,139.

output

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.

DSUM Example 2

The output is 4,501.

DSUM Example 2-1
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.

DSUM Example 3

The output is 15,391.

Output
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.

set the criteria as >25

The output is 15,609.

output
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.

DSUM Example 5

The output is 8646.

output
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.

match 3 different criteria

Design the criteria as shown in the below table and apply the DSUM function

Example 6-1
DSUM function

Things to Remember

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>