What is Excel SUMPRODUCT formula and how to use it?

Nice explanation Chandoo, I love it. It’s “almost” crystal clear for me.

Can you explain the use of — before the test ?

Another alternative for this example is to use SUMIFS function, but it is available only in excel 2007 & 2010

Nice post Chandoo!!
Is there any limit to the number of arguments we can use with SUMPRODUCT function?

Chandoo – good explanation. I’m still learning about sumproduct, arrays, and the function of — .

I’d read elsewhere about using — to “coerce” some values – I know what coerce means but I didn’t really understand it in this context. I do understand your explanation though – basically it’s multiplying the results by -1. I tried using – instead of –, and it appears to work just the same, as long as you have an even number of arrays to multiply, as you do in your Luke Skywalker, West example.

Hi all,
I’ve seen the double hyphen (–) in all SUMPRODUCT tips, but haven´t used and so far my formulas seem to work fine (no errors)… any idea when it should really be used?.

Kaliman – for me, Chandoo’s Luke Skywalker / West example doesn’t work unless I use the double hyphen – -

I’ve just noticed in my earlier post, I had typed 2 hyphens and it came out looking just the same as 1. Apologies if anyone was confused by that
It should have read “…I tried using – instead of – - …”

The double-negative on the TRUE/FALSE result is likely my favorite formula trick, and has always allowed me to stay away from arrays (which I find cumbersome and error-prone).

Chandoo/Excel Gurus, do you believe the SumProduct usage is inherently faster than an array of SumIFs? I’ve read Excel forum threads that assert that the SumProduct performance difference is negligible because Excel was performing array activities behind the scenes, but those threads were a bit old (circa 2002/2003), and I never really bought into it. What has your experience been on the performance question?

Great post. The array part of this post will come in extremely handy at work.

sumproduct() is my favourite multiple condition summing formula. but double”-” before the argument was a new thing for me as I usually used syntax like this =sumproduct((array1=”luke”)*(array2=”west”)*array3)
however, sumproduct has one drawback – the array you want to sum must consist only from numericals. even if actual selections wont include cell like “n.a.” (might happen in datasets), sumproduct() will result in an error. find/replace helps just fine

Seconding the use of SUMIFS if you have 2007 or higher. I personally find it much more intuitive.

Hi Brian, Hi Shandoo,
for more details and considerations …
here from an Excel Guru :
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

;o)))

Great explanation Cahndoo,

I have seen the — used often in the SUMPRODUCT formula but not the N() fucntion, is there a reason not to use the N() function?

Excel has trouble doing math with text values. It needs to turn them into numbers first.

Type ‘0 into a cell (mind the quote). This makes a text zero.

Type =A1=0 into another cell, and you will get FALSE as the result.

Type =A1-1 into another cell, and you will get -1

But wait, if it’s not a number, why can it do math?!

Well, Excel forces things to be numbers when you do math on them.

So if you use math in sumproduct, you don’t need the double-minus:
=SUMPRODUCT((A1:A10=”Luke Skywalker”)*B1:B10=”West”)*D1:D10)

When it does the math for this, it will multiply all the zeroes and 1’s and values together, and will always come out with a number.

If you don’t put them in:
=SUMPRODUCT((A1:A10=”Luke Skywalker”),(B1:B10=”West”),D1:D10)

You get 0 instead.

So we need to turn them into numbers first. We can do that a lot of ways. For instance:
=SUMPRODUCT((A1:A10=”Luke Skywalker”)+0,(B1:B10=”West”)+0,D1:D10)

=SUMPRODUCT((A1:A10=”Luke Skywalker”)*1,(B1:B10=”West”)*1,D1:D10)

=SUMPRODUCT(N(A1:A10=”Luke Skywalker”),N(B1:B10=”West”),D1:D10)

But when it comes down to it, the people who are really smart at Excel figured out that Excel is fastest when it uses the unary operator (the ‘-’ sign) twice.

So now people write:
=SUMPRODUCT(–(A1:A10=”Luke Skywalker”),–(B1:B10=”West”),D1:D10)

Feel free, if you have trouble, to use the *1 or +0 or N() if you’d prefer. They will work too.

So, to conclude.

If you use SUMPRODUCT with commas, you need to turn true/false values into numbers first by doing math or by using the N() function.

If you use SUMPRODUCT with multiplication, you’re already doing math so you don’t need it.

Most of the time it’s personal preference (I’m sure that there is a difference in speed in the long run, but most of us will probably never ever know about it).

Hello Chandoo

– makes formula confusing for many users and it can be avoided if you replace , with * . The revised formula will be as under
=SUMPRODUCT((A1:A10=”Luke Skywalker”)*(B1:B10=”West”)*(D1:D10))

This formula is giving multiconditional sum and we have used only two conditions. However you can add as many conditions as you like provided the hight of each range is same.

If you remove the last entry with does not have condition attached to it, the formula will do the multiconditional count.

Formula for multiconditional count will be
=SUMPRODUCT((A1:A10=”Luke Skywalker”)*(B1:B10=”West”))

Thanks

Does this usage of SUMPRODUCT allow for multiple entries of the search criteria throughout a range?

i’ve always been fond of the SUMPRODUCT((range=criteria)*(range=criteria)) to search for multiple variables: Saves on pesky pivot tables.

Chandoo my man. I had to multiply some arrays today, and was momentarily scratching my head trying to remember how to do this with array formulas. I took a break, checked out your RSS feed, and whoa…you’ve taken my question right out of my mind and answered it.

I owe you a donut for this, which cancels out the one I earnt the other day. Don’t you just love this weightless, low calorie economy?

I seriously wonder how many people-hours you saved with this one post alone Chandoo.

I guess it leaves more time for virtual donuts.

I have a sumproduct question!
In my workbook i am working with 2 worksheets: “Hours” & “Variance”.

I am essential developing a variance tool on the variance tab.

I have a list of employees on the hours tab and the rests of the actual hours that they have worked by dated column.

I am trying to write a formula that totals all of the hours for a single employee based on a given date. See example at the end.

These formulas work as a stand alones:

=ADDRESS(MATCH(A10,Hours!$M$621:$M$725,0)+ROWS(Hours!$L$1:$L$620),MATCH($C$2,Hours!$620:$620),4,1,”Hours”)

=SUMPRODUCT(–(Hours!M621:M725=A10)*Hours!$N$621:$BZ$725)

BUT, when I try to combine the two to get the date variability like I want, Excel says my formula contains an error. Here is what the formula looks like:

=SUMPRODUCT(–(Hours!M621:M725=A10)*Hours!$N$621:ADDRESS(MATCH(A10,Hours!$M$621:$M$725,0)+ROWS(Hours!$L$1:$L$620),MATCH($C$2,Hours!$620:$620),4,1,”Hours”))

Doughnuts all around for anyone who can help me on this one! I suspect that I have a syntax issue in my formula but not sure.

Example:

Variance Tab
As of: 11/28/2009

Resource Total hours worked Answer should be
Billy 130
Bob Formula 50
Joe goes 80
An here 90
Jill 160

Hours Tab

Resource 11/14/2009 11/28/2009 12/12/2009
Billy 40 90 30
Bob 30 20 80
Joe 50 30 80
An 80 10 40
Jill 80 80 50

bummer, example data formatted ugly….imagine rows and columns, rows and columns

Hi Chandoo, Hi Jadam

;o))) there is no use of SUMPRODUCT ….

just you have to name some ranges
ResDat =Hours!$B$1:$A$Z1 ( ie for 52 dates)
ResNam =Hours !$A$2:$A$1000 ( ie for 1000 names)

in Variance!B2 put this formula
=SUMIF(WBK1!resDat,”<="&$A$10,INDIRECT("hours!"&ADDRESS(1+MATCH(A2,WBK1!resNam,0),2)&":"&ADDRESS(1+MATCH(A2,WBK1!resNam,0),52)))

and pull it down as many time as ressource names
beware :
dateref of calculation is absolute $A$10
current ressource name is relative : A2

WBK1 is actual Workbook.name

HTH
;o)))

Supposedly — is slightly faster than any other way of converting the logicals to numbers.

My preference is to use =SUMPRODUCT(1*(A1:A10… instead of =SUMPRODUCT(–(A1:A10=
As it is easier to read, especially for those not so familiar with formulas.

Just fallen on this site – already hooked.

Sumproduct is great. I used to struggle to add multipple criteria up in my spreadsheets, and then someone from UtterAccess.com taught me about Sumproduct. I can’t thank that person enough.

Anyway…many articles to catch up on. Keep up the outstanding work.

Chandoo:

The SUMPRODUCT function is the most versatile in Excel.

It can be used to do multicolumn sorts by array (with no human intervention or VBA required).

It can determine if a number is prime.

It can coerce bitwise operations in integers (i.e. bitwise logical AND, OR, XOR, IMP, EQ, and NOT).

It can be used to supercharge lookup tables with bitmasks.

It can find the dot product of two vectors.

It can do the database calculations you addressed in this post and has the massive advantage over SUMIF and COUNTIF (and the Excel 2007 SUMIFS and COUTNIFS) that it can handle OR clauses in the criteria.

It can replace most any Array-Entered Formula that uses SUM, and is usually about 10% faster.

This list could go on for pages…

I have a very detailed post on my blog:

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

Regards,

Daniel Ferry
excelhero.com/blog