How to use the SUMIF function, how to use the sumif function How to use the SUMIF function

Point directly like this: sumif(conditional cell range, conditional, cell range that needs to be calculated).

If A is listed as a job title and B is listed as a salary, the total salary of the economist title is calculated: sumif(a:a,"Economist",b:b)

sumif is a function of summing.

The formula is "sumif(conditional cell range, condition, cell range that needs to be calculated)".

For example: =sumif(c1:c17,c1>5,f1:f17) means: in the interval c1 to c17, the condition is c1>5, find the sum of the intervals f1 to f17.

It's a bit confusing to say, I don't know if you understand?

Find the sum of the data that satisfies the conditions.

Oh, the sumif function is here, and the brain is buzzing again.

1. Take a practical example as an example, for example, here, we want to sum up in interlaced rows, ignoring other irrelevant factors, so we summarize in column "b" and next row, and deliberately fill in the mark with color.

2. Therefore, there is such a formula, =sum(vlookup(b69,b69:f69,row(1:3)*2-1,0)), sum, and the vlookup function is easy to understand, just talk about the row function, in row(1:

3) In *2-1, 3 is the total number of columns that are summed, that is, the total number of color-coded columns, 2 is the number of columns separated + 1, and 1 in -1 is the number of columns separated by 1.

3. I don't know much about the above, so let's try to sum it every two lines, the formula is, =sum(vlookup(b69,b69:f69,row(1:2)*3-2,0)), the rest is nothing, it's this 3-2, you can think about it.

Eggplant teasing. <>4, this time it's irregular, find a few random to sum, for example, or mark with color, you need to find the marked Shi Nashi demonstration to sum, there is such a formula, =sum(vlookup(b69,b69:f69,,0)).

5. Formula analysis, whether it is interlaced summing or specific cell summing, is inseparable from the sum function and vlookup function, but if it is a regular sum, such as every two lines or every three lines, the row function is needed.

6. The row function is to return the specified function, and the row used here, row(1:3) or row(1:2), represents the number of rows, is a set of numbers, if it is represented by an array, that is, and, more usage, you can study it carefully.

1. There are three parameters in sumif, and one parameter represents the area that needs to be judged by the condition, which is usually a one-dimensional array. The second parameter indicates the condition of the judgment and is the judgment of the first parameter. The third parameter represents the region of summing.

Let's take an example of finding the total score of the second class. Open Excel.

2. Enter "=sumif()" in the cell

3. Fill in the first parameter "A2:A22", which means that the area that needs to be judged is A2 to A22.

4. Enter the second parameter "second class", and note that the double quotation marks are used in the English format to remove the macro.

5. Enter the third parameter "b2 to b22", which is the summation area.

6. Enter to find the total score of the second shift.

Assuming the age is a1:a6 and the salary is b1:b6, the formula can be expressed as:

sumif(a1:a6,"<30",b1:b6)-sumif(a1:a6,"<20",b1:b6)

The sumif function is a commonly used function in Excel. Use the sumif function to sum the values in the report range that meet the specified criteria.

The usage of the sumif function in Excel is to sum several cells, regions, or references based on specified criteria.

How to use the sumif function:

Operating environment: ASUS Tianxuan 2, Windows 10, Excel 2022.

1. After entering the ** file, click the empty pickpocket white cell that you want to display the result of the conditional summation.

<>3. Enter the range of cells to be judged in the range column, and enter the conditions in the criteria column.

4. Enter the actual sum of the cell range, which is the sum of the monthly sales volume with a yield greater than 130.

5. If you ignore the sum range column, then the parameter of the range will be used as the parameter of the sum range, which is the sum of the output of the month with a yield greater than 130.

The method of using sumif:sumif(range,criteria,sum range). sumif(conditional region, summation condition, actual summation area), the second summation condition parameter is in the first conditional area.

The first parameter: range is the range of cells, which is used for conditional judgment. The second parameter: criteria is the summation condition, which is composed of numbers, logical expressions, etc.

What the sumif function does:The sumif function can sum the conditions of the number within the specified range, and its conditional summation function is very powerful. Used to determine the conditions for summing cells, which can be in the form of numbers, expressions, cell references, text, or functions.

The actual cells that require the sum (if you want to sum the cells that are not specified in the range parameter). If you omit the sum range parameter, excel sums the cells specified in the range parameter (that is, the cells to which the condition applies).

Assuming that the department name is in column A and the cost is in column B, the cost of a department is:=sumif(a:a,"A certain department",b:b)

The syntax format of the sumif function:

sumif(range，criteria，sum_range)。

sumif(conditional region, summing condition, actual summation area), the second summation condition parameter is in the first conditional region.

The three parameters of sumif are as follows:

The sumif function syntax is: =sumif(range,criteria,sumrange).

Parameters of the sumif function: The first parameter: range is the range of cells, which is used for conditional judgment.

The second parameter, criteria, is the summation condition, which is composed of numerical arguments, logical expressions, etc.

The third parameter: sum range is the actual sum range, which needs to be summed by the cells, ranges, or references.

Expand your knowledge:

1. Description:

The cells in the sum range are summed only if the corresponding cells in the range meet the criteria. If the sum range is omitted, the cells in the range are summed. Microsoft Excel also provides a number of other functions that can analyze the data based on conditions.

For example, if you want to count the number of times a text string or number appears within a cell area, you can use the countif function. If you want the formula to return one of the two values based on a condition (for example, a sales bonus based on a specified sales amount), you can use the if function.

The sum range parameter can be different in size and shape from the range parameter. The actual spine cells that are summed are determined by using the cell in the upper left corner of the sum range parameter as the starting cell, and then including the cells corresponding to the size and shape of the range parameter.

However, when the range and sum range parameters in the sumif function do not contain the same number of cells, the worksheet recalculation may take longer than expected.

2. Function:

The sumif function is a commonly used function in Excel. The sumif function is a common function in Excel that can sum the values in the report range that meet the specified conditions.

The sumif function is used to sum a number of cells, regions, or references based on a specified condition, which can be a numeric value, text, or expression, and can be applied to personnel, payroll, and achievement statistics.

Conditional summing can be done using the sumif function: first click on the blank cell. Click on the formula in the menu bar and click Insert Function.

Select the sumif function and click OK limb argument. Select the cell range and enter the criteria, and finally click OK.

The sumif function syntax has the following parameters (parameters: Values that provide information about an action, event, method, property, function, or procedure. When using the sumif function to match a string with more than 255 character epochs, an incorrect result is returned value!.

The parameters of the sumif function are as follows: the first state parameter: range is the conditional region, which is used for the cell area of the condition judgment.

The second parameter: criteria is the summation condition, which is composed of numbers, logical expressions, etc. The third parameter:

Sum Range is the actual sum range of cells, ranges, or references that need to be summed.

When the third parameter is omitted, the conditional region is the actual summation region. criteria parameter, including a question mark (?and an asterisk (*The question mark matches any single character; The asterisk matches any string of characters.

If you want to find the actual question mark or asterisk, type a tilde (before the character.)