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
=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)