The SUMPRODUCT Function:
Basic and Advanced Uses

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

The ‘SUMPRODUCT’ function is mostly known for calculating weighted averages in a much simpler way.

But the truth is, this function is a lot more powerful and useful than that.

Before ‘SUMIFS’, ‘AVERAGEIFS’, and ‘COUNTIFS’ came into being, there already was the SUMPRODUCT.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get to know more about the ‘SUMPRODUCT’ function and its uses in this tutorial.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

The SUMPRODUCT Function

As you’ve noticed from the name of the function itself, it involves two operations — multiplication and addition.

Technically, the ‘SUMPRODUCT’ function deals with arrays (continuous range of cells) by multiplying the values in them and returning the sum of the products.

Here’s the syntax:

The ‘SUMPRODUCT’ Syntax

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

The parameters of this function are arrays, hence ‘array1’, ‘array2’, etc. The values within the arrays are multiplied and the products added.

In cases when there’s only one (1) array entered, the function would automatically add the values.

Although the ‘SUMPRODUCT’ function deals with arrays, a normal array syntax isn’t required.

That means once you’re done entering the ranges, you don’t need to press Ctrl + Shift + Enter. Pressing ‘Enter’, like entering a non-array formula, would suffice.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s apply the syntax in a real problem.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Basic Use of SUMPRODUCT

As mentioned earlier, the ‘SUMPRODUCT’ multiplies the values of an array to the values of another array and outputs a sum of their products.

A perfect task that needs such a function is calculating the total sales.

Let’s assume you’re running a shirt business in a certain country where you have three (3) branches.

You have three types of products and you distributed 10 pieces of each to your branches. At the end of the day, you have the total pieces sold as well as their prices.

overview for the shirt business sales exercise

Now, you want to know the total sales of your business for the day.

Without knowing the ‘SUMPRODUCT’ function, you will probably multiple each value under ‘Pcs. Sold’ with their corresponding ‘Price/Pc.’.

Then, you will add their products to get the total sales.

manually computing the total sales results to $995

However, the ‘SUMPRODUCT’ can do that thrice as fast with half the effort.

If you recall, the syntax of ‘SUMPRODUCT’ is =SUMPRODUCT(array1, [array2], [array3], …).

All you have to do is supply the arrays — the range of cells on the parameters:

=SUMPRODUCT(D3:D11,E3:11)

‘D3:D11’ represents ‘Pcs. Sold’ while ‘E3:E11’ represents the ‘Price/Pc.’.

Once you have entered the formula and highlighted the ranges, press ‘Enter’.

the total sales is $995 using the sumproduct function

As you can see, what the function did was take the first number from the first array — ‘Pcs. Sold’ — and multiplied it with the first number of the second array — ‘Price/Pc.’. This went on until all the values are multiplied with their pair on the other array.

After that, the products are added and you get the result. Easy, right? 😊

Kasper Langmann, Co-founder of Spreadsheeto

Now that you got the hang of it, let’s explore the other uses of ‘SUMPRODUCT’.

Array Comparison

The ‘SUMPRODUCT’ function is one of those functions that’s more than what meets the eye.

It can do more than its description — it can compare two or more arrays.

Let’s assume you have the data for the pieces sold yesterday and you wanted to compare it with today’s data.

data overview for shirt business sales exercise with yesterday data

What you wanted to know is how many products have more sales today than yesterday.

Clearly, this calls for a comparison of data — ‘Pcs. Sold Yesterday’ (D3:D11) versus ‘Pcs. Sold’ (E3:E:11).

To do this manually, you’ll have to compare each set and keep score.

But if you want to use the ‘SUMPRODUCT’ function, you’ll have to improvise a bit:

=SUMPRODUCT(–(E3:E11>D3:D11))

No need to panic. 😅 We’ll explain the ‘variation’ slowly.

Kasper Langmann, Co-founder of Spreadsheeto

First off, the double ‘–’  (negative) you see is called a ‘double unary operator’. Basically, you’re turning the argument into a logical test.

What the double unary does is coerce the TRUE/FALSE into the numeric values one (1) and zero (0).

By the way, any non-numeric items in the arrays referenced in a ‘SUMPRODUCT’ equation are seen as zeros.

Then, the greater than (>) operator completes what we’re trying to tell Excel to do.

Here’s the human speech equivalent of what the formula above says:

If the first value in the ‘Pcs. Sold’ array is greater than the first value in ‘Pcs. Sold Yesterday’, return 1; if not, return 0. Do this for all the values in the arrays. Afterward, add all the values and return the sum.

Here’s the result in Excel:

3 times when pieces sold today for a product exceeded yesterday

As shown above, there are three instances when the sales today are higher than yesterday.

But what if you only want to see the results for Product 1?

It’s actually easier than it sounds. All you need to do is add the third column — B3:B11 — in the argument.

=SUMPRODUCT(–(E3:E11>D3:D11), –(B3:B11=“Product 1”))

The addition of ‘–(B3:B11=”Product 1″)’ to the formula simply tells Excel to compare the data in the ‘Product’ column. If ‘Product 1’ is in the cell, return 1; if not, return 0.

only 1 instance when pieces sold today is greater than yesterday

As can be seen above, there’s only 1 instance when today’s sales of ‘Product 1’ is higher than yesterday.

Under the hood (select the range and press F9), you can see TRUE or FALSE values:

under the hood of a sumproduct formula

Because of the double unary operator, TRUE and FALSE turns into ones and zeros:

=SUMPRODUCT(–({0;0;0;0;0;1;1;1;0}), –({1;0;0;1;0;0;1;0;0}))

Then, the values are multiplied against their corresponding pair (1st on the left to the 1st on the right, 2nd on the left to the 2nd on the right, etc.)

=0+0+0+0+0+0+1+0+0

The products are then added to come up with the result, which is 1.

=1

If you like to add more arrays, you can but only up to its limit of 255 arrays.

Conditional Sums and Counts

The earlier versions of Excel (2003 and older) don’t have some of the Excel functions today like ‘SUMIFS’, ‘COUNTIFS’, AND ‘AVERAGEIFS’.

During those times, the ‘SUMPRODUCT’ function handled their tasks very well.

Even in our time, the ‘SUMPRODUCT’ is useful for conditional sums and counts especially in using the ‘OR’ and ‘AND’ logic.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s say you want to do two things:

  • Count the instances ‘Product 2’ has sold 8 pieces in today’s data
  • Sum of all the sales of ‘Product 2’ across the three branches

Conditional counting using ‘SUMPRODUCT’ is easy and closely resembles what we did earlier during the “Array Comparison” topic.

To start, construct the formula to count all the instances where “Product 2” was mentioned:

=SUMPRODUCT(–(B3:B11=”Product 2″))

That narrows it down to 3.

there are 3 mentions of product 2

Now, we will add a second array — the E3:E11 range — and return 1 when the value of a cell is equals to 8.

=SUMPRODUCT(–(B3:B11=”Product 2″), –(E3:E11=8))

Of course, there’s only 1 instance when ‘Product 2’ sold 8 pieces. It was from ‘Branch C’.

only 1 instance when a product 2 has sold 8 pieces

Now, let’s try finding the sum of all the sales of ‘Product 2’.

We already know how to select all the ‘Product 2’ in the set. 

Now, all you have to do is add the arrays required to get the sales (‘Pcs. Sold’ and ‘Price/Pc.’).

=SUMPRODUCT(–(B3:B11=”Product 2″), E3:E11, F3:F11)

The total sales for ‘Product 2’ is 285 as shown below side by side with the manual computation:

the total sales of product 2 is 285

But what if you want to know the total sales of both ‘Product 2’ and ‘Product 3’?

What you should do is add the ‘OR’ operator, which is the plus sign (+), to the formula:

=SUMPRODUCT(–(B3:B11=”Product 2″) + –(B3:B11=”Product 3″), E3:E11, F3:F11)

What the plus sign did was tell Excel to add the product of the values when the value in a cell in B3:B11 is ‘Product 2’ or ‘Produt 3’.

the total sales of product 2 and 3 is $627

As shown above, the total sales for both ‘Product 2’ and ‘Product 3’ are 627. You can also see the manual computation at the side.

Lastly, what if you only wanted to count the total sales for both products in ‘Branch C’?

The answer is to specify in the formula that the sum we want is from ‘Branch C’ in the A3:A11 range.

The ‘AND’ operator, an asterisk (*), will do the trick.

=SUMPRODUCT(–(A3:A11=”Branch C”) * (–(B3:B11=”Product 2″) + –(B3:B11=”Product 3″)), E3:E11, F3:F11)

What it says now is add the product of the values when the value in a cell in A3:A11 is ‘Branch C’ and the value in a cell in B3:B11 is ‘Product 2’ or ‘Product 3’.

260 is the total sales of products 2 and 3 in branch c

The total is 260 as shown above.

With the use of the ‘AND’ and ‘OR’ operator, you can add as many logical arguments as you need.

Weighted Average Calculation

A weighted average is an average where the predetermined weight or importance of each value in a set is taken into account.

It differs from a ‘normal’ average in a way that a ‘normal average’ has values with equal count during the the computation.

Kasper Langmann, Co-founder of Spreadsheeto

Lots of Excel users, like teachers, associate the ‘SUMPRODUCT’ function with the weighted average.

Because why not? The ‘SUMPRODUCT’ is a perfect function to simplify the calculation of finding the weighted average.

To illustrate, here’s the formula of the weighted average:

v = value

w = weight

weighted average = [(v1 x w1)+(v2 x w2)+(v3 x w3)…]/w1+w2+w3

Notice the first half of the formula? 

Yes, it’s exactly how the ‘SUMPRODUCT’ computation works.

Using the ‘SUMPRODUCT’ (and the ‘SUM’) function simplifies this formula:

weighted average =SUMPRODUCT(value,weight)]/SUM(weight)

Let’s pretend that you’re having a staff evaluation for a certain branch in your shirt business.

data overview for branch A staff evaluation exercise

To come up with the weighted average, you’ll have to consider the weight of each duty.

Applying the formula above, we’ll arrive at:

weighted average =SUMPRODUCT(grade,weight)]/SUM(weight)

Here’s the result side by side with a manual computation:

84.56 is the weighted average

Usage with Other Functions

The ‘SUMPRODUCT’ function is also used alongside other functions like ‘LEN’, ‘ISTEXT’, ‘ISNUMBER’, ‘ISBLANK’, and others.

Let’s use the ‘LEN’ function this time.

The ‘LEN’ function is a simple text function that returns the length of a string in a cell.

Kasper Langmann, Co-founder of Spreadsheeto

The syntax of ‘LEN’ is =LEN(text).

For the fun of it, let’s say you want to know how many characters there are in ‘Duty 10’:

the total character count of the word "duty 10" is 7

But if you wanted to know the total characters of the words in A3 (‘Duty 1’) to A12 (‘Duty 10’), you can’t simply put an array as a parameter.

That means =LEN(A3:A12) will result in an error, specifically ‘#VALUE!’.

However, if the ‘LEN’ function would ride a ‘SUMPRODUCT’ formula, you’ll get the result since the ‘SUMPRODUCT’ function takes on arrays as parameters.

Let’s combine them:

=SUMPRODUCT(LEN(A3:A12))

Congratulations! You now have a result:

the total character count of A3 to A12 is 61

Wrapping things up…

You can say that the ‘SUMPRODUCT’ is one of the most useful and ancient functions in Excel. There are several ways you can use this function, from basic to advanced.

It would certainly pay off to learn how to use this awesome function.

Certainly, it becomes a bit complicated when used in an advanced way. But in the end, with more practice, you’ll be able to master the ‘SUMPRODUCT’ function and use it however you like.

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-02T12:54:20+00:00