Excel function learning: Let's talk about the SUMPRODUCT function

In the previous article "Practical Excel Tips Sharing: Two Ways to Quickly Delete Blank Rows in Batch", we learned the two fastest and fastest techniques for batch deleting blank rows in Excel. The following article will teach you how to use Excel functions and talk about the usage of SUMPRODUCT function. I hope it will be helpful to you!

It is recommended that partners use English to understand the SUMPRODUCT function: sum is sum, product is product, and the combination is the sum of products.

The SUMPRODUCT function in Excel is an array type function.

In many cases, the SUMPRODUCT function can be used to replace the array formula of the SUM function, and there is no need to press the third key to end. Friends who left a message yesterday asking about the difference between the SUM function and the SUMPRODUT function can read this article carefully.

The usage of the SUMPRODUCT function is to multiply the corresponding elements between the arrays in the given sets of arrays and return the sum of the products.

First, get to know the SUMPRODUCT function

The syntax of the SUMPRODUCT function: SUMPRODUCT(array1,array2,array3, ... )

where Array1, array2, array3, ... are 2 to 30 arrays, and their corresponding elements need to be multiplied and summed.

Let’s first understand the SUMPRODUCT function through a simple case.

Enter the formula: =SUMPRODUCT(A2:B4*C2:D4), which is to combine all the areas A2:B4 and C2:D4 The elements are multiplied correspondingly, and then the products are added, that is, 3*2 4*7 8*6 6*7 1*5 9*3, and the result is 156.

Enter the formula: =SUMPRODUCT(A2:B4), and the result is 31. According to the above points, if it is an array, then it is the sum of this array, so it is the sum of the {3,4;8,6;1,9} area.

Tip: The multiplication of two arrays is the multiplication of two corresponding numbers in the same row. Array data is enclosed in braces {}, and rows of data are separated by semicolons ";". If the data is in the same row, use commas ","separated.

There are three points to note when using the SUMPRODUCT function:

1. The array parameters must have the same dimensions, otherwise, the function SUMPRODUCT will return the error value #VALUE !.

2, function SUMPRODUCT treats non-numeric array elements as 0.

3, if it is an array, then it is the sum of this array.

Second, SUMPRODUCT function application: counting

The SUMPRODUCT function is used for multi-condition counting to calculate the number of data that meet 2 or more conditions. number. The formula is: SUMPRODUCT((Condition 1)*(Condition 2)*(Condition 3)*...)

1, Statistics on how many copies of the book "Computer Basics and MS Office Applications" have been sold.

#This question is about single condition summation. First of all, you need to know what the condition is. If the B2:B12=B2 area is equal to B2, this part is the condition.

If you directly enter =SUMPRODUCT(B2:B12=B2), the result will be 0. The first part of the usage introduction introduces: The function SUMPRODUCT treats non-numeric array elements as 0, B2:B12=B2, press the F9 key to get the execution result, which is a logical value in the form of true or false, so it is equal to 0.

Then how to convert logical values ​​into numerical values? You must let the logical values ​​participate in the operation. You can use --, *1, 0etc. For example, =TRUE*1, the result is 1. =FALSE*1, the result is 0. Therefore, just add *1 outside (B2:B12=B2).

The formula is: =SUMPRODUCT(1*(B2:B12=B2)), the result is 3 books.

2, count how many books "Computer Basics and MS Office Applications" Wang Yaodong has sold

There are two conditions for this question : First, the statistical graphics and text are from the book "Computer Basics and MS Office Applications", represented by B2:B12=B2. Second, the salesperson is Wang Yaodong, represented by C2:C12="Wang Yaodong".

Apply SUMPRODUCT((Condition 1)*(Condition 2)*(Condition 3)*...), and get the formula: =SUMPRODUCT((B2:B12=B2)*(C2:C12="Wang Yaodong")), the result is 2 books.

Third, SUMPRODUCT function application: summation

Use function SUMPRODUCT to sum. One of the parameters required by the function is the condition for judgment. The other is the data area used for summing.

SUMPRODUCT function summation application format: SUMPRODUCT((condition 1)*(condition 2)*(condition 3)*…*summation area)

1 , statistics on book sales of "Computer Basics and MS Office Applications".

Apply the format and get the formula: =SUMPRODUCT((B2:B12=B2)*C2:C12)

2, count the book sales of Wang Yaodong's "Computer Basics and MS Office Applications"

=SUMPRODUCT( (B2:B12=B2)*(D2:D12=D2)*C2:C12)

excel tutorial

The above is the detailed content of Excel function learning: Let's talk about the SUMPRODUCT function. For more information, please follow other related articles on the PHP Chinese website!