SUM function is the most familiar to everyone and the simplest summation function in Excel, but many people's understanding of it is limited to the summation of selected cells or cell ranges.
Actually, the SUM function is not as simple as we thought. It can help us complete various sums, such as regular sums, single condition sums, multi-condition sums, combined cell sums, and multi-table sums.
1 Conventional summation
WINTER
Conventional summation is the most basic usage of the SUM function, which is to sum the selected cells or cell ranges. It can sum continuous cell ranges or discontinuous cells or Sum the range of cells.
When summing a continuous cell range, you can directly select the cell range that needs to participate in the calculation and store the calculation result, and click the "automatic summation" button.
For example, A is a car sales company. The following figure shows the monthly sales of each car product of A company in 2019. When calculating the total sales of each product, you can directly select the B2: N17 cell area and click " The “Auto Sum” button in the “Function Library” group of the Formula tab can calculate the total sales of each product in 2019.
When summing discontinuous cells or cell ranges, they need to be separated by commas (,) entered in English.
For example, when calculating the total sales volume of each automobile product in the first quarter (January to March) and the fourth quarter (October to December), you need to enter the formula "=SUM(B2: D2, K2: M2)", press [Enter] to calculate the total sales volume of product "SA001", and then fill in the formula downwards to calculate the total sales volume of other products in the first and fourth quarters. The effect is shown in Figure 2-5 Shown.
Attention!
When using the SUM function to calculate the sum, if the parameters are text, logical values and spaces, they will be automatically ignored.
But the error value will not be ignored. If the parameter contains an error value, the formula will return an error value.
2 Single conditional summation
WINTER
Conditional summation refers to the summation of areas that meet a certain condition.
For example, a company has 3 sales stores. As shown in the figure below, the number of orders signed by each salesperson in each store on October 1, 2019. The company requires statistics on the total number of orders signed by each sales store on that day. The
SUM function cannot directly perform summation operations on the regions that meet the conditions. In this case, an array formula is needed to store the qualified data in the virtual memory first, and then use the SUM function to extract the required sum data from the memory for summation.
In addition, because it calculates the cells that meet the conditions in the summation area, you need to use "*" to connect the conditions and the summation area.
In this example, if you want to count the total number of orders signed in the "Clear Water Store", you need to enter the formula "=SUM(($A$2 :$A$13=E2)*($C$2: $C$13 in the F2 cell ))", press [Ctrl+Shift+Enter] key combination to convert the formula to an array formula "{=SUM(($A$2 :$A$13=E2)*($C$2: $C$13))}" , And get the calculation result.
If you want to calculate the total number of orders in other sales stores, you can fill in the formula downwards. The A2: A13 and C2: C13 cell ranges in the
formula use absolute references to facilitate the downward filling of the formula, so that when calculating the total number of orders for "Longmadian" and "Laixindian", there is no need to do anything to the formula edited. The
array formula "{=SUM(($A$2: $A$13=E2)*($C$2: $C$13))}" means that if the value in the range A2: A13 is equal to the value in cell E2, It returns TRUE, which is 1, otherwise it returns FALSE, which is 0. The returned value forms a memory array, andC2: Multiply the corresponding values in the range of C13, and then sum up the multiplied results to get the sum that meets the conditions.
3 Multi-conditional summation
WINTER
Multi-conditional summation refers to the summation of areas that satisfy multiple conditions.
For example, to count the total number of orders signed by the company "Clear Water Store" greater than or equal to 15, you can enter the formula "=SUM((A2: A13="清水店")*(C2: C13>= in cell I6 15)*(C2: C13))", press [Ctrl+Shift+Enter] key combination to convert the formula to an array formula "{=SUM((A2: A13="清水店")*(C2: C13>= 15)*(C2: C13))}”, and get the calculation result. The meaning of
formula "{=SUM((A2: A13="清水店")*(C2: C13>=15)*(C2: C13))}" is similar to that expressed by the single conditional summation formula, which means " The value returned by (A2: A13="清水店")" forms a memory array, the value returned by "(C2: C13>=15)" forms a memory array, and then the corresponding values in the two memory arrays are multiplied respectively , The obtained results are then multiplied by the values in the range of "(C2: C13)", and finally the obtained results are summed to obtain the sum that meets the conditions.
4 Combine cell summation
WINTER
In daily work, continuous data of the same category is often combined into one cell for display. When
uses the SUM function to perform a summation operation on merged cells, it usually encounters the two situations shown in the figure below. Different situations require different summation formulas.
If the merged cells in the table have the same size, as shown in the "Store Daily Report 2" as shown in the lower left figure, then when using the SUM function for the sum operation, you only need to enter the formula "=SUM(C2: C5)”, and then fill in the formula downwards to calculate the total number of orders for the 3 sales stores. The effect is shown in the lower right figure.
If the size of the merged cells in the table is not the same, such as "Store Daily Report 3" as shown in the lower left figure, then using the above formula, the prompt dialog box shown in the lower right figure will pop up, prompting "All merged cells require Same size", that is to say, this method is not feasible. At this time, you can consider using two summation functions to achieve a one-time summation.
For example, when performing a summation operation on the merged cells in the table shown above, you can first select the cell range D2: D14, enter the formula "=SUM(C2: C14)-SUM(D3: D14)", and press 【Ctrl+Enter】key combination, enter the formula into the selected cells in batches, and automatically update the parameters to calculate the correct result, as shown in the figure below.
When using the SUM function to calculate data with different combined cell sizes, you must ensure that the cells under the calculation area are blank, otherwise the calculation result will be incorrect.
Because the subtracted range will refer to the same number of cells, for example, the "D3: D14" range in the formula of cell D2 refers to 12 cells, so the same part in cells D7 and D11 will also refer to 12 cells Cells will be referenced downward in turn, so the referenced area will exceed the data area. When referencing a range outside the data, if there is data in the referenced cell, the calculation result will be incorrect.
5 Multi-table summation
WINTER
Multi-table summation is to summarize the data in multiple worksheets with exactly the same data structure into a new worksheet.
For example, a production company has 3 workshops, and each workshop produces the same products, but the monthly production quantity is not the same. The following figure shows the table data of the first and third workshops. Now we need to check the monthly output of 3 workshopsTo summarize the total amount of products, you can use the SUM function to quickly achieve multi-meter summation.
If you want to summarize the total production volume of "Product 1" in January in the "Product Production Summary Table", you need to enter the formula "=SUM(1st workshop: 3rd workshop! B2)" in cell B2, and press [Enter] The result can be calculated. If you want to calculate the total monthly production volume of the 3 workshops of other products, you can move the mouse pointer to the lower right corner of cell B2, drag it to the right to cell F2, and then drag it down to cell F13.
