There are many summation functions in Excel, including SUM, SUMIF, SUMIFS, SUMPRODUCT, etc. Let me introduce the specific usage and differences of these functions with examples.
SUM function
Expression: SUM(number1,[number2],…), the sum function is everyone’s The most commonly used function is to find the sum of numbers in a specified area. The summing area can be continuous or discontinuous. It is convenient and flexible and an indispensable function in Excel.
One thing to note is that if the summing area contains non-numeric cells such as text, blank cells, logical values, etc., these will be ignored, and sum will only sum the numbers in them.
SUMIF function
Expression: SUMIF (Range, Criteria, Sum_range), conditional summation function, which sums data that meets the conditions. The first parameter Range is the condition area, the cell area used for condition judgment. The second parameter Criteria is the summation condition, which is a judgment condition composed of text, numbers, logical expressions, etc. The third parameter Sum_range is the actual sum_range. and range, the cell, range, or reference to be summed.
SUMIFS function
Expression: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …), multi-condition cell sum (expression The [ ] in means that the things inside can be omitted, that is, the SUMIFS parameters are at least 3). Different from SUMIF, the first parameter of SUMIFS is the summation area, parameter 2 is the condition area, parameter 3 is the condition, and parameter 4 , Parameter 5 is the second condition area and condition respectively, and so on. Therefore, SUMIFS is more powerful than SUMIF in that multiple judgment conditions can be added. In some cases, SUMIFS can completely replace SUMIF.
SUMPRODUCT function
Expression: SUMPRODUCT (array1,array2,array3,…), SUM summation, PRODUCT product, SUMPRODUCT is the sum of products, given In certain sets of arrays, multiply the corresponding elements between the arrays and return the sum of the products.
1. Basic usage
To find the total price of materials, enter in cell F2: SUMPRODUCT(C2:C10,D2:D10), which is equivalent to =C2*D2 C3*D3 C4*D4 C5*D5 C6*D6 C7*D7 C8*D8 C9*D9 C10*D10
2. Add the conditional product sum
to find the total purchase price of the item equal to the pen. In F2 Enter =SUMPRODUCT((A2:A10="pen")*1,C2:C10,D2:D10) in the cell. The difference here from the above example is that a condition is added to find the total purchase price of the pen item. A parameter A2:A10="pen" is easy to understand. The filtered item is equal to the pen data. The reason why it needs to be multiplied by 1 is because it needs to be converted into an array to participate in the operation, otherwise the correct result cannot be returned.
3. Use it as a conditional summation function (similar to sumif and sumifs)
To find the purchase quantity of pen items, enter =SUMPRODUCT((A2:A10=" Pen")*1,D2:D10), the function here is equivalent to SUMIF(A2:A10,"Pen",D2:D10), and also equivalent to =SUMIFS(D2:D10,A2:A10,"Pen" )
For more Excel-related technical articles, please visit theExcel Basic Tutorialcolumn to learn!
The above is the detailed content of excel sum. For more information, please follow other related articles on the PHP Chinese website!