## How to use excel SUMPRODUCT function

#### Syntax

**SUMPRODUCT**(**array1**,array2,array3, ...)

*Multiplies corresponding components in the given arrays, and returns the sum of those products.*

What does that mean? I´ll demonstrate with a few examples.

#### Example 1 - The basics

**Formula in cell B7:**

**Step 1 - Multiplying corresponding components in the given arrays**

The first array is in cell range B2:B4 and the second array is in cell range C2:C4.

B2:B4*C2:C4

becomes

{1;2;3} * {4;5;6}

becomes

{1*4; 2*5; 3*6}

and returns {4; 10; 18}. The same calculations are done in column D and shown in column E, see above picture.

**Step 2 - Return the sum of those products**

{4; 10; 18}

becomes

4 + 10 + 18

and returns 32 in cell B7. The same calculation is done E5, the sum of the products in cell range E2:E4 is calculated in cell E5. See above picture.

Now you know the basics. Let´s move on to something more interesting!

### Example 2 - Use a logical expression

**Formula in cell G4:**

**Step 1 - Multiplying corresponding components in the given arrays**

There is only one array in this formula but something else is distorting the picture. A comparison operator (equal sign) and a second cell value (G2) or a comparison value. With these we have now built a logical expression. This means that the value in cell G2 is compared to all the values in cell range B2:B6 (not case sensitive).

B2:B6=$G$2

becomes

{"Alaska";"California";"Arizona";"California";"Colorado"}="California"

and returns

{FALSE;TRUE;FALSE;TRUE;FALSE}.

They are all boolean values and excel can´t sum these values. We have to convert the values to numerical values. There are a few options, you can:

- Add a zero - (B2:B6=$G$2)+0
- Multiply with 1 - (B2:B6=$G$2)*1
- Double negative signs --(B2:B6=$G$2)

They all convert boolean values to numerical values.

--( {FALSE;TRUE;FALSE;TRUE;FALSE})

becomes

{0;1;0;1;0}

**Step 2 - Return the sum of those products**

{0;1;0;1;0}

becomes

0 + 1 + 0 + 1 + 0

and returns 2 in cell G4. There are two cells containing the value "California" in cell range B2:B6. You accomplish the same thing using the countif function or count multiple values in different columns using the countifs function. In fact, you can count entire records in a table.

#### Example 3 - Use multiple logical expressions

**Formula in cell D10:**

This formula contains three arrays, you can use up to 30 arrays. You can make the formula somewhat shorter:

This allows you to have a lot more expressions, if you like. It is only the available computer memory that is the limit. The formula looks like an array formula but don´t be fooled, you are not required to enter it as an array formula.

**Step 1 - Multiplying corresponding components in the given arrays**

(B2:B8=B10)*(C2:C8=C10)*D2:D8

becomes

({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})*({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE})*{10; 20; 40; 10; 20; 30; 10}

becomes

{0;0;0;1;0;1;0}*{10; 20; 40; 10; 20; 30; 10}

and returns

{0; 0; 0; 10; 0; 30; 0}

**Step 2 - Return the sum of those products**

{0; 0; 0; 10; 0; 30; 0}

becomes

10 +30

and returns 40 in cell D10.

#### Example 4 - Use multiple logical expressions and mathematical operators

Mathematical operators between arrays allows you to do more complicated calculations, like this:

**Formula in cell D10:**

These expressions check if California is found in cell range B2:B8 or Las Vegas is found in cell range C2:C8. They are found in row 3, 5,7 and 8. The sumproduct function sums the corresponding values in column D and returns 70 in cell D10. 20+10+30+10 equals 70.

#### Download excel *.xlsx file

### Category: Functions

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel, Functions

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Comments(10) Filed in category: Excel, Functions

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Comments(8) Filed in category: Excel, Functions, Mod, Quotient

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Comments(4) Filed in category: Excel, Functions, Transpose

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Comments(4) Filed in category: Excel, Functions, Textjoin

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]

Comments(4) Filed in category: Excel, Functions, Mmult

### 2 Responses to “How to use excel SUMPRODUCT function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi Oscar,

While reading this article "How to use excel SUMPRODUCT function" I detected what I think is a minor error.

In example 3 (The California-Los angeles example), you say that the formula transaltes to:

=({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})*({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE})*{10; 20; 40; 10; 20; 30; 10}

but i think that the second set of boolean values is not correct and should be:

=({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})*({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE})*{10; 20; 40; 10; 20; 30; 10}

Is that correct or I understood incorrectly?

Thanks in advance for your support with this blog.

Regards

Arturo

Arturo,

thanks!!