Excel DSUM vs SUMIF Functions (2 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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

excel dsum vs sumif


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.

Difference between DSUM vs SUMIF Functions in multiple criterias in single column

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

DSUM function handle multiple criteria in a single cell compared to SUMIF function


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.

Requirement of Column Header in Range and Criteria for DSUM function compared to SUMIF function.

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

Error warning due to lack of Column Header in Range and Criteria for DSUM function compared to SUMIF function.


Download Practice Workbook

Download the Excel workbook that we’ve used to prepare this article.


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.


Related Articles


<< Go Back to Excel DSUM Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo