**The SUMPRODUCT Function:**

Basic and Advanced Uses

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.

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.*

**Table of Content**

**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, like entering a non-array formula, would suffice.‘Enter’

Let’s apply the syntax in a real problem.

**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.

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.

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’**.

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? 😊

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.

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.

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:

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**.

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*:

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 countsespecially in using the‘OR’and‘AND’logic.

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.

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’.

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:

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’*.

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’*.

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.

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.

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:

**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.

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’:

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:

**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.