*The tutorial explains the basic and advanced uses of the SUMPRODUCT function in Excel. You will find a number of formula examples to compare arrays, conditionally sum and count cells with multiple criteria, calculate a weighted average and more. *

When you hear the name of SUMPRODUCT for the first time, it may sound like some useless formula that performs an ordinary sum of the products operation. But that definition does not show even a tiny fraction of what Excel SUMPRODUCT is capable of.

In fact, SUMPRODUCT is a remarkably versatile function with many uses. Due to its unique ability to handle arrays in smart and elegant ways, SUMPRODUCT is extremely useful, if not indispensable, when it comes to comparing data in two or more ranges and calculating data with multiple criteria. The following examples will reveal the full power of SUMPRODUCT and its effectiveness will become crystal clear.

## Excel SUMPRODUCT function - syntax and uses

Technically, the SUMPRODUCT function in Excel multiplies the numbers in the specified arrays, and returns the sum of those products.

The syntax of the SUMPRODUCT function is simple and straightforward:

Where array1, array2, etc. are continuous ranges of cells or arrays whose elements you want to multiply, and then add.

The minimum number of arrays is 1. In this case, a SUMPRODUCT formula simply adds up all of the array elements and returns the sum.

The maximum number of arrays is 255 in Excel 365 - 2007, and 30 in earlier Excel versions.

Although SUMPRODUCT works with arrays, it does not require using the array shortcut. You compete a SUMPRODUCT formula in a usual way by pressing the Enter key.

Notes:

- All arrays in a SUMPRODUCT formula must have the same number of rows and columns, otherwise you get the #VALUE! error.
- If any array argument contains non-numeric values, they will be treated as zeros.
- If an array is a logical test, it results in TRUE and FALSE values. In most cases, you'd need to convert them to 1 and 0 by using the double unary operator (--) . Please see the SUMPRODUCT with multiple criteria example for more details.
- SUMPRODUCT does not support wildcard characters.

### Basic usage of SUMPRODUCT in Excel

To gain a general understanding of how the Excel SUMPRODUCT function works, consider the following example.

Supposing you have quantity in cells A2:A4, prices in cells B2:B4, and you wish to find out the total. If you were doing a school math test, you would multiply the quantity by price for each item, and then add up the subtotals. In Microsoft Excel, you can get the result with a single SUMPRODUCT formula:

`=SUMPRODUCT(A2:A4,B2:B4)`

The following screenshots shows it in action:

Here is what's going on under the hood in terms of math:

- The formula takes the 1
^{st}number in the 1^{st}array and multiplies it by the 1^{st}number in the 2^{nd}array, then takes the 2^{nd}number in the 1^{st}array and multiplies it by the 2^{nd}number in the 2^{nd}array, and so on. - When all of the array elements are multiplied, the formula adds up the products and returns the sum.

In other words, our SUMPRODUCT formula performs the following mathematical operations:

`=A2*B2 + A3*B3 + A4*B4`

Just think how much time it could save you if your table contained not 3 rows of data, but 3 hundred or 3 thousand rows!

Tip. If you want to only multiply the numbers in each row without adding up the products, then use one of the formulas to multiply columns in Excel.

## How to use SUMPRODUCT in Excel - formula examples

Multiplying two or more ranges together and then summing the products is the simplest and most obvious usage of SUBTOTAL in Excel, though not by far the only one. The real beauty of the Excel SUMPRODUCT function is that it can do far more than its stated purpose. Further on in this tutorial, you will find a handful of formulas that demonstrate more advanced and exciting uses, so please keep reading.

### SUMPRODUCT with multiple criteria

Usually in Microsoft Excel, there is more than one way to accomplish the same task. But when it comes to comparing two or more arrays, especially with multiple criteria, SUMPRODUCT is the most effective, if not the only, solution. Well, either SUMPRODUCT or array formula.

Assuming you have a list of items in column A, planned sale figures in column B, and actual sales in column C. Your goal is to find out how many items have made less sales than planned. For this, use one of the following variations of the SUMPRODUCT formula:

`=SUMPRODUCT(--(C2:C10<B2:B10))`

or

`=SUMPRODUCT((C2:C10<B2:B10)*1)`

Where C2:C10 are real sales and B2:B10 are planned sales.

But what if you had more than one condition? Let's say, you want to count how many times *Apples* performed worse than planned. The solution is to add one more criterion to the SUMPRODUCT formula:

`=SUMPRODUCT(--(C2:C10<B2:B10), --(A2:A10="apples"))`

Or, you can use the following syntax:

`=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))`

And now, let's take a minute and understand what the above formulas are actually doing. I believe it is a worthy time investment because many other SUMPRODUCT formulas work with the same logic.

#### How SUMPRODUCT formula with one condition works

For starters, let's break down a simpler formula that compares numbers in 2 columns row-by-row, and tells us how many times column C is less than column B:

`=SUMPRODUCT(--(C2:C10<B2:B10))`

If you select the portion (C2:C10<B2:B10) in the formula bar, and press F9 to view the underlying values, you will see the following array:

What we have here is an array of Boolean values TRUE and FALSE, where TRUE means the specified condition is met (i.e. a value in column C is less than a value in column B in the same row), and FALSE signifies the condition is not met.

The double negative (--), which is technically called the **double unary operator**, coerces TRUE and FALSE into ones and zeros: {0;1;0;0;1;0;1;0;0}.

Another way to convert the logical values into the numeric values is multiple the array by 1:

`=SUMPRODUCT((C2:C10<B2:B10)*1)`

Either way, since there is just one array in the SUMPRODUCT formula, it simply adds up 1's in the resulting array and we get the desired count. Easy, isn't it?

#### How SUMPRODUCT formula with multiple conditions works

When an Excel SUMPRODUCT formula contains two or more arrays, it multiplies the elements of all the arrays, and then adds up the results.

As you may remember, we used the following formulas to find out how many times the number of real sales (column C) was less than planned sales (column B) for *Apples* (column A):

`=SUMPRODUCT(--(C2:C10<B2:B10), --(A2:A10="apples"))`

or

`=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))`

The only tech difference between the formulas is the method of coercing TRUE and FALSE into 1 and 0 - by using the double unary or multiplication operation. As the result, we get two arrays of ones and zeros:

The multiplication operation performed by SUMPRODUCT joins them into a single array. And since multiplying by zero always gives zero, 1 appears only when both conditions are met, and consequently only those rows are counted:

### Conditionally count / sum / average cells with multiple criteria

In Excel 2003 and older versions that did not have the so-called IFs functions, one of the most common uses of the SUMPRODUCT function was to conditionally sum or count cells with multiple criteria. Beginning with Excel 2007, Microsoft introduced a series of functions specially designed for such tasks - SUMIFS, COUNTIFS and AVERAGEIFS.

But even in the modern versions of Excel, a SUMPRODUCT formula could be a worthy alternative, for example, to conditionally sum and count cells with the OR logic. Below you will find a few formula examples that demonstrate this ability in action.

#### 1. SUMPRODUCT formula with AND logic

Supposing you have the following dataset, where column A lists the regions, column B - items and column C - sales figures:

What you want is get the count, sum and average of *Apples* sales for the *North* region.

In Excel 2007 and higher, the task can be easily accomplished by using a SUMIFS, COUNTIFS and AVERAGEIFS formula. If you are not looking for easy ways, or if you are still using Excel 2003 or older, you can get the desired result with SUMPRODUCT.

- To
**count***Apples*sales for*North*:`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"))`

or

`=SUMPRODUCT((A2:A12="north")*(B2:B12="apples"))`

- To
**sum***Apples*sales for*North:*`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)`

or

`=SUMPRODUCT((A2:A12="north")*(B2:B12="apples")*C2:C12)`

- To
**average***Apples*sales for*North:*To calculate the average, we simply divide Sum by Count like this:`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12) / SUMPRODUCT( --(A2:A12="north"), --(B2:B12="apples"))`

To add more flexibility to your SUMPRODUCT formulas, you can specify the desired *Region* and *Item* in separate cells, and then reference those cells in your formula like shown in the screenshot below:

**How SUMPRODUCT formula for conditional sum works**

From the previous example, you already know how the Excel SUMPRODUCT formula counts cells with multiple conditions. If you understand that, it will be very easy for you to comprehend the sum logic.

Let me remind you that we used the following formula to sum *Apples* sales in the *North* region:

`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)`

An intermediate result of the above formula are the following 3 arrays:

- In the 1
^{st}array, 1 stands for*North*, and 0 for any other region. - In the 2
^{nd}array, 1 stands for*Apples*, and 0 for any other item. - The 3
^{rd}array contains the sales numbers exactly as they appear in cells C2:C12.

Remembering that multiplying by 0 always gives zero, and multiplying by 1 gives the same number, we get the final array consisting of the sales numbers and zeros - a sales number appears only if the first two arrays have 1 in the same position, i.e. both of the specified conditions are met; zero otherwise:

Adding up the numbers in the above array delivers the desired result - the total of the *Apples* sales in the *North* region.

#### Example 2. SUMPRODUCT formula with OR logic

To conditionally sum or count cells with the OR logic, use the **plus symbol** (+) in between the arrays.

In Excel SUMPRODUCT formulas, as well as in array formulas, the plus symbol acts like the OR operator that instructs Excel to return TRUE if ANY of the conditions in a given expression evaluates to TRUE.

For example, to get the **count** of all *Apples* and *Lemons* sales regardless of the region, use this formula:

`=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"))`

Translated into plain English, the formula reads as follows: *Count cells if B2:B12="apples" OR B2:B12="lemons"*.

To **sum** *Apples* and *Lemons* sales, add one more argument containing the *Sales* range:

`=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"), C2:C12)`

The following screenshot shows a similar formula in action:

#### Example 3. SUMPRODUCT formula with AND as well as OR logic

In many situations, you might need to conditionally count or sum cells with AND logic and OR logic at a time. Even in the latest versions of Excel, the IFs series of functions is not capable of that.

One of the possible solutions is combining two or more functions SUMIFS + SUMIFS or COUNTIFS + COUNTIFS.

Another way is using the Excel SUMPRODUCT function where:

- Asterisk (*) is used as the AND operator.
- Plus symbol (+) is used as the OR operator.

To make things easier to understand, consider the following examples.

To **count** how many times *Apples* and *Lemons* were sold in the *North* region, make a formula with the following logic:

`=Count If ((Region="north") AND ((Item="Apples") OR (Item="Lemons")))`

Upon applying the appropriate SUMPRODUCT syntax, the formula takes the following shape:

`=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons")))`

To **sum ***Apples* and *Lemons* sales in the *North* region, take the above formula and add the *Sales* array with the AND logic:

`=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons"))*C2:C12)`

To make the formulas a bit more compact, you can type the variables in separate cells - *Region* in F1 and *Items* in F2 and H2 - and refer to those cells in your formula:

### SUMPRODUCT formula for weighted average

In one of the previous examples, we discussed a SUMPRODUCT formula for conditional average. Another common usage of SUMPRODUCT in Excel is calculating a weighted average where each value is assigned a certain weight.

The generic SUMPRODUCT weighted average formula is as follows:

*values*,

*weights*) / SUM(

*weights*)

Assuming that values are in cells B2:B7 and weights are in cell C2:C7, the weighted average SUMPRODUCT formula will look like this:

`=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)`

I believe at this point you won't have any difficulties with understanding the formula logic. If someone needs a detailed explanation, please check out the following tutorial: Calculating weighted average in Excel.

### SUMPRODUCT as alternative to array formulas

Even if you are reading this article for informational purposes and the details are likely to fade away in your memory, remember just one key point - the Excel SUMPRODUCT function deals with arrays. And because SUMPRODUCT offers much of the power of array formulas, it can become an easy-to-use replacement for them.

What advantages does this gives to you? Basically, you will be able to manage your formulas an easy way without having to press Ctrl + Shift + Enter every time you are entering a new or editing an existing array formula.

As an example, we can take a simple array formula that counts all characters in a given range:

*range*))}

and turn it into a regular formula:

*range*))

For practice, you can take these Excel array formulas and try to re-write then using the SUMPRODUCT function.

### Excel SUMPRODUCT - advanced formula examples

Now that you know the syntax and logic of the SUMPRODUCT function in Excel, you may want to learn more sophisticated and more powerful formulas where SUMPRODUCT is used in liaison with other Excel functions.

## Practice workbook for download

Excel SUMPRODUCT examples (.xlsx file)

## 243 comments

Hi Alexander - I'm back :) So I need to use SUMPRODUCT for columns CS and EH, but in column B I am trying to select only a few criteria. The above solution you provided didn't work for me so I'm sure I did something incorrectly. I am trying 2 ways to accomplish my goal: 1.) include all the buyer groups in column B that I need or 2.) exclude the 3 buyer groups I don't need from my SUMPRODUCT. The problem is that if I do a very manual workaround to check my results, both of these options seem off.

1.) =SUMPRODUCT((B4:B1086="National")+(B4:B1086="Over_500")+(B4:B1086="Allegiance")*($CS$4:$CS$1086)*(EH$4:EH$1086))

2.) =SUMPRODUCT((B4:B1086"International")*(B4:B1086"Individual")*(B4:B1086"Payer")*($CS$4:$CS$1086)*(EH$4:EH$1086))

Do these formulas above seem valid to you or am I doing something wrong? First one is adding all the buyer groups I need and the second one is excluding the 3 I don't want in my SUMPRODUCT.

Thank you so very much!

The * sign in the SUMPRODUCT formula means “AND”, the + sign means “OR”. Without having your data, it is difficult for me to understand your formulas. I assume that all OR conditions in the first formula should be enclosed in brackets.

=SUMPRODUCT(((B4:B1086="National")+(B4:B1086="Over_500")+(B4:B1086="Allegiance"))*($CS$4:$CS$1086)*(EH$4:EH$1086))

Hello Daria!

In the second formula, it is impossible to fulfill the first 3 conditions at the same time according to the logic AND. So replace * with +.

=SUMPRODUCT(((B4:B1086<>"International")+(B4:B1086<>"Individual")+(B4:B1086<>"Payer"))*($CS$4:$CS$1086)*(EH$4:EH$1086))

I recommend reading these guides: IF OR AND formula in Excel and Using logical functions in Excel: AND, OR, XOR and NOT

Hi Alexander - if I'm trying to use SUMPRODUCT, but for my first array I only need certain rows, for example 10-15 named "individual" and then rows 25-30 names International", how can I combine SUMIFS with SUMPRODUCT or do you know of a better way? Pretty much I need to exclude some rows based on their title to use the SUMPRODUCT. Thank you!

Hello Daria!

If I understand your question correctly, add additional conditions to the SUMPRODUCT formula. For example, for your task:

(ROW(D1:D100)>=10)*(ROW(D1:D100)<=15)*(A1:A100="Individual")

Thank you, I will try that!

I have a reporting pack that enables users to select the length of time period that they want to look at, so the sum formula includes an indirect and offset function as well.

=SUMPRODUCT((OFFSET(INDIRECT(the latest column,0),0,VLOOKUP(the number of columns to count back)))*(Data!$A:$A=a helper column linking product, market and measure))) - however, I'm just getting REF# and I'm not sure why.

The backend data is a bunch of products, measures and markets, but I've created a helper column to enable to adding up of products of the same brand, so the helper column gives Brand Market Measure, which is what is being looked up in the criteria.

If anyone could help, I'd massively appreciate it!

Hello Caroline!

If I understand your question correctly, you are trying to use named ranges. However, your named ranges do not follow the rules that you can read here: Excel name rules.

Hi how do I sum the amount of all with the same INV but they are in different columns. In the example below I wanted to add all the amount in INV for Col A and D. This is just part of a big sheet like this could go on with having repeating INV in every column

For ex.

A B C D

INV AMOUNT INV AMOUNT

3012 $50000 1456 $25000

1235 $12000 3012 23555

Hello Myra!

To find the sum of a condition in multiple columns, you can use multiple SUMIF functions. For example, for the range A1:F4:

=SUMIF(A1:A4,1235,B1:B4)+SUMIF(C1:C4,1235,D1:D4)+SUMIF(E1:E4,1235,F1:F4)

You can also extract data from individual columns by using the CHOOSECOLS function.

You can use the SEQUENCE function to specify even columns (AMOUNT) and odd columns (INV).

Use the TOCOL function to convert all odd and all even columns into two columns.

In this way, you will be converting your range of values into two columns - INV and AMOUNT.

Sum the values in the second column TOTAL if the value in the first column matches the criteria INV. To do this, use SUMPRODUCT formula.

=SUMPRODUCT((TOCOL(CHOOSECOLS(A1:F4, SEQUENCE(COLUMNS(A1:F4)/2,1,1,2)))=1235) * TOCOL(CHOOSECOLS(A1:F4,SEQUENCE(COLUMNS(A1:F4)/2,1,2,2))))

could you pls solve me this , this is data sourse

Red Light Green Light Yellow Light Month

01/01/2024 4 5 7 Jan

05/01/2024 6 9 4 Jan

01/03/2024 7 3 4 Mar

this just a sample of a big data, the result should be:

Red Light Green Light Yellow Light Light

Jan (sum of red light for Jan) (sum of Green light for Jan) (sum of Yellow light for Jan) (sum of light for Jan based on partial text)

Mar

How to solve this pls

Hi! To find the sum of the values in a column by condition for a month, you can use SUMIF or SUMIFS formula. You can find the examples and detailed instructions here: How to use SUMIF function in Excel and Excel SUMIFS and SUMIF with multiple criteria. For example:

=SUMIF(E2:E100,"Jan",B2:B100)

or

=SUMIFS(B2:B100,A2:A100,">=01/01/2024",A2:A100,"<=31/01/2024")

Or you can use SUMPRODUCT formula as described in the article above. For example:

=SUMPRODUCT(B2:B100*(E2:E100="Jan"))

I am looking to sum the values in a cell column 'AN' based on the conditions in my original formula below.

my current formula is ;

=COUNTIFS(Report!$I$6:$I$1176,'Data validation'!$D$7,Report!$H$6:$H$1176,">"&TODAY()-31,Report!$AL$6:$AL$1176,"*US*")

i would like to extend this from a countifs to a Sum of the values in column AN for the conditions above.

many thanks

Hi! Use the SUMIFS function with the same conditions you applied in your formula. You can find the examples and detailed instructions here: Excel SUMIFS and SUMIF with multiple criteria – formula examples. The formula might look as follows:

=SUMIFS(AN6:AN1176,Report!$I$6:$I$1176,'Data validation'!$D$7,Report!$H$6:$H$1176,">"&TODAY()-31,Report!$AL$6:$AL$1176,"*US*")

fantastic, really appreciate your advice and website content. thank you

I am trying to use SUMIFS with two criteria from two sheets. One is MMM-YY and second is "Direct" or "Indirect" and it does not return the correct sum.

Hi! I don't know what formula you are using. I don't have an example of your data. Unfortunately, this information is not enough to give you any advice. I recommend reading and studying these instructions carefully: Excel SUMIFS and SUMIF with multiple criteria – formula examples. Or describe the problem in more detail.