Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)™. Register today!

SUMPRODUCT

Multiplies the components of a given array and then returns the sum of the products

What is the SUMPRODUCT Excel Function?

The SUMPRODUCT Excel Function is categorized under Math and Trigonometry functions. The function will multiply the corresponding components of a given array and then return the sum of the products. It is used to calculate weighted average.

As a financial analyst, SUMPRODUCT is a very handy function as it can handle arrays in different ways and help in comparing data in two or more than two ranges. It also helps in calculating data with multiple criteria.

 

Sumproduct Formula

=SUMPRODUCT(array1,[array2],[array3],…)

 

The SUMPRODUCT function uses the following arguments:

  1. Array1 (required argument) – It is the first array or range that we wish to multiply and subsequently add.
  2. Array2, Array 3 (optional argument) – d It is the second (or third) array or range that we wish to multiply and subsequently add.

 

How to use the SUMPRODUCT Excel function?

To understand the uses of the SUMPRODUCT function, let’s consider a few examples:

 

Example 1

Suppose we are given the following data:

 

SUMPRODUCT Function

 

We wish to find out the total sales for the West region. The formula to be used is:

 

SUMPRODUCT Function - Example 1

 

The reason we provided the double negative (- -) is to force Excel to convert the TRUE and FALSE values into 1’s and 0’s.

 

SUMPRODUCT Excel Function - Example 1a

 

A virtual representation of the two arrays as first processed by SUMPRODUCT is below:

 

SUMPRODUCT Function - Example 1b

 

The first array contains the TRUE/FALSE values that result from the expression C4:C12=”WEST”, and the second array is the contents of D4:D12.  Each item in the first array will be multiplied by the corresponding item in the second array.

However, in the current state, the result of SUMPRODUCT will be zero as the TRUE and FALSE values will be treated as zeroes. We need the items in array1 to be numeric, so we need to convert them into 1’s and 0’s. It is where the double negative comes in as it will treat TRUE as 1 and FALSE as 0.

 

SUMPRODUCT Function - Example 1c

 

We get the result below:

 

SUMPRODUCT Function - Example 1d

 

Example 2: Weighted Average

One common usage of the SUMPRODUCT function is to calculate a weighted average where each value is assigned a weight. Suppose we are given the following data:

 

SUMPRODUCT Function - Example 2

 

Assume the values are given in cells C2:C8 and the weights in cell D2:D8. The weighted average SUMPRODUCT formula is:

 

SUMPRODUCT Function - Example 2a

 

We get the result below:

 

SUMPRODUCT Function - Example 2b

 

Notes about the SUMPRODUCT Excel Function 

  1. It is sued to calculate a weighted average
  2. #VALUE error – Occurs when the arrays provided do not share the same dimensions.
  3. The SUMPRODUCT function will treat non-numeric entries that are part of the array as zeroes.
  4. SUMPRODUCT accepts up to 255 arguments in Excel 2016, Excel 2013, Excel 2010, and Excel 2007, and 30 in earlier Excel versions.
  5. Logical tests inside arrays will create TRUE and FALSE values. In most cases, it is advisable to convert them to 1’s and 0’s.
  6. SUMPRODUCT can often use the result of other functions directly. It is commonly used with LEN, or with other functions such as ISBLANK, ISTEXT, VLOOKUP, etc. Few examples are:
  • Two-way lookup in Excel – SUMPRODUCT formula helps look up a value at the intersection of a given row and column.
  • Count characters in Excel – SUMPRODUCT can be used to count the total or specific characters in a range.
  • SUMPRODUCT for counting words in Excel – The function can be used to get the total number of words in a defined range or count only specific words.
  • How to count distinct values – It can be used to find out how many different values appear in a range (unique plus first duplicate occurrences).
  • Count duplicates and unique values – The SUMPRODUCT and COUNTIF functions help count duplicates and unique values in a column or between two columns.
  • SUMPRODUCT and MONTH can be used to get a sum of values for a given month.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!