How to use the sumifs function 10

The sumifs function can quickly sum multi-conditional cells.

sumifs function syntax:

sumifs(sum region, conditional region 1, conditional region 1, conditional region 2, conditional region 2, conditional region n, conditional n).

The first parameter is the summation region, and the following condition regions correspond to the conditions one-to-one, which is illustrated by four cases below.

For example, the following diagram shows:

Calculate the total score of Xiao Ming's three subjects.

sumifs(c92:c100,a92:a100,e92)

e92 is the output area, blue is the sum area, red is the condition area, and Xiao Ming in the green box is the condition.

The sum range cells are summed only when all the corresponding specified conditions are met.

sumifs(sum range, criteria range1, criteria1, [criteria range2, criteria2],1) criteria range1 is the first region where the associated conditions are calculated.

2) Criteria1 is Condition 1, which is in the form of a number, expression, cell reference, or text, which can be used to define which cells in the Criteria Range1 parameter will be summed. For example, a condition can be represented as 32, ">32", b4,"Apples", or"32"。

3) criteria range2 is the cell range used for condition 2 judgment.

4) Criterion2 is Criterion 2. and 3) both in pairs. A maximum of 127 regions and condition pairs are allowed, that is, the total number of parameters cannot exceed 255.

5) Sum Range is the actual cell that needs to be summed. Include numbers or names, regions, or cell references that contain numbers. Ignore blank and text values.

1 6 Step by Step Reading.

See 1 image remaining.

Here, we take the insertion of the summation function as an example to explain how to insert a function into ** for solving the operation.

Click on the Bid section in the dialog box to select an area.

After selecting the region, click the part in the picture to return, and then click the OK button.

See 1 image remaining.

After the results of the statistics, don't you feel that you are very strong! As long as you learn, it's very simple.

In the same way, the average of the "salary" item can be solved.

The sumifs function can be used as follows:

Tools: Desktop or laptop, WPS Office.

1. Inquire about Qi Sui to open the computer and open the WPS Office.

2. Enter "=sumifs" in the blank box or click the fx function above, find the sumifs function in the common functions, and click "OK".

3. Pull the corresponding value in "Sum Area Cavity", "Region 1" pulls the corresponding parameter, and "Condition 1" selects one of the parameters, and click "OK" when finished. <

4. After calculating the first number, you can directly pull all the numbers in the lower right corner, and click to view the data, which is convenient for future inspection and opening work. <>

5. sumifs function function: sum the cells that meet multiple conditions in the region, structure: =sumifs(sum region, region 1, condition 1, region 2, condition 2...

1. Single-condition summation.

We want to know the number of TVs sold, the formula entered in the cell is: =sumifs(c:c,b:b,"Television"), the conditional text must be enclosed in double quotation marks.

If the value of cell e6 is the TV, you can refer to it directly, using the formula: =sumifs(c:c,b:b,e6).

2. Multi-condition summation.

To know the number of TVs sold in Hangzhou, the formula we enter is:=sumifs(c:c,b:b,"Television",a:a,"Hangzhou")

Two of these conditions are in no order, and the formula can also be: =sumifs(c:c,a:a,"Hangzhou",b:b,"Television"Of course, as with single-conditional summation, text can also refer to the position of a cell.

3. Fuzzy summation.

Need to find the sales summary of Huawei mobile phones, as long as the model has Huawei keywords, then we can match the wildcard symbol, the input formula is:=sumifs(c:c,b:b,"Huawei*")

Of course, if the value in a cell is Huawei, you can use the formula: =sumifs(c:c,b:b,e6&"*"Cells need to be used"*"Connect.

4. Summing of multiple conditional values.

The values of the previous condition area,All correspond to 1 condition,For example, in the model,It only corresponds to Huawei,If we need to find the Huawei P30 and Huawei Mate20 these 2 models now。

Most of you may add up the two sumifs formulas: =sumifs(c:c,b:b,"Huawei P30")+sumifs(c:c,b:b,"Huawei Mate20")

You can also use sumproduct with sumifs:=sumproduct(sumifs(c:c,b:b,))).

5. sumifs encounter time value calculation.

Ask for Huawei's sales in the last three days, because today is July 6, 2019, and the time greater than July 4 is the demand, we use the formula: =sumifs(c:c,b:

b,"Huawei*",a:a,">2019-7-4"), you can write it this way when you enter a time value when you encounter a time.

When the time value is in a cell, you need to write it like this: =sumifs(c:c,b:b,"Huawei*",a:a,">"&e6)

If you are using a time function, you can write it in a similar format as you would reference a cell, using the formula: =sumifs(c:c,b:b,"Huawei*",a:a,">"&today()-3)

sumifs(sum range, criteria range1, criteria1, [criteria range2, criteria2],sum range parameter: refers to the cell or range of cells that are summed.

criteral range: A conditional range, which usually refers to a conditional range that is in the same row as the summing cell or cell format, and will participate in the judgment of the condition when summing.

criterl[n]: is usually a specific value involved in the judgment, which comes from the conditional region.

Example: 1, =sumifs(a2:a9,b2:b9,"=a*",c2:c9,"Lu Ning")

2、=sumifs(a2:a9,b2:b9,"<> bananas",c2:c9,"Lu Ning")

SuMiFs is the sum of multiple conditions, you need a single conditional sum, assuming a1:a10 is the loan, b1:b10 is the loan amount, the loan balance = sumif(a1:

a10,"Borrow",b1:b10)-sumif(a1:a10,"credit",b1:

b10)

You're talking about the sumif function, right? If it is =sumif(condition, range of condition, range of sum data).

The sumifs function can be used as follows:

Operating Equipment: Dell laptops.

Operate the silver stupid system: win10

Operational Stalking Procedure: **2019

1. Take the transcript ** as an example and open **, as shown in the following figure:

2. First find the sum of girls' language scores, as shown in the figure below

3. Enter the formula =sumifs(C2:C8,B2:B8,) in cell G2"Female"), as shown in the figure below:

Fourth, the result obtained is 262, which is consistent with the sum of all girls' language scores, as shown in the figure below

5. Ask for the sum of the total scores of students who score more than or equal to 90 points in both Chinese and Mathematics, as shown in the figure below

6. Enter the formula =sumifs(F2:F8,C2:C8,">90",d2:d8,">90"), as shown in the figure below:

7. There is only one student who has a score greater than or equal to 90 points in both Chinese and Mathematics, and his total score is 247 points, which is exactly the same as the result obtained by the formula, as shown in the figure below

Open oneexcel**, Chunhuai takes this ** as an example, and enters [ in the cell"=sumifs("Select column d and enter ,a:a"Lipstick"，c:c，">120"PressEnter keyThe sumifs function can be used to sum up.

The usage of sumifs normal usage formula is simple.

Definition of sumifs functionIf the first cell of Criteria Range1 satisfies Criteria1 and the first cell of Criteria Range2 satisfies Critera2, then the first cell of Sum Range is counted in the sum, for the rest of the cells in the specified range.

The sumifs function is a mathematical and trigonometric function used to calculate the total amount of all parameters that satisfy multiple conditions, sumifs is a new function in office 2007, which can be used to quickly sum multi-condition cells, and only when the cells in the sum range parameter meet all the corresponding specified annihilation conditions, the cells will be summed.

Select the blank cell you want to calculate, click the function button in front of the box, search and select the sumifs function. Enter the cell range of the actual sum of the absolute acres in the sum range, enter the data area of the first judgment condition in criteria range1, enter the second judgment condition in criteria2, enter all judgment areas and judgment conditions in turn, and click OK.

Tools Raw Materials:

ASUS Choice 2 Windows 10

1. Click on a blank cell.

After opening the file, click the blank cell where you want to display the results of the function calculation.

2. Select the sumifs function.

3. Select the actual summation area.

4. Enter the first judgment condition.

5. Enter other judgment conditions.

6. Get the function result.

After entering all the judgment areas and judgment conditions in turn, click OK to find the function result, which is the sum of the total number of years that meet the two conditions of sales greater than 3000 and production greater than 1500 at the same time.