Buzz
An array consists of a series of values in a certain range. Therefore, when calculating with arrays, we work with elements within the array. The SUMPRODUCT function in Excel helps us calculate the sum of the products of elements in two or more different arrays.
GUIDE TO USING SUMPRODUCT FUNCTION IN EXCEL
Syntax: SUMPRODUCT(Array1, [Array2], [Array3],…)
Where:
- Array1: Required
- Array2, Array3: Optional
The SUMPRODUCT function in Excel takes arrays as its arguments, with up to 256 arrays possible. Each array's elements must have the same size, meaning they have the same number of elements.
When encountering non-numeric elements in an array, SUMPRODUCT treats them as the number 0.
Illustrative Example: Given a table of daily sales for a store, calculate the store's revenue with known quantities and product prices.
The formula in cell D5 is: =SUMPRODUCT(C7:C7,D5:D7)
Here are the results:
In this guide, we've demonstrated how to use the SUMPRODUCT function through illustrative examples. By using SUMPRODUCT, you can calculate the sum of the products of elements in one array with corresponding elements in another array. This function is used for calculating revenue of items, total number of items, computing totals, and more. For further reference, you can explore a comprehensive overview of common calculation functions in Excel 2013.
If you're also exploring the STDEV.S function or the Index function, check out the STDEV.S function or the Index function shared by Mytour in the previous article.
