The Venerable SUMPRODUCT

| 151 Comments | 0 TrackBacks


Mastering the venerable SUMPRODUCT function is a requirement for any Excel Hero.

It's one of the most useful in the roster of Microsoft Excel functions.

From my perspective, Excel would be a very different application if the SUMPRODUCT function had never been developed to the degree it is today. It's an amazing function that can be marshaled to solve a dizzying array of problems. It's an Array Function in that it operates on arrays, but it does not need to be entered with the Control-Shift-Enter key combination, and so it does not need the fancy parenthesis that adorn normal Array Formulas. However, it is a function that works on arrays, and is actually slightly quicker than an equivalent CSE Array Function.


excel-hero-academy-web-banner1.jpg


One would never guess from the innocuous name the shear power and versatility embedded within the SUMPRODUCT function. Not only is it well worth your time to examine this function in detail, it is quite literally a prerequisite to honing your Excel abilities to the Excel Hero level. Rarely do I build a model that does not make extensive use of the SUMPRODUCT function.

From a maths perspective, SUMPRODUCT calculates the dot product of two vectors (arrays), but realize that it can be used with many more than two arrays.  A better name might have been the SumOfProducts function, as this in a nutshell is what it does.  Consider the following image:

sumproduct.png

Notice that Column D is the product of the three columns to its left. Cell D2 contains the formula =A2*B2*C2 and that formula is copied down as far as the columns go (Row 30 in this case). The entire purpose for Column D is to get intermediary values so that we can later sum them. Cell F4 shows this final calculation. For some spreadsheets this intermediary column approach is wanted for presentation purposes. However, sometimes all we want is the ultimate total, the sum of all those products. And that's exactly what the SUMPRODUCT function does. Notice the formula in the Formula Bar. It results in the same total.

There are some points to note here. 

Firstly, notice that the arrays I've used are the entire columns (A:A, etc.). I've done this on purpose to point out that doing so is a bad idea.  This was not even possible prior to Excel 2007 and would result in an error. Excel 2007 is happy to accommodate the request, but it puts a massive hit on the instance of Excel that workbook is running in. Always try to limit the number of cells that Microsoft Excel functions or formulas must process. A better formula would have been:

=SUMPRODUCT(A2:A30,B2:B30,C2:C30)

The SUMPRODUCT function multiplies elements of different arrays that hold the same position in those arrays and when done, sums all those products. This trivial example made this obvious by showing the function at work on three columns. But understand that those arrays could have just as easily been rows instead of columns, or they could have been rectangular ranges of any arbitrary dimension. They could have been array constants, i.e. {2,3,4,1,9,8} or Named Ranges, or the results of a boolean comparison (simple or complex), a custom mixture of many native Microsoft Excel functions, an Array Function, or even a User Defined Function. The one overriding requirement is that the arrays must be rectangular, contiguous, and of the same dimension. So if Array 1 is a column of 29 elements (as in the above image) then so must be all of the other ranges.

The next point is that this simple example had just three columns (arrays). The SUMPRODUCT function is just as happy with 1 array or up to 30 arrays, each separated by a comma. Thirty is the maximum number of arguments that Microsoft Excel functions can support. 

Ok, so SUMPRODUCT is adept at summing the products of equivalently placed elements in arrays. Why is that so powerful? Because it can be used in many non-obvious ways to calculate totals and mine data.

One way that it shines is calculating sums, counts, averages, and other metrics of list data based on criteria. The result here is basically analogous to a SQL database query that produces a total with a WHERE clause. For example the following SQL query: 

SELECT SUM(OrderPrice) FROM Orders WHERE Customer='Hansen'

could be emulated in Excel with any one of these SUMPRODUCT formulas:

=SUMPRODUCT((OrderPrice),--(Customer="Hansen"))
=SUMPRODUCT((OrderPrice),1*(Customer="Hansen"))
=SUMPRODUCT((OrderPrice),(Customer="Hansen")*1)
=SUMPRODUCT((OrderPrice),(Customer="Hansen")+0)
=SUMPRODUCT((OrderPrice),(Customer="Hansen")^1)
=SUMPRODUCT((OrderPrice),SIGN(Customer="Hansen"))
=SUMPRODUCT((OrderPrice),N(Customer="Hansen"))
=SUMPRODUCT((OrderPrice),(Customer="Hansen")*True)
=SUMPRODUCT((OrderPrice)*(Customer="Hansen"))

Some notes are in order here. 

The terms OrderPrice and Customer could be Named Ranges (they would basically be defined as columns in a list or table and would each be of the same length). Those Named Ranges (I actually prefer to refer to them as Named Formulas) could be dynamic ranges. Or conversely the terms could be replaced with direct cell references, such as C2:C1000 for OderPrice and B2:B1000 for Customer. Or the terms could be replaced by almost any combination of Microsoft Excel functions that returns an array. Please note that for some reason, SUMPRODUCT always fails when using an IF function on any of the terms inside the SUMPRODUCT, which is OK because we can use boolean logic to eliminate the need for branching. Please read I Heart IF for details.

The order of the terms is irrelevant. In any of the above examples, the answer is the same, whether the OrderPrice term or the Customer term comes first.

The first eight variations above all have two things in common. They use a comma to separate the terms, and each is employing a slightly different technique to coerce a lone boolean value (True or False) into their numeric equivalents, 1 or 0. If we fail to do this coercion on LONE boolean values, the SUMPRODUCT function always returns the value of 0. Of the eight different coercion methods, the double unary method (--) is the quickest. 

The ninth variation (the last one) above is the syntax I personally use most often. The entire calculation is done in the first argument so no commas are required and since the boolean term is being multiplied by the OderPrice term, there is no need to further coerce the boolean into a number. The multiplication has already done. 

This method is shortest in punctuation and therefore more concise. But it is imperceptibly slower. However, the trade off is greater flexibility. Since all of the work is being done in the first argument to the SUMPRODUCT function, there is no longer a limit of 30 terms. Using this syntax you can combine as many terms as you need. And most important of all, this method allows for more robust logic in the criteria. Specifically, we can specify OR clauses. For example the following SQL query: 

SELECT SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR Customer='Jones'

could be emulated in Excel with this SUMPRODUCT formula:

=SUMPRODUCT((OrderPrice) * ((Customer="Hansen")+(Customer="Jones")))

When combining the boolean terms (the ones that evaluate to True or False), they are easy to read if you remember that the plus symbol means OR, while the multiplication symbol means AND. Using this technique, you can tabulate very specific records indeed. For example, consider the following SQL query:

SELECT SUM(OrderPrice) FROM Orders WHERE (Customer='Hansen' OR Customer='Jones') AND State='CA' AND SalesDate>=#1/1/2010# And SalesDate<=#1/31/2010#

This query just says to total all of the orders in January of 2010 from California for the customers Hansen or Jones. It could be achieved in Excel using this SUMPRODUCT formula:

=SUMPRODUCT((OrderPrice) * ((Customer="Hansen")+(Customer="Jones")) * (State="CA")*(SalesDate>="1/1/2010") * (SalesDate<="1/31/2010"))

For me this is even more "readable" than the SQL database query. 

No matter how precise your criteria, which basically means no matter how many criteria terms you need to combine, the SUMPRODUCT function stands ready to tally the specified records, when used with my preferred syntax of combining everything into the first argument.

Another advantage (in my opinion) to using this syntax style is that it acts differently than the comma separated form when there is text in the summing array (i.e. OrderPrice in our examples) in any record returned by the criteria. Sometimes erroneous text can be in a column of data. The comma separated method will treat such text as a 0, combining everything in the first term will result in an error when the text is found in criteria specific records. I prefer to be notified with the error rather than silently calculating an incorrect result.


You'll notice that in all of these examples, we are summing the OrderPrice column. If instead we wanted to count the number of records that satisfied the criteria, all we need to do is drop the OrderPrice term from the formula. So if we wanted to know how many orders there were in January of 2010 from California for the customer Hansen or Jones, we would use this formula:

=SUMPRODUCT(((Customer="Hansen")+(Customer="Jones")) * (State="CA") * (SalesDate>="1/1/2010") * (SalesDate<="1/31/2010"))

Notice that the only difference here is that the OrderPrice term is gone. And finally if we wanted to know the Average amount of those orders we would just divide the total amount by the number of orders. This makes for a long formula, but now that you understand the syntax, it's easy to follow. This very specific average would be calculated with this formula:

=SUMPRODUCT((OrderPrice) * ((Customer="Hansen")+(Customer="Jones")) * (State="CA") * (SalesDate>="1/1/2010") * (SalesDate<="1/31/2010"))    /    SUMPRODUCT(((Customer="Hansen")+(Customer="Jones")) * (State="CA") * (SalesDate>="1/1/2010") * (SalesDate<="1/31/2010"))

It should be noted here that sometimes when the formulas grow this large, it can be helpful to name them (or parts of them) in the Name Manager.

These type of SUMPRODUCT calcs with multiple criteria can become very interesting when the individual terms are used with OFFSET or INDEX or INDIRECT or MOD/ROW. These enhancements go beyond the scope of this post, but they enable such refinements as considering only every nth record in the tabulations, changing the scope of the array, and many other fascinating feats.


Experienced users may point out that most of the above can be done by using the SUMIF or COUNTIF (or in Excel 2007 SUMIFS and COUNTIFS) functions. This is only partially true. The SUMIF and COUNTIF functions can only work with one criterion. The SUMIFS, COUNTIFS, and AVERAGEIFS function of Excel 2007 can work with up to 29 criteria, but they are limited to AND Logic between the criteria with no way to specify OR Logic.

Really experienced users might point out that all of the above can be done with Array Formulas. While this is true, the SUMPRODUCT function is optimized and is roughly 10% faster than an equivalent Array Formula, and there is no need for the Control-Shift-Enter formula entry.

All of this just scratches the surface of what the SUMPRODUCT function can accomplish. It can be used in countless situations that have nothing to do with database calculations. For example, if you wanted to know if the number in A1 was a prime number, you could use this non-Array Formula:

=SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("1:"&INT(A1^0.5))))=0))=1

SUMPRODUCT can be used to do a multi-column sort by formula.

When a SUMPRODUCT function is combined with Conditional Formatting some very interesting results can be achieved.

Another extremely important use of the SUMPRODUCT function which opens an entire universe of new possibilities is to use it to conduct bitwise logical operations such as Logical AND, OR, XOR, NOT, IMP, or EQ. This is the direct manipulation of the bits inside of an integer. There are countless uses of bit manipulation and this post can get you started.

Finally, amongst Excel gurus there is considerable debate about the best way to do database type calculations in Excel. While SUMPRODUCT as demonstrated in this post is very capable, there are alternatives. No one solution is the perfect answer to every situation. I have been conducting timing trials and will soon share detailed results for many different techniques. In the meantime it should be enough to know that SUMPRODUCT is perfectly fine when it is scanning several thousand records, even tens of thousands. However, if you are using it for hundreds of thousands of records than an alternative is likely in order.




And here are some other reference articles at Excel Hero:

The Imposing INDEX
The Venerable SUMPRODUCT
- I Heart IF
5 And 3 Is 1
Live Calendar Musings
Which Function To Use
Excel VBA Shortcut Range References
Excel Partial Match Database Lookup
Excel High Precision Engineering Chart #1
Excel Zip Code Lookup
Excel Dynamic Checkmark
Animated Business Chart
Excel Acrobat PDF Form Filler



And for something completely unique, here's a list of animated charts on Excel Hero:

 - Excel, A Presentation Platform (Number Spiral)
 - Lilac Chaser (Optical Illusion)
 - Stereokinetic (Optical Illusion)
 - Illusory Contours (Optical Illusion)
 - Breathing Square (Optical Illusion)
 - Enigma (Optical Illusion)
 - Two Sinusoids (Optical Illusion)
 - Perpetual Collisions (Optical Illusion)
 - Freezing Rotation (Optical Illusion)
 - Reverse Spoke Illusion (Optical Illusion)
 - Stepping Feet Radial Illusion (Optical Illusion)
 - Swimming Fish (Optical Illusion)
 - Mutually Interfering Shapes (Optical Illusion)



Learn Advanced Excel!

Advanced and Creative Formulas and advanced charting are just a couple of the literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning. EHA is Excel online training.

I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.

In a few weeks I will be offering the course again. There's already over 250 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!

Try it. It truly is one of the best online courses Excel Hero Academy.



Enhanced by Zemanta
If you liked this article, please share it!



Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/6

151 Comments

For AND Criteria and for Excel 2007 and Greater
SUMIFS/COUNTIFS is the fastest followed by DSUM-SUM followed by SUMPRODUCT

For AND Criteria and for Excel 2003 and Below
DSUM-SUM is the fastest

For OR Criteria and for any version of Excel
DSUM-SUM is faster than SUMPRODUCT but SUMPRODUCT is more flexible

When some criterias are in Columns and some in Rows there is no alternate to SUMPRODUCT

There is one more method of coercion
=Sumproduct(N(condition1),N(Condition2)...(RngToSum))which some believe to be faster than --

I have a workbook where i have speeds of different formulas...I could e-mail it to you if you want to compare the results with your work

@Sam - Your results seem to line up with my own.

Have you tested SIGN as a method to coerce a lone boolean term inside a SUMPRODUCT? How about raising the term to the first power, as in (term^1)?

On a different note it's obvious that doing one MATCH column for a bunch of INDEX lookups is faster than including the MATCH inside each formula, but have you tested using one INDEX formula array-entered over an entire range, as opposed to independent INDEX formulas in all the cells?

I would be very interested if you would like to send me your file: daniel [dot] ferry [at] gmail [dot] com

Thanks,

Daniel Ferry
excelhero.com/blog

I tried the Sign Option V/s -- option.
The -- option seems to be faster


1 Match column and several Index's is definetly faster than several match/index and vlookups.

But 1 Match and a Single Array entered formula in each column is slightly faster than the above.

Thanks for sharing this!

@sam -

DSUM may be fast, but it's damn awkward to use since it needs criteria RANGES. Yeah, you could set up something with What-If tables, but it's still awkward.

@Harlan.
DSUM alone is awkward. But DSUM-SUM combo can be dragged down/across like SUMPRODUCT

Eg: =DSUM(Data,B$1,$A$1:$A2)-SUM(B$1:B1)


Hi Daniel,

What exactly are you missing in the OpenOffice implementation of SUMPRODUCT ?

Hi Daniel, can I choose the largest number from unsorted data ie, there are three column contain name, age and criteria (child, young, old) how to choose the oldest for each criteria without sort it? In this case can I combine sumproduct and large function?

@irwan satya-

Are you trying to return the entire record of the oldest for each criteria or just the age?

I ask because SUMPRODUCT only returns numeric answers.

If you send me you spreadsheet, I can take a look for you.

daniel dot ferry at gmail dot com

Regards,

Daniel Ferry
excelhero.com/blog

Daniel,

just read your comment on Chandoo's post about sumproduct, and directed me to this one. Absolutely fantastic definition. I am getting such a fan of this formula, that i was wandering how I survided all these year without using it....

All the best,

Martin.

@Martin -

Thanks. There's so many uses for SUMPRODUCT that I was thinking about writing a follow-up article if I can find the time.

Anyways, welcome to my blog. I hope you become a regular.

Regards,

Daniel Ferry
excelhero.com

Hi Daniel,

Can you please advise me how to use sumproduct to determine the following:

(1) Can you use SumProduct to highlight & sort if data in in all cells, over four columns are in credit. Do use SumProduct & Conditional formatting to do this, (ie Monthly, Quarterly, Yearly & 5 yearly).

How to calculate if all 4 rows are in credit, then sort by highest to lowest balance

ASX Code Company name Sector Market Cap 1 Month Trailing Return 3 Month Trailing Return 1 Year Trailing Return 5 Year Avg. Annual Return
FGL Foster's Group Limited Alcohol/Tobacco 10,327,660,923 -1.70% 0.00% 11.00% 5.10%
ANZ ANZ Banking Group Ltd Banks 60,844,189,521 17.20% 12.10% 60.90% 8.80%

(2) Place the sum of the four cells in column I and/or pivot table

(3) How do you Add the maximum & minimum dollar amount per month, quarter, Six monthly & Yearly.

(4) Do have a document that details all the uses of SumProduct function.

Refer row examples below:

Regards,

Ron.

I have a spreadsheet where I'm trying to use SUMPRODUCT but I need some help.

The data is a monthly calendar where individuals are on call.
column A is days of the week (M-Sunday)
colum b is the individuals' initials like "VG"

I would like to create a chart that shows the number of days VG works on weekends (for example)

But what if the person only works a Saturday or Sunday? Also if they do work both days, we would count that as working one weekend...if they work just Saturday or Sunday it would be counted as working 1/2 the weekend.

I can get the formula to count either Saturday or Sunday but not both.

I can provide the chart if that would help.

Thanks so much.

B

@Bill -
Yeah. Send me your file and I'll take a look. It's kind of difficult without actually seeing it.

Regards,

Daniel Ferry
excelhero.com

Please, Display more examples.

Can you please send the file which contain the excel formula.


Neeraj
Excel Enthusiast
sandesh2neeraj@gmail.com

@Neeraj -

Which file are you referring to?

There is no sample file for this article.

Regards,

Daniel Ferry
excelhero.com

Awesome post Daniel,

If you could find the time to do that followup post, I promise to read it.

Joe

Excel Hero Academy Homework
Jose Alberto Carranza

I've used sumproduct a bunch of times. Since I'm an Engineer most of my uses were to solve maximization, optimization problems to calculate my constraints and then use Excel's solver addin. After reading this I've found a lot of interesting ideas and I will begin to modify my files to accommodate this use of sumproduct since I used to be a nested if guy.

Thanks for sharing all your knowledge and I'm excited about module 2!!!

@Sam/Danial: Can either of you give some more context about the type of situation when you might want to use DSUM - SUM along the lines of Sam's example above of: =DSUM(Data,B$1,$A$1:$A2)-SUM(B$1:B1)

I'm trying to think of a situation when you might want to use such a formula, let alone drag it across or down. In this specific example, dragging down would 'uncover' more criteria as $A$1:$A2 became $A$1:$A3, so I guess this could be useful in the event where you want to check the difference that additional criteria make to your result set. But I don't get the point of the SUM bit. Or more likely, I'm missing the point completely.

Sorry Daniel, misspelt your name again.

Excel Hero Academy Homework
Hans Knudsen

I wonder how much this function and its many uses would have been well-known without the many very smart Excel people in user groups etc. Excel's help contains about 15 lines on this function.

I have use SumProduct very simplistically. I am wondering if it possible to get an example template which demonstrates the 9 SumProducts with real data? I find it had to understand concepts without being able to work through it.

I love using the SUMPRODUCT function having only recently got to grips with it's beyond scope functionality. But as far as I can tell (being a little late to the party as I was learning it) - haven't functions like SUMIF and other new Excel 2007, 2010 replaced it for speed and functionality...?

I may well be wrong (it's happened many times!)

Oli (EHA student)

Oli,

SUMIFS and the like are nice functions, but they do NOT entirely replace SUMPRODUCT. Firstly, solutions based on SP are compatible with all versions of Excel, while the new functions require 2007+. And since millions of users have not upgraded this is an important consideration.

But there is another issue - flexibility. The SUMIFS type functions have NO provision for OR logic in the criteria. For example you can sum all the values that satisfy this criteria AND that criteria AND this AND that, etc. But none of the AND's can be an OR.

With SUMPRODUCT, all of the logical operators can easily be included (AND, OR, NOT, even XOR, and IMP)..

Finally, SP can be used to do many array-style calculations that SUMIF is simply not capable of, for example the test to see if a particular number is PRIME. There are literally thousands of these kind of array-style formulas.

So, the SUMIFS family of functions is a welcome additions to Excel, but they do not replace the Venerable SUMPRODUCT.

Regards,
Daniel Ferry
Excel Hero Academy

P.S. See you in class!


Thanks for yet another great post!

I have used SUMPRODUCT for a couple of years - but mainly for rather simple calculations like weighted averages across a range or so. After reading this very post in the summer, I have started using the function for mining data as an alternative/supplement to using Pivots to extract the information.

It is certainly a very versatile function - so I am eager to see what magic we can come up with in EHA!

I love using Sumproduct, and having worked a bit with Access and SQL in the past definetly find it easy to read what's going on. Add in Named formulas from Module one and... Well, the Academy is blowing my mind!

I would love to learn some more uses for Sumproduct, it seems so versatile.

Both this article and I heart IF() are the first places I point people too who are interested in improving in Excel.

Must now replace SUMIF() with SUMPRODUCT() in a couple of recently-developed workbooks...

Thank you for explaining.

That is reassuring - not really considered the backward compatibility angle.
As for the finer details of SUMPRODUCT obviously more practice is required on my part. I am sure such a useful function will crop up in one of your classes (I be sure to pay extra attention)

Oli

KimC asked for an example of sumproduct in use:

I like the article at: http://www.xldynamic.com/source/xld.sumproduct.html#top

I had not been there in a while (before reading this article) and it appears that the info has been updated significantly.

I like to use sumproduct to summarize monthly mortgage payments of P + I + fees + prepayments, etc. - into an annual mortgage payment schedule.

This is a great post, but it leaves me wanting more. Im particularly interested in your 'finding the nth instance of something' implemented.

The other one that comes to mind is the problem of maxif and maxifs - since neither function exists in excel unfortunately. I use a lot of sumifs and countifs, but will now start shifting to sumproducts for those. But i want to find the maximum point in a set of data given some and / or criteria. I've had a crack at developing a UDF, but it was too cumbersome, and after rereading this post i feel like the answer might be in sumproduct.

Any ideas?

Jesse Warburg (from Excel Academy)

The SUMPRODUCT function has been used in more and more spreadsheets since I read this post a couple of months ago. Now reading it the second time thru the Academy and I am finding things that were missed before, such as the build-in error checking function in the example.

One question: what are the excel native functions? Yesterday, I was trying to use the MEDIAN function inside SUMPRODUCT. It did not work and the work around I came up with is not concise at all. Anyone with suggestions?

-Luke Liu (student of EHA)

Great post, Daniel. Info on distinction btw SUMIFS and SUMPRODUCT was very useful as well.

Thanks Terry this was very useful. Between this link, Excel Hero and Chandoo I get it all now :-)

Time to practice and see if I can get rid of some pivot tables!!

Absolutely a fine post Daniel. Thank you. To what you have said already (November 2, 2010 7:41 AM and elsewhere) it might be worth to add that SUMPRODUCT works with closed workbooks. I made a small example based on your 3 column example in that I changed column 1 to A, B, C, D, E, F, A, B, C, D, E, cleared column 3 and saved the workbook as Daniel_1. Then in a new workbook I entered:

=SUMPRODUCT(([Daniel_1.xls]Sheet1!$A$2:$A$12="A")*([Daniel_1.xls]Sheet1!$B$2:$B$12))
and
=SUMIF([Daniel_1.xls]Sheet1!$A$2:$A$12;"A";[Daniel_1.xls]Sheet1!$B$2:$B$12)

Now close Daniel_1. In the workbook with the two above formulas, select the cell with the SUMPRODUCT formula, press F2 and then Enter. Now try exactly the same in the cell with the SUMIF formula.

Hans

Great post - have not used SUMPRODUCT before but certainly will going forward
Thanks Bruce (EHA student)

@Jesse Warburg. You could do this with an array formula such as {=MAX((Product="Apples")*Sales)}
which would return the biggest sales figure for Apples.

Great Post -

Anyone want to walk through the prime test formula?

=SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("1:"&INT(A1^0.5))))=0))=1

Thanks - Greg S.

I recently used SUMPRODUCT to total payments made in each month of the year, subtotals not being practical in this situation. Very useful and timely information, thanks again !
(EHA student)

Another great post. I totally get it. Of course, right off I can't think of any places in my existing workbooks where I can use it, but I will be reviewing to see.

One way or the other, it's nice to have another arrow in my Excel quiver.

As always, a well written, informative post. I wouldn't have thought to look at this function for half of what it can do.

Fascinating stuff. It would be very interesting to dissect the formula above for identifying primes: =SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("1:"&INT(A1^0.5))))=0))=1.
Ed Fry
EHA student

Great elucidation of the robust SUMPRODUCT function. Thanks, Daniel!

A few people have asked to know how the Prime Test works, so I am sending an explanation to everyone at the academy!

Regards,

Daniel Ferry
Excel Hero Academy

I discovered these uses of SumProduct a couple years ago, and they were absolutely the most significant enhancement to my Excel usage in years. My thanks to all Excel expert users who make this stuff known as Microsoft seems to want to keep this functionality a secret.

Fascinating!
I never thought we could use SumProduct that way.
(EHA Student)

Got to learn SUMPRODUCT last week from chandoo's excel school and now this one takes it one step further.... Have to test all other capabilities you have mentioned here

Sriram
Excel Academy student

Must have read this a few times now over the last few months!

I do like this function, and always seem to use the last one in Daniel's list (could never get my head round the double negative one for the trues & falses!)
However I do use DSUM() if I have large tables & use in conjunction with Data Validation. But only if a pivot-table is not applicable.
Lovely & versatile function.

Excellent post. Loved the Prime Number method. Now I need to go away and think about how to avoid divison by already known primes :-)

Iain

Never used it before but looking forward to it :-)

-Harold

Very nice summing up!

I'm using sumproduct for quite a while already, it's really powerful. Used it in some cases to emulate a pivot table.

Tim

Daniel,
Thanks for the message explaining the primes test. The part I don't quite get is:
"INDIRECT("1:"&INT(A1^0.5)) ... creates a range reference ... "
Can you elaborate on where and how the range reference is created?
Ed

I have been using SUMPRODUCT for years like this as far as I'm concerned its the swiss army knife of functions - if I was going to be stuck on a desert island with one function it would be SUMPRODUCT.

Its amazing that this use of SUMPRODUCT is not in the Excel Help system.

Neale Blackwood EHA

I've never used sumproduct before. After reading this article, I'm kicking myself for not using sumproduct before!

Thanks for the wonderful explanation.

Daniel,

I created a complex spreadsheet with thousands of rows and found that extensive use of sumproduct made the spreadsheet large, slow, and unstable. I reengineered the spreadsheet using getpivottable with much better results. Can you talk more about the alternatives to sumproduct?

Thanks,
Matthew

Excellent article.....

Interested in learning when sumproduct becomes unmanageable due to performance issues. From my experience I have had either too many records or more likely the need for too many sumproduct formulas.

I have tended to go down the pivot table path in combination with the getpivotdata function to present the data.

Cheers,

Michael

Excel Academy Student

Daniel,

You have a way of making potentially complex subjects simple to understand. Thank you for that. I did some basic sumproduct work awhile back but it was very confusing to understand. This certainly provides some clarity to it. I especially appreciate the conversion of SQL statements using sumproduct. I will use this!

Janice (EHA)

I first learned Array processing from my study of John Walkenbach----I use his approach out of habit.

{sum((Customer="Hansen")* OrderPrice)}

If I use SUMPRODUCT I use a similar syntax
SUMPRODUCT((Customer="Hansen")*Order Price)
This easily lets me know the conditions that I am defining and the last Named Range is what I am summing.

I always wondered which one was more efficient--SUMPRODUCT 10%---now I know.

For the beginner this is the most important concept to understand
The one overriding requirement is that the arrays must be rectangular, contiguous, and of the same dimension. So if Array 1 is a column of 29 elements (as in the above image) then so must be all of the other ranges.
When I first learned I wasted several hours before this became apparent.
Also using the Evaluate formula and watching how array processing "works" reveals the mystery.
I wish this post was available to me when I was first learning array processing.
Rick

When I read this post for the first time several months ago it was while not life altering, at least game changing. I feel I understood for the first time what sumproduct can really do.
Scott Wiltshire - Academy homework

SUMPRODUCT is my favourite in Excel, very powerful and lends itself to creative solutions. I came to it after being frustrated by speed of SUM ARRRAYS via http://www.meadinkent.co.uk/xlsumproduct.htm

I started using SUMPRODUCT after buying "In Business with VBA for Excel" in 2005.
My weakness is in not using Name Formulas.

Excellent article - looks like a very useful tool.

I had heard Chandoo sing the praises of the sumproduct function before, but I never could imagine why or how it could be useful? I can see clearly now :) I've got a lot of optimization to do on old workbooks.

I've noticed lots of people mentioning pivot tables in their comments. While I have a general idea of what they do, I'm embarrassed to say that I never use them. Am I missing something? I don't use tables either ;) I think I perceive them as being for "noobs," lol.

I've used array formulas to emulate SQL queries after reading John Walkenbach's book, but I've never used Sumproduct. Spent a lot of time fixing array formulas because I forgot to Ctr-Shift... Another great exposition of how Excel functions. thanks.
Mark Voge(EHA)

Excellent article! I have been using Sumproduct for a while but never realise that I could have used named formulas instead of direct cell references.Thanks Daniel.

M Rettenberger (EHA student)

I also did use SP in the past, but not in the way described here.
insightful article, thanks!

EvE@EHA

Thanks for another good read Daniel!

Re some postings above, why use SUMPRODUCT in favor of a Pivot Table? Is it a question of efficiency?

Paula (EHA)

Welcome back, Paula!

I hope V. was spectacular!

SUMPRODUCT is an amazing function. But in a speed test it will always lose to a Pivot Table.

However, it can do things that PT's cannot. One example from thousands would be the Prime Number Test.

But beyond those capabilities and looking solely at data mining applications, the SUMPRODUCT can be extraordinarily useful for interactive reporting where VBA is not allowed. To get a PT to update requires at least one line of code. SP requires none.

Regards,

Daniel Ferry
Excel Hero

I don't use SUMPRODUCT nearly as much as I should. This have given me some inspiration in using it for filtering data.

Mark (EHA)

I've now read this a few times and have used sumproduct on occasion once suggested after I sought help from others.

I'm just not yet to the point where using it is reflexive and natural to me.

Seeing the usages detailed in this article has helped me better grasp what is going on with boolean logic compares for data-mining. Now I get it.

Sometime I would like to see examples of the interesting results to be had by use of SumProduct for conditional formatting which were alluded to above.

-JC (EHA)

The first example I could reproduce on excel.
=SUMPRODUCT(A2:A30,B2:B30,C2:C30)

the logic is well explained but as soon as you got into
=SUMPRODUCT((OrderPrice),--(Customer="Hansen"))
to
=SUMPRODUCT((OrderPrice)*(Customer="Hansen"))
I could not make it work in excel. perhaps you can tell the uninitiated how to set up ranges or point us to places to look

as for the other members suggestions sumif, maxif, dsum are these all harder to use hence we are being pointed towards sumproduct, or are they easier to use for simplier examples but more complicated for larger data lists?

stumbling towards herohood

Read and understood, very interesting, as usual.
Andrea

I have been using SUMPRODUCT for few years, it's excellent!
Since I know for it, I don't need any other function for data mining.
Once I have done complete application reporting with it, without any VBA or Pivot tables with data up to 5000 records. With more data things could get slower...
Daniel, is it just the SUMPRODUCT stopping you from using OpenOffice? I would say that the main reason could be VBA, or am I wrong?
Thanks for the great post.

Drazen (EHA student)

Hello, can someone please help with this real-life scenario: Col-1 contains monthly salaries for different jobs (15 rows); and columns 2-13(for each month of the year) that shows how many people I have for each month for each job. I want to have a single cell that shows the value of: [Cells(1,1)*sum(Cells(2-13,1)) + Cells(2,1)*sum(Cells(2-13,2)) + ...+ Cells(1,15)*sum(Cells(2-13,15))]

What's the best way to do this, without having to create an intermediate column?

Thanks a lot in advance for your help!

i like your work and your blog,i found it very useful.thanks a lot!

Daniel,

yesterday I got rid of a beautiful PivotTable, thanks to Sumproduct. But found myself in a problem.

I have set up a list of items as a named range (Profit_centers), which I use to populate another named range(Selected_PF)using validation.

I have a series of sumproducts
(=SUMPRODUCT(Rev_Budget_QTD;--(Rev_Profit_Center=Selected_PF);(--(rev_Quarter=C$5&"-"&RIGHT($F$1;2))))

that work perfectly for each item in Profit_centers, when selected in selected_PF.

My problem is that I don't know how to create a single item for Profit_centers (namely "All") that, when selected, tells the sumproduct formulas to select all Profit_centers, instead of one.

any ideas?

as always, thanks for your help.

Martin

@Martin
Take the profit centre section out, that way it is selecting all profit centres as it isn't limited by that constraint
(=SUMPRODUCT(Rev_Budget_QTD;
(--(rev_Quarter=C$5&"-"&RIGHT($F$1;2))))

I have read it twice now - once before joining EHA2 and now - having watched the fifth module... I must say that my understanding of the text is way better now - and it seems like a great way to move at least some of the newly acquired knowledge into the long-term memory!

Cheers

Marcin "Kuty" Markiewicz
EHA2 student

I agree that the SUMPRODUCT is a must function to master. Several years ago, I took on a project to speed up a PNL statement for a bank I was working at. The workbook was horrendously slow...took over 20 minutes to recalculate when a value changed. It was riddled with improperly used array formula's.

To make a long story short, I took ample advantage of the SUMPRODUCT function and, along with other enhancements, reduced recalculation time to under 1 second.

Great post Daniel.

-Chris
EHA2

Excellent article on the use of SUMPRODUCT. I have been using it this way for years, but you have pointed out some new tricks, and I now have a better understanding of how to optimize it.

Bob N.
EHA2 student

I’ve used SUMPRODUCT in the past. Now I have a better understanding, I will be using it more often. -- ajp

Great info, and definitely thought provoking! Now starting to grasp the possibilities, I am very anxious to learn how to use SUMPRODUCT to do a multi-column sort by formula.

Bryan
EHA2


Great post Daniel. Thanks for the wealth of information as well as helping me understand the similarities between SQL statements and the SUMPRODUCT, stressing the AND/* & OR/+ operators.

Calvin
EHA2 student

I can handle this homework assignment. Now I think I actually understand sumproduct. Before I was using it without really understanding it.

Thanks for the excellent explanation of SUMPRODUCT - I'd only used it for fairly straightforward purposes in the past. However, I'm stuck on the same issue that it looks like transly (whose post doesn't seem to have been answered) is stuck - namely, how best to incorporate Excel's built-in scalar functions to SUMPRODUCT criteria. For example, I have three columns:

H: item quantity
P: date a payment was applied
U: amount remaining on invoice

I want to sum the item quantity if the payment month is a certain value and the amount remaining is zero. None of the following formulas work:

-- sumprod w/ array inside month() (doesn't work b/c passing array to scalar fn)
=SUMPRODUCT(H2:H15, MONTH(P2:P15)=5, U2:U15=0)

-- sumifs w/ empty month() call (doesn't work b/c passing no param to scalar fn)
=SUMIFS(H2:H15, P2:P15, "month()=5", U2:U15, "=0")

-- sumifs w/ conditional formatting reference type (doesn't work; I thought it might b/c conditional formatting takes a ref like $P2, where 2 is first row in range, and adjusts the row num as it evaluates the formula for each row)
=SUMIFS(H2:H15, P2:P15, "month($P2)=5", U2:U15, "=0")

Obviously I can create a separate column to store the month, but this comes up a lot for me, so I figured I'd ask the experts to see if I'm missing something. If it can't be done via a formula, perhaps there's another trick that obviates the need for an extra column?

Thanks in advance, and again, great post! This is my first time at Excel Hero - looking forward to digging deeper.

John

@jrschroe,

Try this:

=SUMPRODUCT( (MONTH(P2:P15)=5)*(T2:T15=0), H2:H15 )

Wow - I've used sumproduct before, but, I see now, only in a very amateurish way :(

After watching the M05 video in EHA2 this post has gotten a much deeper meaning for me.
The formula for prime numbers is brilliant: First getting sumproduct to calculate boolean values by multiplying with one and then forcing the true/false answer with "=1".

Thanks
Michael Rapp
EHA2

I had no idea that you could do so much with this function. Definately requires more reading.

DantheMan
EHA2

Enlightening as always... and leaves me with a desire to use SUMPRODUCT somewhere, anywhere... right now!

Bijoy Mathew
EHA2

Excellent article. Obviously I'm not devious enough to get all the power out of Excel ;-)

EHA2 student watsonm

Great article, thanks Daniel. Already read months ago, but better understood this time.

EHA2 Homework
Geoff Beals

Juanito says hello!

EHA2

Thanks Daniel, I'ved used SUMPRODUCT before but only in the basic summing of two multiplied ranges. The combination with boolean logic opens up many possibilities.

David Binns
EHA2

Done.

EHA2 Homework
Tim McCollough

Great stuff - will certainly be considering it in future projects.

John Broggio (EHA2)

Hi Daniel!!!

This SQL approach was just perfect to elucidate this powerfull SUMPRODUCT formula. I had no idea this formula could do this that easy way, even having some experience with SQL.

Regards,

Rodrigo Bertin
EHA 2 Student

Hi Daniel,

Good post in combination with module 5 lectures
I've been using sumproduct more often over the last few weeks, without realy understanding what I was doing. It's good to know why and how it works.

Lorette (EHA2)

Just working throught the homework. I use sumproduct every single day, so am very familiar with it. In my data sets it may appear several thousand times. I believe it is 'volotile though, and causes ANY action on a sheet to recalculate the whole shooting match. Looking forward to learning (if it is possible) to addess this issue

EHA3 homework. Read it again, great article thanks!

Great Article Daniel, I have been using Sumproduct for years now and I feel it does have one down fall: Sumproduct is a volatile function. I have one spreadsheet with numerous complex sumproduct functions and I often turn off auto calculate. It only takes a few seconds to calculate but annoying non the less.

I would like to find a method to create a UDF that would calculate the same thing as SUMIFS and SUMPRODUCT and will be non volatile.

-Brent

Finally I learned how to make OR comparisons without repeating twice xIFS functions.
Thanks Daniel!

I certainly learned something new here. Thanks Daniel :)

CHol, EHA3

I have read this one several times and I'm still learning from it!

KenU, EHA3

Extremely useful and interesting article.

Nimir, EHA3_HW_5

Extremely useful and interesting article.

Nimir, EHA3_HW_5

EHA 3 HW

Done!

Vito Jr

I use sumproduct in many models and have a good understanding. This article, plus the explanation in the EHA video, will be very helpful when teaching others. Clear and easy to understand.

EHA3 Student

Finally I get the "--(" bit. I thought it was some weird syntax I could never get right.
John

EHA3 student.

Great article. Read it waiting to get into the Academy :)

Thank you,

Greg Jury

Understanding this funtion really helps to unlock the potential of using a single function to conduct many different types of calculations/analysis and should be applicable to many of my spreadsheets.

Hi Daniel,

Great info on sumproduct. I didn't pay attention to it until now. It's quite amazing function.

I wonder how

"=SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("1:"&INT(A1^0.5))))=0))=1" work as well. I saw some posts asking you the same question and you send them explanation.

Will you also send it out to the EHA3 or just that I missed it?

SiyingRuan

I always wanted to learn the sumproduct formula in detail..........watched the video....truly amazing formula and great work by Daniel in demystifying it....excellent article and very informative posts by so many excel users....there is a wealth of information....on these posts....

Mohammed Mustafa
EHA3 student

Hi Daniel,
i have used SUMPRODUCT for many years but inconsistently as I never understood the working behind it.
Your article here, and the Excel Hero video are excellent explanations and should make my work better, as well as opening up new possibilities.
John
EHA3 student

This seems like a much simpler, more elegant, and more flexible way to write conditional statements.

John
EHA3 - ndarmyserver

This covers EHA3 homework 4.
By the way, I have been using SUMPRODUCT for a while since Excel 2003 doesn't have SUMIFS, and I understand the logic, now it's more clear with it's direct relation to the Conjuction Truth Table - AND.
Thanks,
Pablo

Thanks for the explanation, SUMPRODUCT will be very useful.

Thank you!
Returning as an active EHA 3 student...
pj1001

Thank you!
Returning as an active EHA 3 student...
pj1001

This is good stuff, really usefull

John
EHA3 student

Daniel, this blog post, combined with the lecture video, has caused me for the first time ever, to understand this remarkable function. I had glimmers before, but this really drives it home...thank you.

I'm looking forward to a big reduction in my use of complicated logical formulas.

Very good concise article Daniel.
I use SumProduct a lot - but very rarely with booleans - that may very well change! Thanks

This reading is great reinforcement after the video. I have one particularly complex array formula at work that I can't wait to replace!

Very informative.. I will be using this info with my work instead of always nested IF's

With my previous limited understanding of sumproduct, I have shied away from its usage. After the video lecture and this acticle, I am now not as itimidated by it.

With my previously limited understanding of the SUMPRODUCT function, I have found ways to work around its use. The video lecture and this article have greatly increased by understanding.

With my previously limited understanding of the SUMPRODUCT function, I have found ways to work around its use. The video lecture and this article have greatly increased my understanding.

SUMPRODUCT for the win!! I recently stumbled upon this function and it is indeed very powerful. And I have only scratched the surface I'm sure. This one is at the top of my favorites list!

Yes, I had started using SUMIFS and the COUNTIFS but your points about them only allowing "and" is very relevant. Also curious to know what you would suggest in situations where people have new data every month - so they are adding to the list continually...dynamic ranges and the criteria set up in particular fields (I presume then the SUMPRODUCT would refer to that named range). I really like the flexibility of the SUMPRODUCT - and I think I might teach it more in class (I've been using the SUMIFS/COUNTIFS more so). Thanks for the great info. Lovin the Excel Hero --(ine) :-) Academy

This is going to be a huge help. Thanks

I've used SUMPRODUCT a fair bit in its most basic form. This is very useful additional information as I had previously seen the double negative used in SUMPRODUCT formulas without actually understanding why.

I don't really agree with reason 2 in the video about SUMIFS and COUNTIFS. These formulas are very easy to read in my opinion especially if the range from which the data is sourced is named. Certainly easier to show an Excel user with basic knowledge.

Bermir was here. Besides the additional sumproduct clarification, I really enjoyed the link to animated charts. I did not know Excel could do that and now I am happy to know that it can...

I originally used sumproduct to calculate weighted averages. Thanks for taking this formula to the next stage for me.

I originally used sumproduct to calculate weighted averages. Thanks for taking this formula to the next stage for me.

This was very interesting. Leia

I have used SUMPRODUCT for years for summing products, but not to mine data. Thanks for the additional information.

Thanks. Great Article. Will be using this function much more in the future. Larry Jensen

Thanks for the article Daniel. I'm very keen to learn more about sorting using SUMPRODUCT.

Again an interesting blog post. Always good to learn new uses for "old" functions.

Looks like a nice recap (with examples) of a lot of what we saw in the video....then more!

Michelle

Thanks for the article.

I'm interested in how this stacks up to using ADO. I'm not sure if that's addressed in the EHA class, but I make extensive use of SQL using ADO, and I'd be interested in seeing if SUMPRODUCT might be better in some cases.

EH5 Homework check-in

I'll need to use this function more. Thanks, great article.
eduardotk
EHA5 student

Great Article - thanks

EHA5 student - Thanks, a very useful technique.
Douglas

EHA5 Student, very useful info. Thanks!

Very interesting article! Thanks

It's amazing, i've never thought all this could be possible

Thansk, really useful

EHA5

Great supplement to the EHA Module 5 data. I will certainly be incorporating this function into more of my spreadsheets.

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on January 21, 2010 12:16 PM.

I Heart IF was the previous entry in this blog.

5 And 3 Is 1 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.