Excel describes sumproduct in detail

I. sumproduct function grammar and function

Sumproduct function: Calculates the sum of the multiplication of the corresponding values in multiple columns within a worksheet.

Its syntax is:

Sumproduct (Array1,array2,array3, ...)

Among them, Array1, Array2, Array3, ... is 2 to 30 arrays whose corresponding elements need to be multiplied and summed. The array parameter must have the same dimension, otherwise the function Sumproduct will return the error value #VALUE!. This function handles an array element of a non-numeric type as 0.

Examples of the product of sumproduct function

The table is as follows:

A B C D (column number)

1 2 10 20 (line 2nd)

3 4 30 40 (line 3rd)

5 6 50 60 (line 4th)

Formula: =sumproduct (A2:B4, c2:d4), Description: All elements of two arrays are multiplied, then the product is added, i.e. 1*10 + 2*20 + 3*30 + 4*40 + 5*50 + 6*60 (Result 910)

Two, multiple conditions sum + find number

A, the use of sumproduct for multiple conditions count, counting is the number of statistics to meet the conditions.

Syntax: =sumproduct (condition 1) * (Condition 2) * (condition 3) * ... (condition N))

Function: Count the number of records that meet condition 1, condition 2 to condition n at the same time.

Example: =sumproduct ((a2:a10= "Male") * (b2:b10= "intermediate title"), Formula explanation: Statistics sex is male and the title is the number of employees with intermediate title

B, using sumproduct to sum up multiple conditions

Syntax: =sumproduct (condition 1) * (Condition 2) * (condition 3) * ... (condition N) * a region)

Effects: Summarizes the total amount of the specified area for records that meet condition 1, condition 2, and condition N. Example: =sumproduct ((a2:a10= "Men") * (b2:b10= "intermediate title") *C2:C10)

Formula Explanation: Statistic sex is male and the job title is the sum of the employees of the intermediate title (assuming c as salary)

Sumproduct (condition 1* condition 2* Condition 3 ... Conditional n) Using this function for multiple condition counts, *: satisfies all conditions;

Sumproduct (condition 1+ condition 3...+ condition N) +: satisfy any condition

Example one:

=sumproduct ((g1:g3= "Men") * (E1:E3<=60))

The meaning of this formula is to count, the g1:g3 satisfies the condition is male, simultaneously e1:e3 the numerical value is less than equal to 60, satisfies these two conditions the total number. First, this is an array formula, which ends by Ctrl+shift+enter.

Example two: Multiple conditions sum

Male Pass Number: =sumproduct ((c2:c13>=60) *1, (b2:b13= "men") *1)

Example three: Multiple conditions sum

Summary of the three classes for the number of men in Zhejiang, =sumproduct (b2:b13= "male") *1, (d2:d13= "Class three") *1, (e2:e13= "Zhejiang") *1)