The SUMPRODUCT function in Google Sheets is a powerful tool that simplifies the process of multiplying corresponding entries in two or more arrays, then summing up the results. This function is particularly useful for various mathematical and statistical operations where conditional logic may be required, making it an indispensable asset for data analysis and manipulation.

Syntax

SUMPRODUCT(array1, [array2], ...)

• array1: The first range or array that contains numeric values to be multiplied.
• array2: (Optional) Another range or array of numeric values to be multiplied with array1.
• …: (Optional) Additional arrays can be included, allowing for multiple products to be summed together.

Example #1

SUMPRODUCT(A1:A3, B1:B3)

This function calculates the sum of the products of corresponding entries in the ranges A1 to A3 and B1 to B3. For instance, if A1=2, A2=3, A3=4 and B1=1, B2=2, B3=3, the result would be (2×1) + (3×2) + (4×3) = 2 + 6 + 12 = 20.

Example #2

SUMPRODUCT(A1:A4, B1:B4, C1:C4)

This function multiplies the entries across three ranges and sums the products. If A1=1, A2=2, A3=3, A4=4; B1=5, B2=6, B3=7, B4=8; C1=1, C2=0, C3=1, C4=0, the calculation would be (1×5×1) + (2×6×0) + (3×7×1) + (4×8×0) = 5 + 0 + 21 + 0 = 26.

Example #3

SUMPRODUCT((A1:A3>1)  (B1:B3))

This function uses a conditional multiplication method. Here, it sums the values in range B1:B3 only where the corresponding values in A1:A3 are greater than 1. If A1=0, A2=2, A3=3 and B1=5, B2=6, B3=7, then the result would be (0 × 5) + (1 × 6) + (1 × 7) = 0 + 6 + 7 = 13.

Error handling

• VALUE!: This error occurs when the ranges or arrays provided are not of equal size.
• N/A: Raised when one of the referenced cells contains a N/A error, interrupting the calculation.
• REF!: This indicates that the cell references are no longer valid, possibly due to deleted rows or columns.

Conclusion

In summary, the SUMPRODUCT function in Google Sheets is a versatile tool that not only performs simple multiplication and addition but also enables complex calculations involving multiple conditions and criteria. Whether for basic arithmetic or advanced data analysis, leveraging this function can greatly enhance your spreadsheet capabilities.