Sep 06, 2016
SUMPRODUCT is about more than sums and products.
This is what Excel's Insert Function feature says of SUMPRODUCT: 'Returns the sum of the products of corresponding ranges or arrays'. But SUMPRODUCT is a much more versatile function than this description suggests, and it is not limited to the summation of products.
The syntax of SUMPRODUCT as indicated by Excel is this:
SUMPRODUCT(array1,array2,array3, ...)
In this context an array is a continuous range of cells in a row or column. The number of cells must be the same in each array, as in this example of a formula:
=SUMPRODUCT(B2:B11,C2:C11)
When applied to the following simple spreadsheet the result of the formula is 770:
What SUMPRODUCT has done is this:(1*2)+(2*4)+(3*6)+ … + (10*20) = 770
The same result is achieved by this version of the formula, in which an asterisk replaces the comma:
=SUMPRODUCT(B2:B11*C2:C11) - Multiply
It follows that the sign replacing the comma separating the arrays determines the mathematical operation that SUMPRODUCT engages between the pairs of values in the two arrays.
Thus this SUMPRODUCT returns the result 165:
SUMPRODUCT(B2:B11+C2:C11) = (1+2)+(2+4)+(3+6)+ … + (10+20) - Addition
And this one returns the result 5:
SUMPRODUCT(B2:B11/C2:C11) = (1÷2)+(2÷4)+(3÷6)+ … + (10÷20) - Division
The examples above are based on arrays arranged in columns. SUMPRODUCT works equally well with arrays of values arranged in rows.
For more information, take a look at New Horizons' Microsoft Excel training courses.
How do your Excel skills stack up?
Test NowNext up:
- I am merging traditional and digital selling – And it is working!
- Float workloads into the cloud with Microsoft Azure
- How-to: Install Windows 10 using the media creation tool
- Assertiveness in practice
- Working with Styles - Part 4
- How to improve your business writing
- SQL Server 2016 - Row Level Security (RLS)
- Automate your website publishing process using Github and Azure
- Excelling in Excel
- 3 TED talks on habit
Previously
- I am merging traditional and digital selling – And it is working!
- How-to: Validate data based on the value of a different cell in Excel
- Make your content fluid with AngularJS
- Great managers provide great feedback
- What makes you healthy can also make you a good Social Seller (Infographic)
- Are you using these features in Microsoft Word?
- SQL Server 2016 - Always Encrypted
- Is Emotional Intelligence B.S.*?
- It is not about 'Traditional' OR 'Social' selling ... It is about 'Traditional' AND 'Social' selling
- ITIL® RACI Matrix