The formula and using method of SUMIF function

sumif function Function

The SUMIF function is used to calculate the sum of all numbers in a range of cells or in an array that meet a specified condition.

　　SUMIF function format

SUMIF (Range,criteria,[sum_range])

　Parameter description

Range (required): a range of cells that represents the criteria to be judged.

Criteria (required): Represents a condition to be judged, in the form of a number, text, or expression. For example, 16, "16", ">16", "book" or ">" &A1.

Sum_range (optional): a range of cells that represents the results to be evaluated based on the criteria. If this argument is omitted, the qualifying cells in the range of cells specified in the parameter range are summed.

　SUMIF function Usage Considerations

(1) When a comparison operator is included in the criteria argument, the operator must be enclosed in double quotes, or the formula will be faulted.

(2) You can use wildcard characters in the criteria argument-question mark (?) And the asterisk question mark is used to match any single character, and the asterisk is used to match any number of characters. For example, looking at the end of a cell contains all the contents of the word "mall", which can be written as "* Shopping mall". If you need to find a question mark or the asterisk itself, you need to enter a tilde (~) before the question mark or asterisk.

(3) The parameter Sum_range can be abbreviated, that is, only the upper-left cell of the range is written, and the SUMIF function is automatically extended from the cell to a range of ranges such as the parameter range. For example, for a formula =SUMIF (A1:A5, ">3", B2), the parameter sum_range simply enters a cell reference B2, which is equivalent to =SUMIF (A1:a5, >3, B2:b6).

(4) Range and Sum_range must be reference to a range of cells, not an array.

SUMIF function Use method instance

SUMIF function Use Method Example one: Calculate the sum of employees ' annual salary in a department

This example effect is shown in Figure 1, enter a formula in cell G1 and press the "Enter" key to calculate the total salary of the employees in the engineering department. The formula is as follows:

=SUMIF (B2:B14, "engineering Department", D2:D14)

Figure 1 Calculating the sum of employees ' annual salary in a department

　　SUMIF function Use example two: Calculate total sales for top two and two employees

This example results in Figure 2, enter a formula in cell E1 and press the "enter" key to calculate the sum of sales for the top two and the last two employees. The formula is as follows:

=SUMIF (B2:B10, ">" &large (b2:b10,3)) +sumif (B2:B10, "<" &small (b2:b10,3))

Figure 2 calculates the total sales of the top two and the last two employees

Formula resolution: The first SUMIF function uses the ">" &large (b2:b10,3) as a condition that represents the 3rd largest data in the region B2:B10, the first two in the range. Similarly, the second SUMIF function uses "<" &small (b2:b10,3) as a condition that represents less than the 3rd lowest data in the region's B2:B10, that is, the first and second lowest data in the region.