SubscribeAdvanced 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 4thMay?
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 3rd May?
So, How many Holden cars did I sell on the 3rd 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 10th May?
So, How many Ford and Suzuki cars did I sell on the 10th 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 10th May?
How many Toyota and Holden cars did I sell on the 10th 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 3-May 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] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
61 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 hi-lighted 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 >7-May
In cell E30 enter this formula =DSUM(TABLE,3,b29.b3)
Results are total for Toyota (Column 3) for a date greater than 7-May.
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 5-7-15-30-46-48-1-2-21-42
I need to substract the abs of 5-7, 5-15 etc. and after 7-15, 7-30 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 macro-vba-code 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/posting-a-sample-workbook
A work of art, mate – very helpful!
Great explanation on a seemingly difficult to understand topic from the offset! Thank you!