I. Functional explanation
The basic grammar is:
SUMPRODUCT (array 1, [array 2], [array 3], ...)
The SUMPRODUCT function syntax has the following parameters:
Array 1: required. The first array parameter, whose corresponding elements need to be multiplied and summed.
Array 2, Array 3, ...: Optional. 2 to 255 array parameters whose corresponding elements need to be multiplied and summed.
Please pay special attention to:
Array parameters must have the same dimension. Otherwise, the function SUMPRODUCT will return #VALUE! Error value #REF! .
Second, examples of usage
1, basic usage
The most basic usage of SUMPRODUCT function is:
Multiplies the corresponding elements between arrays and returns the sum of the products.
As shown in the figure below:
Formula: =SUMPRODUCT(B2:B9, C2:C9)
The meaning of this formula is:
B2 * C2+B3 * C3+B4 * C4+B5 * C5+B6 * C6+B7 * C7+B8 * C8+B9 * C9
2. Single conditional summation
As shown in the figure below, calculate the sum of the scores of female employees whose performance scores are higher than 15:
As shown in the figure below, calculate the performance score of female employees:
Formula:
= sumproduct ((B2: b11= "female") *C2:C 1 1)
These include:
B2:B 1 1= "female": add the values in each cell of B2: B 1 1 with? Woman? By contrast, what is gender? Woman? True, otherwise false, the result returns a set of logical values: {FALSE true; Fake; True; Fake; True; Fake; True; Fake; True; }
(B2:B 1 1= "female") *C2:C 1 1: Multiply the values in the above logical array with the corresponding C2:C 1 1 values.
3. Multi-conditional summation
As shown in the figure below, calculate the sum of the scores of female employees whose performance scores are higher than 15:
Formula: = sumproduct ((B2: b11= "female") * (C2: c11> 15)，C2:C 1 1)
The general writing of multi-condition summation is =SUMPRODUCT ((condition 1) * (condition 2) * * (condition n), summation range).
4. Sum of fuzzy conditions
As shown below, calculate the performance scores of female employees in the sales department, and:
There is more than one sales department. If you want to find all the sales departments, you must follow the keyword? Sales? Search belongs to fuzzy search.
Formula:
= sum product (is number (find ("sales", A2: a11)) * (C2: c11= "female"), D2: d1/kloc-0.
These include:
FIND ("Sales ",A2:A 1 1): Find" sales "in each cell value of A2: A11. If it can be found, return the position of "sales" in the cell value; If not, return the error value #VALUE! .
The result of this part is: {#VALUE! ; 1; 1; 1; # Value! ; 1; # Value! ; # Value! ; 1; # Value! }
ISNUMBER(FIND ("sales ",A2:A 1 1)): judge whether each of the above numerical values is a number, and if so, return TRUE; otherwise, return FALSE, and the result of this formula is:
{false; True; True; True; Fake; True; Fake; Fake; True; FALSE}
5. Single condition counting
Count the number of female employees:
Formula: = sum product (n (B2: b11= "female"))
N function:
Syntax: n (value);
Function: Convert non-numerical values into numerical values;
Different parameter values, corresponding return values:
In this example, N(B2:B 1 1= "female") returns 1 to indicate that the value TRUE is equal to female, and returning 0 indicates that the value FALSE is not equal to female.
6. Multi-condition counting
Calculate the number of female employees whose performance score is higher than 15.
Formula:
= sumproduct ((B2: b11= "female") * (C2: c11> 15))
7. Fuzzy conditional counting
Calculate the number of female employees in the sales department
Formula:
= SUMPRODUCT(is number(FIND(" sales "，A2:a 1 1))*(C2:c 1 1 = " female "))
8. Monthly statistics
Requirements: Statistics of total sales on a monthly basis.
The formula is:
=SUMPRODUCT ((monthly $ a $2: $ a $13) = d2) * ($ b $2: b $13))
9. Cross-column statistics
Requirements: Count the total sales and inventory of three warehouses.
The formula is:
= sum product(($ B $ 2:$ G $ 2 = H $ 2)* $ B3:$ G3)
(In this formula, we must pay attention to the use of relative references's absolute quotation. )
10, multiple weight statistics
Requirements: Calculate the total score according to the proportion and weight of sub-items.
The formula is:
= sum product(B＄2 :D＄2，B3:D3)
1 1, two-dimensional regional statistics
Requirements: Count the total sales volume of each commodity in each sales department.
The formula is:
= sum product(($ B $ 2:$ B $ 13 = $ E2)*($ A $ 2:A $ 13 = F $ 1)* $ C $ 2:C $ 13)
12, continuous ranking (Chinese ranking)
The RANK function is used for ranking. If there are the same values, the ranking will be discontinuous. The SUMPRODUCT function is used to avoid this sort discontinuity.
As shown in the figure below:
The formula for C6 cells is:
= sum product(($ B $ 2:$ B $ 7 & gt； = B6)/COUNTIF($ B $ 2:B $ 7，$ B $ 2:B $ 7))
($ B $ 2:$ B $ 7 & gt； =B6), the return value is:
{true; True; True; True; True; FALSE}
Namely: {1; 1; 1; 1; 1; 0}
COUNTIF($B$2:$B$7, $B$2:$B$7), the return value is:
{ 1; 1; 2; 2; 1; 1}
sum product(($ B $ 2:$ B $ 7 & gt； = B6)/COUNTIF($ B $ 2:B $ 7，$ B $ 2:B $ 7))
Namely: sumproduct ({1; 1; 0.5; 0.5; 1; 0}), which is ranked 4.
Do you know other uses of SUMPRODUCT function?