Five formulas to complete conditional summation in Excel

This article brings you relevant knowledge about excel, which mainly introduces issues related to conditional summation, including the use of SUMIF, SUMIFS, SUMPRODUCT, etc. Let’s take a look I hope it helps everyone.

Related learning recommendations: excel tutorial

Today we will talk about the problem of summing by conditions.

The picture below is the melon-eating record of the majority of melon-eating people in the recent period. The total profit must be calculated based on the designated melon-eating types:

Method 1:

=SUMIF(A3:A7,G3,D3:D7)

The function of SUMIF is based on the specified To sum the condition, the first parameter is the condition area, the second parameter is the specified condition, and the third parameter is the summation area.

If the content in cells A3:A7 is equal to the content of cell G3, sum the cells corresponding to D3:D7.

Method 2:

=SUMIFS(D3:D7,A3:A7,G3)

##SUMIFS The function is to sum based on one or more specified conditions.

The first parameter is the summation area, and the following parameters are pairs of two, which are the corresponding summation area and summation conditions. When all condition ranges meet the specified conditions, the corresponding cells in the summing range are summed.

Method 3:

=SUMPRODUCT((A3:A7=G3)*D3:D7)

## The function of #SUMPRODUCT is to multiply two sets of numbers correspondingly, and then calculate the sum of the products.

In this example, first use (A3:A7=G3) to determine whether column A is equal to the specified condition, and obtain a memory array composed of a set of logical values ​​TRUE or FALSE.

Then use this memory array to multiply the corresponding values ​​​​in D3:D7. If an element in the memory array is TRUE, it will still be the original value of column D after multiplying with column D. Otherwise, After multiplication, it is 0. Finally, use SUMPRODUCT to sum the results of each calculation.

Method 4:

Hold down Shift Ctrl, then press Enter and enter the following array formula:

=SUM(IF(A3:A7= G3,D3:D7))

Be careful not to enter the outermost curly brackets when entering.

In the IF (A3:A7=G3,D3:D7) part of the formula, first use the IF function to compare the contents of column A with G3 to obtain a memory array result. If they are the same, return the value corresponding to column D, otherwise return the logical value FALSE.

Finally use the SUM function, ignoring the logical values ​​in the memory array for summation.

Method 5:

=DSUM(A2:E7,H2,G2:G3)

DSUM The function is used to return the sum of the numbers in a column of the database that meet the specified conditions.

The first parameter is the cell range that constitutes the database.

The second parameter is used to specify which column of data is to be returned. It can be the same column title as in the database area, or it can be represented by a number.

The third parameter is a set of cell ranges containing the given conditions. It needs to contain a column header identical to the one in the database range and the cells used to set the condition.

In this example, the first parameter is A2:E7.

The condition field name specified by the second parameter is "profit" in cell H2, or written as 4.

The third parameter is the G2:G3 cell range, where the field title of G2 is the same as the title in the database, and the specified condition is "Fan Fan Gua" in the G3 cell.

Related learning recommendations:

excel tutorial

The above is the detailed content of Five formulas to complete conditional summation in Excel. For more information, please follow other related articles on the PHP Chinese website!