Excel Formula (sumif, sumproduct)

To put it bluntly, first look at the basic data:

There are first "departments" groups in the data, and then "level" groups. To demonstrate the formula, there are three fixed subsidy forms in the data: 0 and 50,100. We need to aggregate some groups based on the data.

1. For a single condition set, we can directly use the sumif formula for a simple single condition.
Formula description:Sumif (matching range, condition, total range)
Let's take a look at how to calculate the salaries of the three departments. OK, prepare three cells first, and write the formula
= Sumif (A $2: a $15, G2, d $2: d $15) column A is a condition matching column, column D is a total column,"$"It indicates that the position is not automatically changed when the formula is copied at a fixed position. After writing the first formula, you can copy the formula directly to other cells below. Otherwise, when you copy the formula, the range in the formula is automatically added to 1.

The result is as follows:

2. Composite condition set. For a set with more than one condition, sumif cannot be used.
Formula description:Sumproduct (array 1, array 2, array 3 ,...) It is used to multiply multiple arrays and then aggregate them. The non-numeric data in the array is calculated as 0.
To understand the sumproduct formula, Let's first look at a simple application:

The result is = 30x3 + 40x2 + 50x1 = 220.

Then, we can use an expression like array 1 = xxx (xxx can be the specified data or cell) to filter a certain condition and analyze sumproduct (A1: a10 = A) * (B1: B10) is equivalent
(A1 = A) * B1 + (A2 = A) * B2 +... + (A10 = A) * B10 statement. If the condition matches, 1 is returned and 0 is returned. Therefore, the matching will be added, and the non-matching will be equivalent to adding 0.

Next, we can further find the sum of different levels in each department ":
= Sumproduct (A $2: a $15 = A20) * (C $2: C $15 = B20) * d $2: d $15)
The explanation is that the department column meets the cell A20 content and the level column meets the cell B20 content, and the total salary is calculated.

As shown in, the total salary of Class A in the Administration Department is obtained. (Yellow part)

3. In addition to the sum function, sumproduct can also be used for count.
How can we calculate the number of subsidies = 100 in each department?
= Sumproduct (A $2: a $15 = A18) * (E $2: E $15 = B18 ))
This formula is interpreted as adding 1 for matching, and adding 0 for non-matching.

Thanks for watching. This introduction is complete ~