What is the function of sumproduct function

#What is the function of sumproduct function?

SUMPRODUCT function

The SUMPRODUCT function returns the sum of the products of the corresponding area or array. The default operation is multiplication, but addition, subtraction, and division are also possible.

In this example we will use SUMPRODUCT to return the total sales for a given item and size:

For each product name, size and An example of using the SUMPRODUCT function to return total sales when a single sales value is provided.

SUMPRODUCT matches all instances of items Y/Size M and sum, so in this example 21 plus 41 equals 62.

Syntax

To use the default operation (multiplication):

= SUMPRODUCT （array1，[array2]，[array3]，...）

SUMPRODUCT function syntax has the following parameters:

Perform other arithmetic operations

Use SUMPRODUCT in the normal way, but replace the array argument with the desired arithmetic operator (*, /, , -). After all operations are performed, the results are summed in the usual way.

Note: If using arithmetic operators, consider enclosing array parameters in parentheses and using parentheses to group array parameters to control the order of arithmetic operations.

Remarks

Array parameters must have the same dimensions. Otherwise, function SUMPRODUCT will return #VALUE! error value #REF!. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error because the range sizes are different.

SUMPRODUCT Treats non-numeric array entries as zero.

Example 1

Example of the SUMPRODUCT function that returns the sum of items sold when the unit cost and quantity are provided.

To create a formula using the example list above, type =SUMPRODUCT(C2:C5,D2:D5) and press enter. Each cell in column C is multiplied by the corresponding cell in the same row in column D, and the results are added. The total amount of groceries was $78.97.

To write a longer formula that provides the same result, type =C2 * D2 C3 * D3 C4 * D4 C5 * D5 and press enter. After pressing Enter, the result is the same: $78.97. Cell C2 will be multiplied by D2, the result of which will be added to the result of cell D3 times cell C3, and so on.

Example 2

The following example uses SUMPRODUCT to return the total net sales of the sales agent, here we press both the agent's sales and total expenses. In this case, we are using an excel table, which uses structured references instead of standard Excel ranges. Here you'll see the Sales, Fees, and Agency scopes referenced by name.

Example of the SUMPRODUCT function that returns total sales by sales representative when sales and expenses are provided for each salesperson.

The formula is: =SUMPRODUCT (([Sales]) (Table1 [Expenses])) * (Table1 [Agent] = B8)) and returns all sales and expenses for the agent listed in cell B8 Sum.

Example 3

In this example we want to return the total number of specific items sold by a given region. In this case, how many cherries are sold in East?

Exampe Use SUMPRODUCT to return the sum of items by region. In this case, the number of cherries sold in the East region.

This formula is: =SUMPRODUCT ((B2:B9=B12)*(C2:C9=C12)*D2:D9). It first multiplies the number of occurrences of East by the number of matching occurrences of Cherry. Finally, it sums the values ​​of the corresponding rows in the "Sales" column. To see how Excel calculates, select the formula cell and go to Formulas >Calculate Formulas> Calculate Formula.

PHP Chinese website has a large number of free Excel tutorials, everyone is welcome to learn!

The above is the detailed content of What is the function of sumproduct function. For more information, please follow other related articles on the PHP Chinese website!