The SUMPRODUCT function first multiplies corresponding arrays, then returns with the sum.
=SUMPRODUCT (array1, [array2], …)
array1 and [array2] are arrays whose values you want to first multiply then add.
For example, the SUMPRODUCT function mentioned below calculates the total cost of the transaction.
The SUMPRODUCT function calculates the sum as under:
(2 * $10) + (2 * $5) + (2 * $19) = $68
If any text entry found within selected directions, then Excel will treat it as zero and returns with calculation of remaining values.
The arrays must have same directions otherwise Excel will return he #VALUE! error.
One direction is missed in the formula:
Use of the SUMPRODUCT function