Excel function learning: the simplest conditional summation function DSUM()

It is called the simplest conditional summation function! Because it has been anonymous in the world for many years, it is not known to others. Although it is not as famous as the SUMIFS function, its unique multi-condition summation method still makes it invincible. The formula of SUMIFS is like a long train, and its formula is like a short taxi. Now it's time to reveal its mystery, it is - the DSUM function!

The SUM series summation functions are the most commonly used functions in our daily work. I believe that most friends are already familiar with functions such as SUMIF, SUMIFS, and SUMPRODUCT.

But there is a summation function that everyone may not be familiar with. It is the DSUM function, which is used to find the sum of the field (column) data recorded in the database that meets the given conditions.

The syntax is: =DSUM (data area, number of columns to be summed, condition area)

Syntax description:

• Data area: In addition to selecting a single value, you can also select multiple cells for multi-condition search.

• Number of columns: The number of columns where the data to be summed is located (can also be expressed by column headers)

• Conditional area: consists of the header row and Multi-row area composed of conditional cells

In fact, its function is relatively close to SUMIF and SUMIFS, so which one is better to use, DSUM or SUMIF and SUMIFS? Let’s compare it below!

The following is a table of product sales in each region of a company. Now we need to sum up the sales based on different conditions.

1. Single condition summation

For example, if you need to count the North China area of the entire sales volume.

1) Use the DSUM function

Function formula: =DSUM(A1:C13,3,E1:E2).

• A1:C13: Specify the data area.

• 3: Specify the data column to be summed, here it refers to column C. (In addition to specifying the column number, the data column can also refer to the cell where the column title is located or specify the column title with quotation marks at both ends)

• E1:E2: Specify Condition area, in this example the condition is the "North China" area. (Note: The third parameter must include a column title and the cell below the column title for setting conditions)

The meaning of the function formula is actually to summarize the areas in column A that are North China The data in column C corresponding to the cell.

2) Use the SUMIF function

=SUMIF(A2:A13,E2,C2:C13 )

By comparison, we can see that in single condition summation, these two functions are not very convenient in terms of convenience. Big difference.

2. Multi-condition summation

For example, we need to count the sales volume of A002 product in North China.

1) Use the DSUM function

Function formula: =DSUM(A1:C13,3,E1:F2)

We see that the DSUM function formulas of multi-condition summation and single-condition summation are very similar, except that the condition area is adjusted from E1:E2 to E1:F2.

E1:F2 represents the condition area, that is, North China and A002 are used as the conditions for the sum of sales.

2) Use the SUMIFS function

Function formula: =SUMIFS(C2:C13 ,A2:A13,E2,B2:B13,F2)

By comparing the summation of multiple conditions, we found that DSUM The function structure and usage are simpler than the SUMIFS function. DSUM is a good choice for newcomers who do not have a good function foundation!

However, the DSUM function has a disadvantage compared to the SUMIFS function, that is, the summation conditions cannot be entered manually.

For example, for SUMIFS multi-condition summation, we can write the function =SUMIFS(C2:C13,A2:A13,"North China",B2:B13,"A002"), without the need for two EF columns as conditional auxiliary columns , the summation can be completed by directly entering the conditions manually. The conditional area of ​​the DSUM function requires that the column header and the cells below the column header be used to set the condition, so you need to use auxiliary columns to complete the sum.

To sum by condition, do you prefer to use the SUMIF function or the DSUM function? Welcome to leave a message for discussion.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel function learning: the simplest conditional summation function DSUM(). For more information, please follow other related articles on the PHP Chinese website!