The Key Differences Between SUMIFS and DSUM in Google Sheets

What sets SUMIFS and DSUM apart in Google Sheets? Let’s dive into the details and explore these conditional sum functions.

Typically, I create formulas specifically for Google Sheets users, but many of them are also applicable to Excel users. The “key differences” discussed in this tutorial are applicable to Excel as well.

In this article, we’ll examine the difference between SUMIFS and DSUM in Google Sheets and illustrate it with a simple example. But first, let’s address why we’re skipping SUMIF, another similar function.

Why Skip SUMIF?

The reason is simple. While SUMIFS and DSUM can handle multiple criteria columns, SUMIF is designed for a single criteria column. If you’re interested in including multiple conditions in the SUMIF function, check out my unique tips in my article Multiple Criteria Sumif Formula in Google Sheets.

SUMIFS and DSUM Key Differences in Google Sheets

Before we dive into the example, let’s explore the main distinctions between SUMIFS and DSUM:

1. Application:

   • DSUM requires table-like structured data and identifies the criteria range using field labels. It can use field labels or column indexes for the sum range.
   • SUMIFS uses range references and requires individual column ranges in the formula. This allows scattered criteria columns to be included if they have the same number of rows.

2. Specifying Criteria:

   • DSUM requires a table with a header row containing field labels and criteria listed below. Specifying hardcoded criteria can be complex and requires knowledge of creating arrays.
   • SUMIFS allows you to specify criteria using cell references or hardcoded values within the formula.

3. Readability:

   • DSUM formulas with multiple criteria are more reader-friendly due to the clarity achieved by specifying criteria as a table.

4. Advanced Capability:

   • Advanced users used database functions for row-wise array formulas before the introduction of Lambda. This is not possible with SUMIFS.

Now that we’ve outlined the key differences, let’s move on to an example that will help you understand the distinction between SUMIFS and DSUM.

Formulas to Illustrate the Difference Between SUMIFS and DSUM

In this example, we’ll sum the “Sales Value” for “Philip Nida” based on specific conditions.

Sample Data and Criteria:
Below, we have structured data in the range A6:D14. The header row A6:D6 contains field labels: Name of Sales Person, Area, Sales Date, and Sales Value, respectively.

We’ll sum the “Sales Value” in column D for “Philip Nida” in column A based on the following conditions:

• Area: North or South (column B)
• Sales Date: Between 01/07/2017 to 31/07/2017 (column C)

First, let’s observe the DSUM criteria and formula:

Criteria Usage in DSUM:
Refer to the criteria table in cell range A2:D4 in the screenshot below.

Note: The entered criteria won’t appear exactly as stated in the range C3:D4. For example, after entering the criteria =”>=”&DATE(2017,7,1) in cell C3, it will be converted to >=42917. This is natural and you can read more about using date conditions in DSUM in my article How to Use Date Difference As Criteria in DSUM in Google Sheets.

To calculate the sum using DSUM, use the following formula:
=DSUM(A6:D14, 4, A2:D4)

• database: A6:D14 (structured table)
• field: 4 (sum range)
• criteria: A2:D4 (criteria entered as a structured table)

Now let’s move on to the SUMIFS formula:

To calculate the sum using SUMIFS, use the following formula:
=SUMIFS(D7:D14, A7:A14, A3, B7:B14, B3, C7:C14, C3, C7:C14, D3) + SUMIFS(D7:D14, A7:A14, A3, B7:B14, B4, C7:C14, C3, C7:C14, D3)

• sum_range: D7:D14
• criteria_range1: A7:A14
• criterion1: A3
• criteria_range2: B7:B14
• criterion2: B3 (in the first formula) / B4 (in the second formula)
• criteria_range3: C7:C14
• criterion3: C3
• criteria_range4: C7:C14
• criterion4: D3

Compared to DSUM, the SUMIFS formula can be lengthy and confusing, especially when multiple criteria need to be specified within the same column. In this example, we nested two formulas to evaluate “North” and “South” in the area column. Alternatively, you can use the following formula:
=ArrayFormula(SUMIFS(D7:D14, (B7:B14=B3)+(B7:B14=B4), 1, A7:A14, A3, C7:C14, C3, C7:C14, D3))

(B7:B14=B3)+(B7:B14=B4) tests whether the value in B7:B14 is either “North” or “South” and returns 1 or 0. So we have specified the criterion as 1. You can also use REGEXMATCH in this case.

Both DSUM and SUMIFS can produce the same result. The choice between the two depends on your preference.

That’s all about the differences between the SUMIFS and DSUM functions in Google Sheets. For more function guides and tips, make sure to visit Crawlan.com. Happy sheeting!