# SUMPRODUCT

March 13, 2012 4 Comments

The SUMPRODUCT function returns the sum of the products of corresponding numbers in one or more arrays.

SUMPRODUCT (array 1, array 2, …array 30)

array1: A array of cells. array 1 is a reference to a single range of cells containing values of any type. If string or Boolean values

array 2,……. array 30: Optionally include one or more additional array ( ranges) of cells. The ranges must all have the same dimensions.

Usage Notes

The SUMPRODUCT function multiplies the corresponding numbers in each range and then sums each of the products. If only one range is specified, SUMPRODUCT returns the sum of the range.

## Traditional Use of SUMPRODUCT

In it’s traditional form or classic form, SUMPRODUCT multiplies each value in one array by the corresponding value in another array, and returns the summed result. As an example, if cells A1:A3 contain the values 1,2,3 and B1:B3 contain 10,20,30, then

=SUMPRODUCT(A1:A3,B1:B3)

returns 140, or (1*10)+(2*20)+(3*30)=10+40+90=140.

This is a useful function, but nothing more than that. A further, more ‘creative’ use of

**SUMPRODUCT**has evolved, and is still evolving. This has been a creative and productive process that has significantly increased the useability of

**SUMPRODUCT,**

## Advanced Use of SUMPRODUCT

There are two very useful functions that support conditional counting and summing, namely COUNTIF and SUMIF. Very useful functions, but limitation in that they can only evaluate a single test range.

To know more about the countif function have look at the following link

https://anandexcels.wordpress.com/2012/01/28/countif/

To know more about the Sumif have look at the following link

https://anandexcels.wordpress.com/2012/01/28/sumif-function/

The only constrain in these function is it will evaluate a single criteria, all thou we can force them to do multiple criteria using IF function

We will look into more rational way of forcing he sumproduct function to do more using a array formula. if you observe in the functions list it is under the array formula’s group. lets try to understand the functionality using an example

We can easily count the number of Amazon with

**which returns 2.**

=countif(B2:B9,”Amazon”)

=countif(B2:B9,”Amazon”)

Similalrly, it is straight-forward to get the value of products sold through “Amazon”, using

**which gives 2725**

=SUMIF(B2:B9,”Amazon”,D2:D9)

=SUMIF(B2:B9,”Amazon”,D2:D9)

**But supposing that we want a count of how many Amazon sold in Jan, or the value of them? The number can be calculated with**

**.**

**which is an array formula. Similarly, the value is obtained with**

=ArrayFormula(SUM(IF(B2:B9=”Amazon”,IF(A2:A9=”Jan”,1,0),0)))

=ArrayFormula(SUM(IF(B2:B9=”Amazon”,IF(A2:A9=”Jan”,1,0),0)))

**which is also an array formula.**

=ArrayFormula(SUM(IF(B2:B9=”Amazon”,IF(A2:A9=”Jan”,D2:D9,0),0)))

=ArrayFormula(SUM(IF(B2:B9=”Amazon”,IF(A2:A9=”Jan”,D2:D9,0),0)))

But as this page is about SUMPRODUCT, you would expect that we could use that function in this case, and we can. The solution for the number of Amazon sold in Jan using this function is

**The value is obtained with**

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)))

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)))

**The * is being used as the AND operator, the formula is saying, where A2:A9 = Amazon AND B2:B9 = Jan, and where A2:A9 = Amazon AND B2:B9= Jan, multiplied by D2:D9.**

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)*(D2:D9)))

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)*(D2:D9)))

This is relatively easy to use when there are multiple criteria.

**We can see that the * is equivalent to AND in the formula, how this works is explained later, but supposing we want an OR condition. As a further extension of its use, we use the ‘+’ (plus) operator to count OR conditions, such as how many books sold were either**

Flipkart or Landmark. The formula for this is

**which returns the result 6 as expected**

=ArrayFormula(SUMPRODUCT((B2:B9=”Flipkart”)+(B2:B9=”landmark”)))

=ArrayFormula(SUMPRODUCT((B2:B9=”Flipkart”)+(B2:B9=”landmark”)))

**So far, so good, in that we have a versatile function that can do any number of conditional tests, and has an inbuilt flexibility that provides extensibility.**

## SUMPRODUCT Explained

Understanding how SUMPRODUCT works helps to determine where to use it, how to can construct thus formula, and thus how it can be extended. below shows an example data set that we will use.

In this example, the problem is to find how many Amazon with a date in Jan were sold. A2:A9 holds the Date, B2:B9 has the Shop, and D2:D9 has the number sold. The formula to get this result is

**=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)*(D2:D9)))**The first part of the formula (B2:B9=”Amazon”) checks the array of makes for a value of Amazon. This returns an array of TRUE/FALSE, in this case it is

**Similarly, the categories are checked for the vale Date with (A2:A9=”Jan”). Again, this returns an array of TRUE/FALSE, or**

{FALSE,TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE}

{FALSE,TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE}

**And finally, the numbers are not checked but taken as is, that is (D2:D9), which returns an array of numbers**

**{TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE}**

**So now we have three arrays, two of TRUE/FALSE values, one of numbers. This is showm in below.**

**{1200,1525,1400,900,800,1200,1100,1250}**

**And this is where it gets interesting.
**

SUMPRODUCT usually works on arrays of numbers, but we have arrays of TRUE/FALSE values as well as an array of numbers. By using the ‘*’ (multiply) operator, we can get numeric values that can be summed. ‘*’ has the effect of coercing these two arrays into a single array of 1/0 values. Multiplying TRUE by TRUE returns 1 (try it, enter =TRUE()*TRUE() in a cell and see the result), any other combination returns 0. Therefore, when both conditions are satisfied, we get a 1, whereas if any or both conditions are not satisfied, we get a 0. Multiplying the first array of TRUE/FALSE values by the second array of TRUE/FALSE values returns a composite array of 1/0 values, or

**{0,1,0,0,0,0,0,0}*{1200,1525,1400,900,800,1200,1100,1250}**which will result in 1525

Similarly we can count the same

**the arrays will be as show in the below example**

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)))

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)))

and after executing the above formulas

and answer is highlighted in yellow colour which will result in value 1

If you have been able to follow this explanation all of the way through, it may have occurred to you that although we are using the SUMPRODUCT function, the ‘*’ operators have resolved the multiple arrays into a single composite array, leaving SUMPRODUCT to simply sum the members of that composite array, that is, there is no product. This is perfectly correct, and perfectly valid,SUMPRODUCT can work on a single array (put 1,2,3 in cells A1,A2,A3, and insert =SUMPRODUCT(A1:A3) in a cell, it returns 6 correctly). In reality, we only need the ‘*’ to coerce the arrays that are being tested for a particular condition, we do not need it for the array that is not subject to a conditional test. So we could also use

=ArrayFormula(SUMPRODUCT((B2:B9=”Amazon”)*(A2:A9=”Jan”)*(D2:D9)))

which does use the product aspect .

- Double uninary will not work in google docs spead sheets.
- When using the SUMPRODUCT function, all arrays must be the same size, as corresponding members of each array are multiplied by each other.

- When using the SUMPRODUCT function, do not use the open ranges it may cause errors

In a SUMPRODUCT function, the arrays being evaluated cannot be a mix of column and row ranges, they must all be columns, or all rows. However, the row data can be transposed to present it to SUMPRODUCT as columnar.

Very nice article. Thankfully the newer sumproduct syntax is much easier to use and the double unary approach is no longer needed (as you covered above, there were some very clever people who pushed sumproduct way beyond its initial intended use). Importantly, sumproduct now works for mixing column and row data for Excel 2010 and on, which is another very nice use.

Consider this array, with cell A1 at top left and cell D6 at lower right (cell A1 is empty):

X Y Z

P 6 7 9

Q 5 4 3

R 3 2 2

S 1 2 2

Q 3 4 5

I want to sum all numbers when the row starts with Q and the column starts with Y. The following sumproduct equation will provide the correct answer, which is 8:

=sumproduct((a2:a6=”Q”)*(b1:d1=”Y”)*(b2:d6))

Dear Mike Fallon,

That is a very good observation

thanks for updating

Hello! This is a great article! I have one question though….my formula is returning “argument out of range” because the range in my data sheets is constantly changing and is never the same size as the day (sheet) before. How could I change this formula to either look at the entire row no matter how long it is, or skip blank cells in a range? Any help would be greatly appreciated!

=ArrayFormula(sumproduct((‘1’!$E$5:$E$199)*(‘1’!$A$5:$A$199=$A8)*(‘1’!$D$5:$D$199=G$3)+((‘2’!$E$5:$E$200)*(‘2’!$A$5:$A$200=$A8)*(‘2’!$D$5:$D$200=G$3)+….and so forth up to 31 sheets.

Dear Tammy McBride,

Make sure the length of the selected array is same, what i mean to say is instead of this

’2′!$E$5:$E$200 try this ’2′!$E$5:$E$199

Hope this will work

anand varma