Excel summation function sum application tips

The SUM function is the most used function in Excel, which can be used to sum operations to ignore cells with text, spaces, and so on, with simple syntax and convenient use. I believe this is one of the first Excel functions you learn to use. In practice, however, Excel provides more than just a sum function, including subtotal, Sum, SUMIF, Sumproduct, and SUMSQ.

Take a unit payroll as an example to highlight the use of sum (the sum of a set of parameters), SUMIF (summation of the range of cells that satisfies a condition). (Note: For simplicity, the calculation of the tax is ignored in the example.) )

SUM

1. Row or column summation

Take the most common payroll as an example, which is characterized by the need to sum several cells within a row or column.

For example, to ask the unit to pay the actual payroll in May 2001, you can enter the formula in H13:

=sum (H3:H12)

2. Regional summation

The area summation is often used to sum up all the data in a single worksheet. At this point you can leave the cell pointer in the cell where the result is stored, and then enter the formula "=sum ()" In the Excel edit bar, click in the middle of the bracket with your mouse, and then drag through all the cells that you want to sum. If these cells are not contiguous, you can hold down the CTRL key and drag them separately. For cells that you want to subtract, you can hold down the CTRL key and select them one by one, and then manually add a minus sign to the cell that the formula references. Of course, you can use the Formula Palette to do this, but for the SUM function, the manual still comes faster. For example, H13 's formula can also be written as:

=sum (D3:D12,F3:F12)-sum (G3:G12)

3. Note

The argument in the SUM function, that is, the cell or range of cells that is summed cannot be more than 30. In other words, there can be no more than 29 delimiters (commas) in the SUM function parentheses, or Excel will prompt too many parameters. For a constant that requires a sum to be involved, it can be directly referenced in the form of "=sum (cell range, constant)", which generally does not necessarily refer to the cell that holds the constant.

SUMIF

The SUMIF function sums the range of cells that meet a condition, which can be numeric, literal, or expression, and can be applied to personnel, wages, and performance statistics.

Still, for example, in the salary scale, we need to calculate the salary distribution of each department separately.

To calculate the May 2001 overtime pay for the sales department. The input formula for the F15 type is:

=SUMIF ($C $: $C $, Sales department, $F $ $F $)

Where "$C $: $C $" to provide a logical basis for the range of cells, "sales department" for the determination of the criteria is only statistics $c$3: $C the unit in the area of "Sales", $F $: $F The range of cells that are actually summed.