How to use the Sumproduct function in Excel

Meaning of the 1.sumproduct function

1. 1

   The applicable range of the 1.Sumproduct function, in a given set of arrays, then multiplies the corresponding elements of the array, and finally returns the sum of the products.

   Literally, Sumproduct has two English words, sum is and, product is product, so it is the sum of the products.

   END

Syntax format for 2.sumproduct functions

1. 1

   Syntax format for 2.sumproduct functions

   =sumproduct (Array1,array2,array3, ... An array of arrays.

   END

3.sumproduct function Case List--basic usage

1. 1

   3. When the arguments in the Sumproduct function have only one array, the sum of the arrays {1;2;3;4;5;6;7} is 1+2+3+4+5+6+7=28.

2. 2

   4. When the arguments in the Sumproduct function are two arrays, all the elements of the two arrays are multiplied by each other.

   The formula =sumproduct (A2:A8,B2:B8) can be converted into

   =sumproduct (array 1, array 2)

   =sumproduct ({1;2;3;4;5;6;7},{1;2;3;4;5;6;7}) =1*1+2*2+3*3+4*4+5*5+6*6+7*7=140.

3. 3

   5. When the arguments in the Sumproduct function are three arrays, all the elements of the three arrays are multiplied by each other.

   END

4.sumproduct function Case--multi-conditional summation

2. 1

   6. Single conditional sum------statistics on the shipment of Chengdu Shipping platform.

   =sumproduct (a2:a13= "Chengdu delivery Platform") * (B2:B13))

   You may have doubts about seeing this formula, which seems to be different from the syntax format and actually sees it as just one parameter. Because when a logical array of true and false is present in the function, the formula is written in this format =sumproduct ((a2:a13= "Chengdu Shipping Platform") (B2:B13)), multiplied by 1, to convert it into a group to participate in the operation. Otherwise, it's written in the top form.

   Equation decomposition

   =sumproduct ({array 1}*{array 2})

   =sumproduct ({TRUE; ..... TRUE; true}*{11012, ..... 41568;.. 12506})

   =1*11012+1*41568+1*12506=65086.

   For the use of the SUMIF function, you can watch the experience of the SUMIF function in Excel using a small compilation.

4. 2

   7. Multi-Conditional summation--------------------the delivery platform for Chongqing

   =sumproduct (a2:a13= "Chengdu delivery Platform") * (c2:c13= "Chongqing delivery Platform") * (D2:D13))

   For the use of the SUMIFS function, you can watch the experience of the SUMIFS function in Excel using a small compilation.

6. 3

   8. Multi-Conditional summation----------------the delivery of Chengdu delivery platform and Chongqing delivery platform as long as one of the conditions

   Input Formula =sumproduct ((a2:a13= "Chengdu Shipping Platform") + (a2:a13= "Chongqing delivery Platform"), (B2:B13))

   Note: sumproduct (conditional 1* Condition 3 ... Condition N) *: satisfies all conditions; sumproduct (conditional 1+ conditional 3...+ condition N) +: satisfies either condition.

   END

5.sumproduct function Case--multi-condition counting

1. 1

   9., Input formula =sumproduct ((a2:a13= "Chengdu Shipping Platform") * (c2:c13= "Chongqing delivery Platform"). Use the Sumproduct function to count the effects of true and false logical arrays 1 and 0.

   Formula parsing =sumproduct ({1;0;0;0;0;0;0;0;0;1;0;1}*{1;0;0;0;0;0;1;0;0;1;0;0}) =2

   END

6.sumproduct function--to achieve conditional ranking

1. 1

   10. Input Formula =sumproduct ((d2< $D $: $D $8) * +1

   The array formula d2< $D $: $D $8 Here is a logical array of "TRUE and FALSE" {false;true;true;false;true;false;true} converted to 1 and 0 of the array {0;1;1;0;1;0;1}.

   This is used to compare the results with the results, and by measuring the results above this score, each time it accumulates 1.

   END

Note points used by 7.sumproduct functions

1. The argument after the 11.sumproduct function must be an array, and the dimensions of the row and column are consistent. Parameter dimensions are inconsistent so return error value #value!

2. 2

   12.sumproduct functions, each parameter of a comma partition must be a numeric data type.

   If it is the logical value of the result of the judgment, it will be converted to a number by 1.

   If you do not use commas, directly connected with the * number, it is equivalent to multiplication, you do not have to add * *.

How to use the Sumproduct function in Excel