Excel describes sumproduct in detail

Source: Internet
Author: User
Tags arrays

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)

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.