Excel Formula (sumif, sumproduct)

Source: Internet
Author: User
Tags sumif formula

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 ~

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.