Advanced Sumproduct Queries
The use of the Sumproduct function for doing multiple criteria Sum If’s is possibly one of the greatest extensions of an Excel function beyond what it was primarily designed for. Maybe it was actually designed with that in mind ?
However Sumproduct can be extended even further through use 2D Ranges together with carefully constructed queries.
The examples below are included in the Example File, Excel 2003 Example File.
Scenario 1: Lookup a value within a 2D Range matching 2 criteria
You have a table of Dates and Fruit Sold and Number Sold each Day
How many Bananas did I sell on the 4^{th}May?
In the above I have setup 3 Named Ranges
Named ranges are used as it makes the reading of forthcoming formulas easier.
Fruit: C2:H2
Dates: B3:B12
FruitData: C3:H12
So, How many Bananas did I sell on the 4th May?
Using the equation =SUMPRODUCT((Fruit=D16)*(Date=D15)*FruitData)
Returns the correct answer 31
Related: Doing 2way lookups in Excel
Scenario 2: Sum all values within a 2D Range matching 2 criteria
You have a table of Dates and Cars Sold and Number Sold each Day. There are multiple entries for on various days, possibly from various salesmen.
How many Holden cars did I sell on the 3^{rd} May?
So, How many Holden cars did I sell on the 3^{rd} May?
Using the equation =SUMPRODUCT((Dates=D17)*(Cars=D18)*CarData)
Returns the correct answer 9 = (1 + 5 + 3)
Scenario 3: Sum values within a 2D Range matching multiple unordered criteria
You have a table of Dates and Cars Sold and Number Sold each Day, There are multiple Entries for on various days.
How many Ford and Suzuki cars did I sell on the 10^{th} May?
So, How many Ford and Suzuki cars did I sell on the 10^{th} May?
Using the equation =SUMPRODUCT((Dates=D24)*((Cars=D25)+ (Cars=E25))*CarData)
Returns the correct answer 13 = (4 + 5 + 3 + 1)
Note that this can be extended to add additional queries where the Car Type can be entered in any cell in the Range D25:H25
=SUMPRODUCT((Dates=D24)*((Cars=D25)+ (Cars=E25) + (Cars=F25) + (Cars=G25) + (Cars=H25))*CarData)
Scenario 4: Sum values within a 2D Range matching multiple ordered criteria
You have a table of Dates and Cars Sold and Number Sold each Day, There are multiple Entries for on various days.
How many Toyota and Holden cars did I sell on the 10^{th} May?
How many Toyota and Holden cars did I sell on the 10^{th} May?
Using the equation =SUMPRODUCT((Dates=D30)*(Cars=D31:H31)*CarData)
Returns the correct answer 21 = (3 + 6 + 6 + 6)
Note that this can be extended to allowing additional queries but the Car Type must be entered into the same position as in the Header Row.
How Does This Work?
The above techniques is using matrix arithmetic to setup a conjunctive truth table within the Sumproduct formula.
Using =SUMPRODUCT((B4:B6=D10)*(C3:E3=D9)*(C4:E6))
The conjunctive truth table logic (B4:B6=D10)*(C3:E3=D9) is simply saying make a matix of elements that are true when the conditions are met and false otherwise
Sumproduct then takes this and multiplies and it by the data values and accumulates the values to get the sum of the matching values.
It is important to note that the Width and Height of the Criteria Row and Column must match the Width and Height of the data area or a #Value! error is returnd.
The Maths
To understand and explain how this works I will use a simple model with 3 rows and 3 columns see below
The formula: =SUMPRODUCT((B4:B6=D10)*(C3:E3=D9)*(C4:E6)), shown above consists of 3 areas
(B4:B6=D10) is a 3 Rows x 1 Column range
(C3:E3=D9) is a 1 Row x 3 Columns range
(C4:E6) is a 3 Row x 3 Column range
Breaking the formula into components
=SUMPRODUCT((B4:B6=D10)*(C3:E3=D9)*(C4:E6))
(B4:B6=D10)*(C3:E3=D9) is the same as multiplying 2 arrays, representing the 2 areas as shown below
You can see that where the components are True I have put a 1 and a 0 where they are false
Where the Date was 3May Excel evaluates this to 1 and similarly where the Fruit was a Banana, Excel evaluates this to 1.
Where the criteria isn’t met Excel evaluates this to a 0
The multiplication of a 3 x 1 and a 1 x 3 array is a 3 x 3 array
Representing the (B4:B6=D10)*(C3:E3=D9) part of the equation
Next this is multiplied by the data area
=SUMPRODUCT((B4:B6=D10)*(C3:E3=D9)*(C4:E6))
This is the same as multiplying two 3×3 arrays which produces a 3 x 3 array, below:
Sumproduct then adds up all the array components to get the final answer of 3.
Modifications
The Data Area can be included in the Truth Table Logic or as a seperate component of Sumproduct.
=SUMPRODUCT((B4:B6=D10)*(C3:E3=D9)*(C4:E6)) and =SUMPRODUCT((B4:B6=D10)*(C3:E3=D9), (C4:E6)) are both equal
Multiple “OR” crietria can be added by use of the+ operator within criteria
In Scenario 3 above, we sum the number of Ford or Suzuki cars sold on the 10th May.
SUMPRODUCT((Dates=D24)*((Cars=D25) + (Cars=E25) + (Cars=F25) + (Cars=G25) + (Cars=H25))*CarData)
The Or logic is added to the criteria by use of the + operator above within the criteria for Cars
the And logic is added by use of the * between the Dates and Cars criteria
Other Logic Elements
You can add Greater Than (>), Less Than (<) etc and other logic elements to the queries to suit your requirements.
Sample File
The examples below are included in the Example File, Excel 2003 Example File.
What do you think of the above technique ?
What do you think of the above technique ?
Let us know in the comments below.
More Tips & Resources:
 Introduction to SUMPRODUCT formula
 Introduction to SUMIFS formula
 More on SUMPRODUCT formula – tips & examples
 
 

Leave a Reply
Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]  How Would You Visualize Product Sales Data? [Excel Challenges #2] 
73 Responses to “Advanced Sumproduct Queries”
Hi Chandoo. There appear to be a few errors in this blog mate? Some of the charts don’t match the formulas. And stuff like
“Using the equation =SUMPRODUCT((Dates=D24)*(Cars=D31:H31)*CarData)
Returns the correct answer 15 = (3 + 6 + 6 + 6)”
No it doesn’t… That equals 21
Plus your image shows cell D30 instead of D24. Must be something to do with the hidden rows when you were creating the worksheets?
Might be worth just checking the other stuff too as I didn’t have time to go through those.
Anyway, love a bit of SUMPRODUCT me, so keep up the fantastic work on your site
Ooh, incidentally, SUMPRODUCT((Dates=$D$24)*(Cars={“”,”Toyota”,”Holden”,””,””})*CarData) also works. Not sure when you might use it, but hey!
I couldn’t download the files either Chandoo?
Hi Chandoo,
using the advanced features of the sumproduct function is a huge advantage in daily business! – cool > as you can see as well on http://www.excelformeln.de (well, you would need some german knowledge)
Stefan
@Lee
I should hire you to proof read for me.
I have fixed those few errors, thanx
Both files are downloading ok for me ?
I get a “Sorry, but I cant find the page you are looking for” message, Hui?
@Lee
Should be fixed now?
Yep, that’s working now. Thanks!
Hi guys, just wanted to say this has to be one of the most useful blogs out there on excel. I’ve been reading this blog for exactly a week now (subscribed, RSS), I check this daily now for tips on how to manipulate data. Big thanks to Chandoo, Hui et al.. Love the articles and find the comments quite helpful too. I will most definitely do the VBA, dashboard and excel classes later on during the summer.
Nice job Hui. Excellent break down of how SUMPRODUCT can be used, and how it works (rather than black magic). Will probably refer to this often in the forums…
Chandoo,
congratulations on a great post. Let me also add that using sumproduct in this way, is pretty much the same than using pivot tables, with an extra benefit: when you don’t change the layout of the Pivot Table, and only change the values for some field (eg, one you set a page field in a PT), you can use the info on the table to create a dynamic chart, WITHOUT LOSING THE FORMATTING ON IT !!
I can’t recall the number of times that I’ve formatted a PT Chart, only to find out that changing/refreshing a value resetted every format applied !!!
hope this helps. Thanks !
With this approach you are getting in what is called array formulas. The same will easily work if you put it as just “=SUM(Dates=D24)*(Cars=D31:H31)*CarData)”. The trick is to enter this formula with Ctrl+Shift+Enter combination, as a result the formula will display like”{=SUM(Dates=D24)*(Cars=D31:H31)*CarData)}” in the formula bar.
I myself like array formulas very very much. They are so much better than the Pivot tables. Much more flexible at the least
Continued…
As an example here is what you can do with the array formula:
1) Weekly sales report for all car brands
=SUM((Weeknum(Dates)=20)*CarData)
2) Average daily sales per week for all car brands
=AVERAGE((Weeknum(Dates)=20)*CarData)
Week #20 was taken as an example. All the above formulas need to be entered with Ctrl+Shift+Enter.
Hui Great explanation and nice walkthrough of all the logic. You can do so many things with this formula.
@Hui.. excellent work on this article. Really a must read for anyone aspiring to use Excel for advanced data analysis and reporting.
@All.. please note this article is written by Hui…
Great post! I have used this a couple of times, but never really known how it works. Excellent explanation.
The sumproduct formula is fantastic. The link below is another great explanation of its varied use. It goes into a bit more detail on the different formats that you can use within the formula. I.E. double unary operators, using the N function, and even raising to the power of 1 (^1). http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Excellent tips! Thank you very much for the great implication, Hui!
Using Scenario 1 I was able to develope a way to do the following:
1. Number sold on a single product within a range of dates. Eg. Mango 4 May to 9 May is 216
2. Multiple fruits sold within a range of dates. Eg Mangos and Oranges 4 May to 9 May is 430!
And here is the question I can see my boss asking:
Can you set it up in such a way that when I select, say Mango between 4 May to 9 May the figures would turn into red and the cell hilighted or borderd??
I love sumproduct but have a hard time in implementation.
For instance, am trying to find the row for any set of data that contains a special character like a single quote.
If i put text in a1:a4 with a4 having a single quote in the text, using this formula,
=SUMPRODUCT(NOT(ISERROR(FIND(single quote,A1:A4)))>0*ROW(A1:A4))
Thoughts?
This gives me Zero instead of row 4.
What is the matter with the following functions
=DSUM
=DAVERAGE
=DCOUNT
=DCOUNTA
Name the table
and perform the =D functions against the table name. I think you are overthinking this.
I never really understood how valuable sumproduct was until Chandoo’s example of the 1s & 0s (trues to falses) was displayed as he does above. It was in a different post, but, this post is just plain awesome as well. Love the sumproduct!
My immediate thought was to put a row of checkboxes above the fruit columns, link them to the cells the checks are sitting in, and do a variant that includes those fruits that have been checked.
For the conditional hightlighting, Fred, you’d have a formula in the conditional formatting for the data that would make each cell check itself against the criteria. The first example above, for instance would be (for cells c3:h12) something like =and($b3=$d$15,c$2=$d$16)
Your modification using a date range for selection would be a little more complex, but not bad.
I’m in love with SUMPRODUCT, my heart races every time I see her, and I cant bear it when we are apart No, but seriously, learning how this formula works was a life changing moment for me. You’ve explained things really well here Hui – great work!
thanks! great topic, great treatment.
Hi Chandoo,,,thanks for sharing ur knowledge..It is very helpful…..Hope u r very much satisfied by sharing this kind of info..
Regarding Scenario 4, also note that should you wish to make the results a little more versatile and show the sum of all dates if the date in the query field (D30) is left blank, you can do this.
It makes SUMPRODUCT even more flexible!
Use the equation
=SUMPRODUCT(((Dates=D30)+(D30=””))*(Cars=D31:H31)*(CarData))
instead of
=SUMPRODUCT((Dates=D30)*(Cars=D31:H31)*CarData)
What a life saver this has turned out to be!
I have recently been spending several OT hours trying to work out complicates sum/match/if statements. While I’m sad I wasn’t so educated 2 weeks ago, this will most definitely save me much work in the near future.
THANKS.
@Chandoo: thanks for pointing out the author !!
@Hui: as always, a terrific post !!
Let’s raise the bar a little: suppose that I have more than a month info, and I want the total for Toyota for, let’s say, June the question applies for a subset of may as well, e.g.: the first 4 days of may.
How should I construct the SUMPRODUCT query?
Create a table from B2.G14 and name “TABLE”
In cell B2 enter the name DATE
In cell B29 enter the name DATE
In cell B30 enter >7May
In cell E30 enter this formula =DSUM(TABLE,3,b29.b3)
Results are total for Toyota (Column 3) for a date greater than 7May.
I seem to be having a problem in the implementation.
My condition checks itself as True before comparing with data.
B7:B10 = Data in either Pounds or USDollars
“C2″ = US Currency in Cell “format” formula.
=SUMPRODUCT((B7:B10)*((CELL(“format”,B7:B10)=”C2″)*1))
this gives me the sum of all data not the ones where the currency format = “C2″ (us dollars)
What is the fix to my formula issue. Thanks in advance!
Just a quick comment, matrix multiplication is not commutative meaning A*B B*A. So with this specific example you have to put the column first in the SUMPRODUCT then the row. If you did not you would be multiplying a 1 X 3 by a 3 X 1 leaving a 1 X 1 matrix behind. In this example it would be 0. In other words, the order you have the arguments in SUMPRODUCT matters.
@Ryan
Although you coment about cummutative meaning of A*B B*A is mathematically correct,
in Excel =(C2:C5)*(D6:F6) array entered into a 4 x 3 area returns the same result as =(D6:F6)*(C2:C5) array entered into a 4 x 3 area
Hi Chandoo, first thanks for you incredible page, due it page I could learn a lot things that used every day at my work.
I woudl like to show you a daschboard that I created, obviously to the knowledge that i got from you.
Let me know if exist a posibilty to send my excel file.
Again Thanks a lot.
Bless you..
@William… Please email your file to chandoo.d @ gmail.com
it’s very good. the thinking behind it is excellient and the way it is implimented is very good and appreciable
I love how you can just do anything using Excel.
I could honestly say, everything I learned about Excel was from this website.
I tried doing the same equation slightly different (just playing around) and it worked.
I changed the second multiplication sign with a comma.
=SUMPRODUCT((B4:B6=D10)*(C3:E3=D9),(C4:E6))
[…] Advanced SUMPRODUCT Queries in Excel […]
Excellent!! I’m finally catching up on my RSS reading and I’m so happy I did. Like others, I have used this but never really quite understood how it works – I do now!! Very simple explanation – Hui – THANKS!!
I have a monthly report I need to run that tells me which person worked on a certain date and if the date is a weekday or weekend.
I have a range of cells that contain blanks or one of four different initials. I’d like to search the range, then count up how many of a specific initial appeared in the range. For example if I have VG, KA, AK AND MC as the initials in a set range, I’d like to be about to show how many of each initial appeared in that range. I’m not very good with Code, so I’d like it to be an excel formula. Is there a way to do this? help help. LOL.
I wasn’t sure if there was a way to attach a sample spread sheet so I’ll try to creat it here.
Date UH Day
MONDAY 1 VG
TUESDAY 2 MC
WEDNESDAY 3 KA
THURSDAY 4 KA
FRIDAY 5 KA
SATURDAY 6 MC
SUNDAY 7 MC
MONDAY 8 VG
When I’m done, I need to show a simple report like this:
MONTHLY DAYS’ REPORT
CATEGORY VG MC KA AK
UH Day 8 7 8 2
UH/VA night 8 5 5 7
Backup Weekdays #VALUE! 6 6 5
Backup Weekends 0 2 1
Weekends 1 1 1 1
Thanks. Bill
pm me with a copy of your worksheet and I’ll have a look for you Bill
larmitage at heathlambert dot com
Thank, Lee. I have.
//b
Enjoyed it very much my man!
Hui,
Excellent article. I’m fairly new to SUMPRODUCT but have started using it now to great effect in my workbooks.
Can you explain – or anybody else for that matter – why in some cases you can use commas as the separator for the arrays in the formula, and other times (as in these examples) you use the asterix? I thought the asterix was employed in cases when the arrays were of different dimensions?
Thanks a lot.
Hi Chandoo,
Need your help.
Using excel, i want to get the sum of the product of the Message Count and Ave Duration on each of the agent present on May 1, 2, 3 (See Table).
Date Agent Message Count Ave Duration
1 May 11 A 100 25
1 May 11 B 120 26
1 May 11 C 140 27
2 May 11 A 105 28
2 May 11 B 115 29
3 May 11 A 125 30
3 May 11 B 110 31
3 May 11 C 120 32
3 May 11 D 130 33
Below is the solution of the problem if done manually.
For May 1
A 100 x 25 = 2,500
B 120 x 26 = 3,120
C 140 x 27 = 3,780
SUM = 9,400
Looking forward.
@Jaime
=SUMPRODUCT((A2:A100=DATE(2011,5,1))*(C2:C100)*(D2:D100))
or
=SUMPRODUCT((A2:A100=H1)*(C2:C100)*(D2:D100))
where H1 has the Date 1/5/2011
[…] Advanced Sumproduct Queries by Ken on Oct 3, 2011 • 6:51 pm No Comments Original Post Click Here […]
[…] Advanced SUMPRODUCT Formula Examples […]
I was having a hard time understanding the SUMPRODUCT logic until I read the math tables shown above – brilliant! Thank you so much for the explanation. Now makes using the formula…understandable!
@Tanner
Thanx for the comments
You will enjoy next weeks Formula Forensics !
Where have you been all my life, SUMPRODUCT?! I’m smitten.
Where have you been all my life, =SUMPRODUCT((Dates=D17)*(Cars=D18)*CarData)?! I’m smitten.
I have a problem at work i am trying to resolve and just can’t figure it out. I have a database that is for tracking labor. the headers of this database are employee name, date, hours charged. I want a formula that sums a list of 6 specific employees and how many hours they charged on a specific day. How can I do that with out having 6 sumifs formulas added together? any suggestions?
=SUMPRODUCT(–(hours_charged)*(date=DATEVALUE(“01/01/2001″))*(employee_name={“mary”,”mungo”,”midge”}))
This assumes
“hours_charged” refers to your hours charged cells
“date” refers to your date cells
“employee_name” refers to your employee named cells
Works Great!! You are Awesome!!!
I applied it to a series of dates to get a daily sums for 6 Months and it keeps crashing excel. Thoughts? the multinested sumifs formula doesn’t crash but is slow.
Can you post your formula Greg?
please somebody can tell me how to solve this: as scale example I need to substract rows among them. as analogy is for example if I have 5715304648122142
I need to substract the abs of 57, 515 etc. and after 715, 730 etc and the same with all of them. In my case I have in my workbook a tabel A1:F1900 as you see each row have 6 numbers, so I need to substract each row to the other like in the example above, any macrovbacode for this. thanks for any advise. or solution.
@Vicktor: Welcome to Chandoo.org and thanks for your question.
I am not able to understand your data. Can you upload sample data somewhere and share a link with us so that we can help you.
what do you mean upload S O M E W H E R E ?
@Vicktor
Have a read of: http://chandoo.org/forums/topic/postingasampleworkbook
A work of art, mate – very helpful!
Great explanation on a seemingly difficult to understand topic from the offset! Thank you!
I need an equation to help me sum revenue (dollars) in column D that meet criteria from x3 other columns (Opened, July and Texas). NOTE: Column D also includes several rows that include TEXT, which is what’s throwing the whole thing off.
I have followed some of the examples I’ve seen on here and other websites and although I can get my formula to return a result, it’s returning the COUNT of items in column D meeting the criteria instead of the SUM.
Here’s my formula:
=SUMPRODUCT(($K$10:$K$200=”OPENED”)*($X$10:$X$200=”July”)*($I$10:$I$200=”Texas”)*ISNUMBER(($N$10:$N$200)))
If I remove the “ISNUMBER” notation, the formula presents the dreaded #VALUE. I’ve also tried it using ISNONTEXT but that doesn’t work either.
Is “ISNUMBER” the right solution? Will it work for current values?
I’m running out of steam trying to figure this one out! Any help on this would be greatly appreciated!
Thank you.
ANSWERED:
The formula I’ve included above works for counting. I just had to change the *ISNUMBER(($N$10:$N$200) at the end to be ,($N$10:$N$200) instead to sum it. The ISNUMBER is needed to overlook any text in the column for counting. ISNUMBER is not needed when summing it. Using the * vs. the , is the difference between counting and summing.
Thanks for anyone who may have looked at this and was working on an answer.
@Kkrause
The * and , don’t switch between counting and suming at all.
Sumproduct does as its name says Sums the Products of the constituent arrays
It’s syntax is =Sumproduct(Array 1, Array 2, Array 3,…, Array n)
Sumproduct hence multiplies each array value and then sums them
So If array 1 is 1,2,3 and array 2 is 10, 20, 30
Sumproduct({1,2,3},{10,20,30}) will do 1×10 + 2×20 + 3×30 = 140
But this can also be done using
Sumproduct(({1,2,3})*({10,20,30})) will do 1×10 + 2×20 + 3×30 = 140
Sumproduct never counts but when the results of the arrays is equal to 0/1 or False/True, it has the same effect as counting
Often you will see =Sumproduct( – (range>Value)) or =Sumproduct( 1*(range>Value))
What is happening here is that each cell in the range is being compared to a value
If it is greater than the value it equates to true, otherwise it equates to false
The – – at the start converts the True/False to 1/0, which Sumproduct then adds up, effectively counting the Number of True occurrences.
I hope that clarifies Sumproduct to some extent
I was working on the similar thing and was getting the #VALUE! error. I could not figure it out for so long until i came across this blog. This in indeed very helpful and i highly appreciate it. Thanks!!
One addition I think is important: you can use the SUMPRODUCT formula to search not just by 2 conditions, but by 3 (or maybe more) conditions.
For instance, you could add a row above Oranges, Apples, Melons, etc., for instance with two names: John & James. You could then add this row to your SUMPRODUCT formula to specify that you want only fruit sold by John. (so, “how many melons did John sell on May 4?”, not just “how many melons were sold on May 4?”)
Nicely explained – Thanks
But
You could have picked better subject for “Scenario1″.
When I sum FRUIT I keep getting my DATES included.
(I’ll get my coat!)
For Scenario 3, and for adding additional Car types in D25:H25, would love to figure out if this SUMPRODUCT version:
=SUMPRODUCT((Dates=D24)*((Cars=D25)+ (Cars=E25) + (Cars=F25) + (Cars=G25) + (Cars=H25))*CarData)
Could be modified somehow to accomplish the equivalent of:
=SUMPRODUCT((Dates=D24)*(Contains(Cars, D25:H25))*CarData)
Or
=SUMPRODUCT((Dates=D24)*(Contains(Cars, CarModels))*CarData)
Where CarModels is a named range for D25:H25.
I’m not claiming there’s a CONTAINS function, but I’d love the equivalent of one. That way I could create a named range of CarModels, and then add new models *without having to add* “+ (Cars=Z25)” (or whatever the next column is) to all of the SUMPRODUCT formulas in all of the cells every time I needed to track a new model. It may seem minor, but basically it would be an easier way to maintain the spreadsheet, and I feel it would be less errorprone as it grows. This is a real problem (or maybe a nit) I have now, and it’s amazing how often I run into it.
@Doug
What this is doing is building up arrays of matching values for each car
so that you get something like:
0;0;1;0;0 +
1;0;0;0;0 =
1;0;1;0;0
I am yet to find a simpler way to do that
Although I must admit I haven’t spent a lot of time on it
[…] A formula that I just started using is sumproduct which is extremely helpful when you have multiple complex criteria to search on and works because of linear algebra so that’s really cool. For instance, sumproduct can help you do a casesensitive countif: =sumproduct(–exact(a1:a5,"c")). But more generally it is good for when you need something more complicated than the regular countif can handle. This website has some good examples of these cases. […]
One thing you cannot do with Sum Product (or I cannot think of a way without using a helper column)
Row[A]Category[B]Value
1A15
2A10
3B17
4C14
5D32
N = SUM(B2:B6)^2/SUMPRODUCT(B2:B6,B2:B6) = 4.41
However, the result desired should be 3.28 which we obtain when summing category A (10+15 = 25) and then the formula..please advice if there is a way to perform the aggregation within the formula itself
Oops messed up the rows thr..but you guys get the point :/
seriously NO ONE ?