Shulou(Shulou.com)11/24 Report--

The title of the original text: "isn't it just peace?" The boss asks for so much spicy, 1 function is all done! "

Hello, everyone!

I am Chang Xiaoan, an excellent student from Akiba Excel data processing class.

Today, my colleague called me after work: Xiao an, come and take a look at it for me!

The boss gave me a bonus data and asked me to find out the sum of bonuses for the department as the "commissary" and the position as the "sales".

Is there any way to solve this problem quickly?

Me: this is not difficult, as long as the multi-conditional summation function-SUMIFS!

Function introduction SUMIFS is a multi-conditional summation function. First, let's take a look at the official introduction of the function:

= SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

Translate:

= SUMIFS (summation region, region 1 for judging conditions, region 2 for judging conditions, condition 2,... )

In this function, the first three parameters are necessary, that is to say, there must be at least one summation region, a conditional judgment region and a condition.

The following parameters can add conditional areas and conditions in pairs according to the actual situation. (there is a case later.)

Next, let's take a look at the specific case analysis and operation of multi-conditional summation.

In the data below in the case operation, B is listed as "department", C as "post" and F as "bonus".

We need to require the department to be the "commissary" and the position to be the sum of bonuses corresponding to "sales".

You can use this function formula:

= SUMIFS (FRB, "commissary", "C", "sales") put these data in the parameter area one by one, and the bonuses that meet the conditions can be calculated.

Procedure:

❶ enter the formula in G6 cell: = SUMIF, click insert function [fx]

❷ summation region selection column F: bonus column; conditional region 1 selection column B: Department column

Condition 1 input: commissary

Conditional area 2 Select column C: post column

Condition 2 input: sales.

Get the final result easily.

Colleague: if there are new conditions, for example, on the basis of just now, people whose seniority is equal to 10 years of service will be asked for a bonus. Do I just need to add a pair of new conditions at the end of the function?

Me: yes, you just need to add an additional pair after the first two pairs of areas and conditions. For example, the following figure:

Colleague: great! This function Get has arrived!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: witty Qiu Xiao E

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.