What is SUMPRODUCT Function?
SUMPRODUCT(array1, [array2], [array3], ...)
array1, required. The first data to be multiplied, then sum.[array2], optional. The second data to be multiplied, then sum.
- array argument must have the same dimensions, otherwise it will generate error #VALUE!.
- Any non-numeric value will be considered as number 0
How to Use SUMPRODUCT Function in Excel
SUMPRODUCT Function #1
The result is 21.
SUMPRODUCT Function #2
The SUMPRODUCT function use 2 arrays. Excel will multiply the number in the first array with the number in the second array then add it up.
The result is 94.
SUMPRODUCT Function #3
Similar to the SUMPRODUCT function #2, but the first and second array dimensions are not the same. The first array has 7 numbers while the second array has only 6 numbers. Array dimensional differences generate error #VALUE!.
SUMPRODUCT Function #4
The SUMPRODUCT function use 3 arrays, all have the same dimensions, but the first array contains text. All data other than numbers will be considered 0. All numbers multiplied by the number 0 result is 0.
You can see the complete results of all SUMPRODUCT functions in the picture below.
What is the price of all smartphone purchases above? Which excel function will be used?
Simple, fast, accurate and no helper column 🙂