Fabulous Examples of Excel Functions:
SUM and SUMPRODUCT

This Keynote Support tutorial teaches the Microsoft Excel functions of SUM and SUMPRODUCT. It contains helpful examples from a sample spreadsheet to show how these functions can be used in a variety of formulas and in combination with other functions.

Author: Keynote Support

A related function, SUMSQ, is covered in a separate tutorial Microsoft Excel Functions: SUMSQ.

If you are new to Excel, we recommend the video-based course Excel 2010 Tutorial for Beginners. We have this course (over 9 hours of hands-on lessons) and find it well done and a good value. An advanced Excel course is also available, along with free, online video tutorials.

Before we begin, remember that every Excel function must begin with an equal = sign unless the function is imbedded inside of another function. Now - click on the link to go directly to that function:   SUM     SUMPRODUCT

SUM

If we want to add the contents of two cells together, we can use a math formula in Excel to do so quite easily; i.e. =G3+G4. But if we want to add the contents of a lengthy column of cells, for instance, the SUM function comes in handy!

Let's assume we want to total the contents of cells G3 through G28. Instead of writing the math formula =G3+G4+G5 … all the way to G28, simply use the SUM function and enter =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. For information about COUNTA and other counting functions, see Microsoft Excel: The COUNTing Functions

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. The first array extends from A2 to B4 as shown on the worksheet below. The second array extends from cells D2 to E4.

Beginning in Row 7, we show the calculations that Excel performs when executing this function. First Excel multiplies the corresponding components from the two arrays, and we show their products in Column C. For example, Excel multiplies the first cells in each array: A2 x D2, then the second cells, and so on. Then Excel adds together the products and returns a value of 66.

In Conclusion

Also, we recommend locking any cell containing a function or formula, and then protecting the worksheet, to prevent accidental erasure. For directions on how to lock cells and protect worksheets, see the tutorial Excel Made Easy: A Beginner's Guide - "Locking Cells and Protecting Worksheets."

We hope this article has been helpful. Cheers!

Return to the top of Fabulous Examples of Excel Functions: SUM and SUMPRODUCT