Fabulous Examples of Excel Functions:
SUM, SUMPRODUCT, and SUMSQ

Author: Patricia Lynn

Microsoft Excel has many built-in functions to perform calculations. This tutorial covers three functions for addition: SUM, SUMPRODUCT, and SUMSQ.

Tutorials for other Excel functions are listed on the right sidebar menu.

For an introduction to Excel math, see the separate tutorial Excel Math Basics.

An Excel function always begins with an equal = sign unless the function is imbedded inside another function. Also, it is a good idea to lock any cell containing a formula or function, and then protect the worksheet to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy.

Click on the link below to go directly to the function listed.

• SUM
• SUMPRODUCT
• SUMSQ

SUM

If you want to total the contents of cells G3 through G28, you can use a math formula such as =G3+G4+G5 … all the way to G28. But there is a much better way!

In an Excel worksheet, cells G3 through G28 are referenced as G3:G28. The Excel SUM function provides an easy way to add these cell contents: =SUM(G3:G28). The syntax for the SUM function is:

SUM(number1,number2,number3, ... )

… where number1, number2, etc. are from 1 to 255 arguments, for which you want the sum. The arguments can contain cell references, formulas, and functions.

Here are some other examples of the SUM function:

• =SUM(2,1,3)   This Excel function adds the numbers 2, 1, and 3 and returns a value of 6
• =SUM(6/3,4*2,33)   This Excel function adds the numbers 2 (6÷3), 8 (4x2), and 33 and returns a value of 43
• =SUM(B6:M6,Q6)   This Excel function adds the contents of B6 through M6, plus Q6
• =SUM(A2:A23)/150   This Excel formula adds the contents in A2 through A23 as directed by the SUM function, and then divides the result by 150
• =10*SUM(A1:D1)   This Excel formula adds the contents of cells A1 through D1 as directed by the SUM function, and then multiplies the result by 10
• =SUM(A2:A14,C4:C29)   This Excel function adds together the sum of the contents of A2 through A14, and the sum of the contents of C4 through C29

Examples that contain other functions:

• =SUM(PRODUCT(8,9),100)   This Excel function adds 72 (8x9) and 100, and returns a value of 172
• =SUM(PRODUCT(A3,A4),B9)   This Excel function is like the previous example, but contains cell references instead of numbers
• =SUM(COUNTA(A1:A10),COUNTA(B1:B10))   This Excel function adds together the number of non-blank cells in A1 through A10 to the number of non-blank cells in B1 through B10

SUMPRODUCT

The SUMPRODUCT function in Microsoft Excel calculates the sum of products. (A product is the result of a multiplication.) This function provides for the elements of two or more arrays to be multiplied together, and the resulting products summed. The syntax for SUMPRODUCT is:

SUMPRODUCT(array1,array2,array3 ... )

Up to 255 arrays may be included, and all arrays must have the same dimension (same number of rows and columns) or Excel will return a #VALUE! error. An array is a contiguous block of cells, referenced by its beginning (upper left) and ending (lower right) cell names.

The function =SUMPRODUCT(A2:B4,D2:E4) has been entered in cell C2 in the worksheet below. It references 2 arrays - each 2 columns wide by 3 columns high.

Below the arrays and function, beginning in Row 7, we show the calculations Excel is performing when executing this function. First Excel multiplies the corresponding components from the two arrays, and we show their products in Column C. Then Excel adds together the products and returns a value of 66.

SUMSQ

The SUMSQ function returns the "sum of the squares" of the numbers specified in the function. Do not confuse the "square" of a number with the "square root" of a number. The "square" of a number is the number multiplied by itself.

The syntax of the SUMSQ function is:

SUMSQ(number1,number2, ... )

… where number1, number2, ... represent from 1 to 255 arguments you want squared, and summed. The arguments can contain numbers, cell references, formulas, or other Excel functions.

For example, the value of =SUMSQ(3,5) is 34. First, Excel squares the arguments (3x3=9, 5x5=25), and then adds them together (9+25=34).

Most examples of this function show numbers as the arguments, as we did above. However, in real life, the arguments usually contain cell references, formulas, or functions.

Review the four examples of SUMSQ shown in the image below. The actual functions reside in column A, but we display the function text in column B.

Example 1: In this example, the values in cells C1 and D1 are squared (4x4=16, 3x3=9), then added together (16+9=25).

Example 2: Here we illustrate that either argument can contain a formula. Excel sums the square of C1 (4x4=16) and the square of D1+E1 (3+2=5 and 5x5=25,) and returns a value of 41 (16+25)

Example 3: In the third example, the function is being used to square a number that is first derived from a formula. Here, we first find the sum of C1 and D1, which is 7, and then square the answer to arrive at 49. In this example, we provide only one argument to the function, but it works well for this purpose.

Example 4: In the last example, the function sums the squares of the contents of contiguous cells. Contiguous cells can be referenced by the first cell and last cell separated by a colon. Excel sums the squares of C1, D1, and E1 (16+9+4=29), and returns a value of 29.

Back to Top

We hope this article has been helpful. Cheers!

Related tutorials:
►  Linking Excel Worksheets
►  Excel Keyboard Shortcuts

   Back to Top