A variety of Excel table condition automatic summation formula

One, using the SUMIF () formula of a single condition sum:

To count the data in column C, the statistical condition is that the data in column B is "conditional one". And put the results in cell C6, we just enter the formula in the C6 cell =sumif (B2:B5, condition one, c2:c5) to complete this statistic.

Two, sum () function +if () function nested way double conditional sum:

If the statistical production of the production of a class of quality of "qualified" product totals, and put the results in E6 cell, we use the "conditional sum" function to achieve:

① Select the "tools → Wizard → conditional sum" command, in the pop-up dialog box, press the button on the bottom right with "-", select the D1:i5 area with the mouse, and press the button on the right side of the window with a red Arrow (Restore dialog box State).

② Press "Next", in the pop-up dialog box, press the Drop-down button to the right of "sum column" to select the "Throughput" item, press the Drop-down button to the right of the condition column, operator, compare value, and then select the production team, the = (default), the Production one class option, and then press the Add Condition button. Repeat the preceding operation, set the condition column, operator, compare value to quality, =, qualify, and press the Add Condition button.

③ two times Click "Next", in the pop-up dialog box, press the button on the lower right with "-", select E6 cell with the mouse, and press the button on the right side of the window with a red arrow.

④ Press the Finish button, the result of the eligible rollup is automatically and accurately displayed in the E6 cell.

In fact, the above four steps can be done with a formula, because the formula contains an array formula, in the E6 cell directly enter the formula: =sum (if (d2:d5= "Production class", if (i2:i5= "qualified", E2:e5)), and then hold ctrl+shift+ Enter to get the formula entered to take effect.

The IF formula above can also be changed, SUM (if (d2:d5= "Production Class") * (i2:i5= "qualified"), E2:e5), and the same, you can apply flexibly, but note that IF it is nested up to 7 layers.

In addition to the above two methods I commonly used, and I found that there is a network using the product function of the array, which is found in Baidu, I recommend:

Three, sumproduct () function mode:

The form is:

A B C D

1 Name class gender balance

2 Zhang 33 five female 98

3 Lee 43 five male 105

4 Wang 53 five female 33

5 Lee 63 five female 46

Now find out the total balance of five girls in three years.

Formula: =sumproduct ((b2:b5= "three years Five") * (c2:c5= "female") * (D2:D5))

Explanation: In a given set of arrays, Sumproduct multiplies the corresponding elements of the array and returns the sum of the products.

Grammar sumproduct (array1,array2,array3, ...) Array1, Array2, Array3, ... is 2 to 30 arrays whose corresponding elements need to be multiplied and summed.

Here ((b2:b5= "Three V") * (c2:c5= "female") * (D2:D5) is an array (b2:b5= "three") * (c2:c5= "female") to determine the row for the cell that satisfies the prepared summation condition, * (D2:D5) Is the range of cells that meet the sum condition.

The third function is not commonly used, I do not know it very well, in order to respect the author's original works, I only put his content posted, please forgive me.

In fact, the second method above (the SUM function nested if function) and the third Way (sumproduct function) can not only achieve the sum of two conditions, but also achieve three conditions and even multiple conditions sum. But the function is slightly changed.

Four, in addition to the above three ways, many friends also like to use conditional filtering, sorting, automatic screening, sorting, and other ways to "sum up", is very simple, I do not describe, in addition I also recommend a new function in the EXCEL2007.

Five, sumifs function

This function is widely used in EXCEL2007 and is more convenient to use, for example in the third way, we can enter the formula: "=sumifs (D2:D5,B2:B5," Three Five ", C2:c5," female ")" Is it easier? "