Summary of formulas for the sum of various conditions in Excel

People who often deal with execl certainly feel that the summation formula is often used by everyone. What are some of the ways to sum formulas in Excel? How are they used? I'll summarize it for you.

2. Single conditional summation using the SUMIF () formula:

   To count the data in column C, the statistic condition is that the data in column B is "condition one." and put the result in C6 cell, we simply enter the formula "=SUMIF (B2:B5," condition One ", c2:c5)" in cell C6 to complete this statistic.

4.  

   sum () function +if () function nested by double-conditional summation:

   If the quality of the production of one class is the total number of "qualified" products, and put the result in E6 cell, we use the "conditional sum" function to implement:

   ① Select the "tools → Wizard → conditional sum" command, in the Pop-up dialog box, press the button with "-" on the right, with the mouse to select the D1:i5 area, and press the button with a red arrow to the right of the window (to restore the

   ② Press Next, in the Pop-up dialog box, press the drop-down button to the right of the sum column to select the throughput item, and then press the drop-down button to the right of the condition column, operator, comparison value, select the production track, the = (default), the production shift option, and then press the Add Condition button. Repeat the preceding actions to set the condition column, operator, comparison value to quality, =, qualify, and press the Add Condition button.

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

   ④ Press the Finish button, at which time the summary results that match the criteria are automatically and accurately displayed in cell E6.

   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 press CTRL + The Shift+enter key to allow the input formula to take effect. The IF formula on the

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

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

6. Sumproduct () function mode:

   The table is:

   A B C D

   1 Name class gender balance

   2 Sheets 33 Five female 98

   3 Li 43 Five male 105

   4 Kings 53 five female 33

   5 Lee 63 five female 46

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

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

   Explanation: Sumproduct multiplies the corresponding elements between the arrays in a given set of arrays and returns the sum of the products.

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

   Here ((b2:b5= "three-year-five") * (c2:c5= "female") * (D2:D5)) is an array where (b2:b5= "three years Five") * (c2:c5= "female") is the row that determines the cell containing the prepared summation criteria, * (D2:D5) Is the range of cells that satisfy 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, I only put his content, please forgive me.

   In fact, the second way above (the SUM function nested if function) and the third Way (sumproduct function) can not only realize the double conditional summation, but also can achieve the three conditional summation or even multi-conditional summation. Just a little change in function.

8. 4

   sumifs function

   This function is widely used in EXCEL2007, it is more convenient to use, such as the third way of example, we can enter the formula: "=sumifs (D2:d5,b2:b5," Three years Five ", C2:c5," female ")", is not easier?

Summary of formulas for the sum of various conditions in Excel