Rubayed Razib Suprov
We normally need to evaluate the summation of values by criteria. Which is one of the most commonly used functions in the world. So, to carry out this type of task, DSUM and SUMIF functions are best. But they have some differences, and those usage differences need to be understood. In the following article, we discuss the differences between the DSUM vs SUMIF functions in Excel with appropriate explanations.
Download Practice Workbook
Download the Excel workbook that we’ve used to prepare this article.
Introduction to DSUM Function in Excel
- Objective
Overall, the DSUM function calculates the total sum of a specific Field by matching specific Criteria from a given Range.
- Syntax
=DSUM (database, field, criteria)
- Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| database | Required | Database that contains values. |
| field | Required | Indicates the column to be calculated for sum |
| criteria | Required | Range of cells where specific conditions are assigned |
- What Can be Used as Criteria:
Additionally, DSUM offers multiple criterion types to filter data from the range. Moreover, some most used criterion types are.,
| Criteria | Type | Output |
|---|---|---|
| “Unit Price” | String | Rows match “Unit Price” |
| Cook* | Wildcard | Rows start with “Cook” |
| *ies | Wildcard | Rows end with “ies” |
| 120 | Number | Equal to 120 |
| >120 | Comparison | Greater than 120 |
| <120 | Comparison | Less than 120 |
| >=120 | Comparison | Greater than or equal 120 |
| <>120 | Comparison | Not equal 120 |
| <> | Comparison | Not blank |
| =B7 | Formula | Equal to the argument of B7 |
- Return Parameter
The DSUM function returns a sum value. Moreover, it can return errors if the arguments are not placed properly.
- Version
Microsoft Excel version 2000 to Office 365, Excel version 2011 for Mac and onwards.
Introduction to SUMIF Function in Excel
- Summary
In overall, we can add the values of the cells specified by a given condition or criteria using this SUMIF function.
- Syntax
=SUMIF (range, criteria, [sum_range])
- Arguments
| Argument | Required/Optional | Value |
|---|---|---|
| range | Required | The range of cells that we want to be evaluated by criteria. |
| criteria | Required | The criteria are in the form of a number, expression, cell reference, text, or function that defines which cells will be added. |
| [sum range] | Optional | Moreover, the actual cells to add if we need to combine cells other than those defined in the range argument. |
- Return Parameter
Will return summation values of values based on the criteria.
- Version
The SUMIFS function debuted in Excel 2007 and has been included in each of the versions that have come after it: 2010, 2013, 2016, 2019, 2021, and Excel 365.
Note:
- Firstly, wildcard characters can be included – a question mark (?) to match any single character, and an asterisk (*) to match any sequence of characters. Like 6?”, “apple*”, “*~?”
- Hence question mark (?) will be used for matching any single character.
- As a result, an asterisk (*) will be used to match any sequence of characters. Using this method, we can find out any text or string by matching any substring. For “*Apples” we can find the words like Pineapples or any other words where the last portion is “Apples”.
- Additionally, sum_range should be the same size and shape as the range.
- Moreover, the SUMIF function only supports a single condition.
Differences Between Excel SUMIF and DSUM Functions
| DSUM Function | SUMIF Function |
|---|---|
| You can use multiple criteria in a single column | Using multiple criteria in a single column is not available |
| For DSUM Function, column header always needed | Column header is not needed at all |
| Criteria with their name must be placed in the sheet locally | Presence of the criteria value is enough. |
| Comparatively slower to execute | Faster compared to DSUM function |
2 Suitable Examples of Excel DSUM vs SUMIF Functions
In the following examples, the two main differences between the DSUM and SUMIF functions are demonstrated with illustrations. Moreover, in order to avoid future compatibility issues, try to use the Excel 365 Edition.
1. Calculate Sales for Multiple Criteria in Single Column
Normally, in the case of DSUM, you can apply multiple criteria for a single column. But you can’t do the same for the SUMIF function. This will be shown in the below example.
Steps:
- Firstly, we need to create the outline of our functions example.
- Secondly, we allotted separate cells for the criteria and database.
- After that, the preliminary outline is shown in the below image.
- Firstly, we will see how we can use the SUMIF function with criteria.
- Hence, enter the following formula in cell D21.
=SUMIF($B$5:$B$15,B18,$D$5:$D$15)
- Then press Enter after this.
- After that, repeat the same process and enter the following formula in cell D22.
=SUMIF($B$5:$B$15,B19,$D$5:$D$15)
- Finally, press Enter.
- Therefore, from the above demonstration, we can see that we cannot use multiple criteria in a single column.
- Hence SUMIF we have to use a separate formula for separate criteria.
- So, to get rid of the issue, we need to use the DSUM.
- Then DSUM function can take the whole database as input and then can use multiple criteria.
- After that, we need to convert the dataset to a table.
- Hence select the range of cell B4:D15 and go to Insert > Table.
- Then we will see that there is a window opened Create Table.
- After that, check My table has headers.
- Therefore, click OK.
- After that, clicking OK, you will see that the table is now created.
- Additionally, change the table name from Table Design > Properties > Table Name.
- Next in cell D24, enter = and then select the range of cell B4:D15.
- Then select the Total Sales column header.
- Lastly, select the criteria by selecting the range of cells B17:B19.
- Then press Enter button.
- Therefore, by pressing Enter, you will see the summation value using the DSUM function shown in cell D24.
Read More: How to Use DSUM Function with Multiple Criteria in Excel
2. Requirement of Column Header in Range and Criteria
In the case of the DSUM function, you can not use the dataset without turning the table into a dataset. And subsequently turning every column header into a field. But the SUMIF function does not have this issue. Therefore, go through the steps below to see Excel DSUM vs SUMIF functions.
Steps:
- Firstly, select cell D20 and enter the following formula:
=SUMIF(C5:C15,B18,D5:D15)
- After that, press Enter after this.
- Then in order to achieve the same for the DSUM function, we have to convert the range of cells to a table and then enter the following formula:
=DSUM(Sales[#All],Sales[[#Headers],[Total Sales]],B17:D18)
- Therefore, press Enter after that.
- But if we tried to do this without converting them into tables, and according to the traditional way, we can see some issues.
- Finally, to test this out, enter the following formula into cell D23.
=DSUM(B4:D15,D4:D15,B17:D18)
- After that press Enter.
- After that, pressing Enter, you will notice that there is an error in value warning showing in cell D23.
- Therefore, in the case of the DSUM function, it is imperative that we convert the dataset to a table and then use those fields in our formula.
Read More: How to Use Database Functions in Excel (With Examples)
Conclusion
So, to sum it up, the differences between the DSUM vs SUMIF functions in Excel are shown here with two separate examples. For this problem, a workbook is available to download. Hence, you can practice these methods. Feel free to ask any questions or feedback through the comment section. Finally, any suggestion for the betterment of the Exceldemy community will be highly appreciable.
Related Articles
- How to Use Macaulay Duration Formula in Excel (2 Easy Methods)
- Use DSTDEV Function in Excel (3 Practical Examples)
- How to Use DMIN Function in Excel (4 Suitable Examples)
- Can Excel DGET Return Multiple Records [See 4 Solutions]
- How to Use DOLLAR Function in Excel (5 Suitable Examples)
- Use EDATE Formula for Days (3 Ideal Examples)
- How to Use DCOUNT Function in Excel (5 Suitable Examples)
