There are two more advanced summing functions that we’ll discuss – SUMPRODUCT and SUMSQ.
SUMPRODUCT multiplies individual components in arrays together and then returns the sum of those components. The syntax is as follows:
SUMPRODUCT(array1, [array2], [array3],…)
array1: the first array whose components will be multiplied then added
array2, array 3, etc.: (optional) additional arrays whose components will be multiplied then added
The first argument, array1, is the only one that is required. If only one argument is entered, the function will simply return the sum of the array components without doing any multiplication. To fully utilize this function, enter multiple arrays (up to 255). The function will multiply the individual array components and sum them.
SUMSQ returns the sum of squares of whatever numbers are entered as arguments. Its syntax is:
SUMSQ(number, [number2], [number3],…)
number1: first number for which the sum of squares is calculated
number2, number3, etc.: (optional) additional numbers for which the sum of squares will be calculated
Again, only one argument is required. In that case, the function will return the value of that number squared. As you add additional values, they will be squared, and then summed together to return a single value.
Worksheet 03o contains an example that uses both functions. The spreadsheet contains XYZ coordinates for two vectors. We can calculate the angle between the vectors by using the equation:
This equation uses the dot product of the vectors to calculate the angle. The SUMPRODUCT function is essentially a dot product, so this function can be used to calculate the numerator. Enter =SUMPRODUCT( into cell C11. Select the XYZ coordinates for vector 1, then type a comma. Select the coordinates for vector 2. The resulting formula is:
=SUMPRODUCT(C6:E6,C7:E7)
The denominator will be the product of the magnitude of the two vectors. To find the magnitude, take the square root of the sum of squares of the vector components. Enter in the formula:
=SQRT(SUMSQ(C6:E6))*SQRT(SUMSQ(C7:E7))
Finally, to calculate the angle in degrees, use the inverse cosine function, ACOS, inputting the numerator and denominator that was just calculated. To get the angle in degrees, nest the ACOS function inside of the DEGREES function:
=DEGREES(ACOS(C11/C12))
This formula will return an angle of 39.1 degrees.
