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.
102 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!
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 1x10 + 2x20 + 3x30 = 140
But this can also be done using
Sumproduct(({1,2,3})*({10,20,30})) will do 1x10 + 2x20 + 3x30 = 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 error-prone 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 case-sensitive 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
1|A|15|
2|A|10|
3|B|17|
4|C|14|
5|D|32|
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 ?
Hi Chandoo
Very useful indeed.
I was looking for something similar but with a different condition. I have a table similar to one in scenario 4. But the output that I would need is the value between a certain range, not the sum itself.
Here you give the sum as 21 = (3+6+6+6). In my case, I would need to check if any of these array values (3,6,6,6) are less than a particular value, say 5, and then return corresponding value. or it can also return a standard text...
Not sure how to do this.
Radjesh
Hi Chandoo
Very useful post,
I have a doubt, if we want to have sumproduct for multiple dates in the above example then what will be the formula.
eg. sumproduct of 3rd may and 7th may of sizuki and toyota.
Himanshu Jain
@Himanshu
Simply use:
=SUMPRODUCT(((Dates=D30)+(Dates=E30))*(Cars=D31:H31)*(CarData))
I have a question regarding the scenario 4:
Does the ranges on the horizontal condition (Cars=D31:H31) have to be equal? I mean if the range "cars" is 5 cells long horizontaly, we should necesseraly have a 5 cells long range after the "="?
What if the first range ("cars" in this case) is longer than the 2nd range?
Thanks
@Pierre
If you edit the formula using F2
Select the section Cars=D31:H31
then press F9
Excel will show: {FALSE,TRUE,TRUE,FALSE,FALSE}
This is saying that the 2nd and 3rd positions have matches, the other 3 positions don't match
If you edit the formula using F2
and change the formula to Cars=D31:G31
then press F9
Excel will show: {FALSE,TRUE,TRUE,FALSE,#N/A}
This is saying that the 2nd and 3rd positions have matches, the other 3 positions don't match
The last position causes an error
If you edit the formula using F2
and change the formula to Cars=D31:I31
then press F9
Excel will show: {FALSE,TRUE,TRUE,FALSE,FALSE,#N/A}
This is saying that the 2nd and 3rd positions have matches, the other 3 positions don't match
The last position causes an error
So it is important that the two ranges match for the above 2 reasons
@Hui
Thanks for the explanation, it's clear.
But I will go a bit further, is there a technique to avoid those #N/A and make sure that the both range (cars and D31:H31 in the example) have the same size?
I am thinking about the case of a sales table where you have horizontally dates (let's say from from 01/01 to 31/12) and vertically you have sales people name. Outside of this range I put a start and end date and I want to sum the sales for a specific sales people during the specified time range. Is it possible to do with a sumproduct technique, or another approach would be needed?
@Pierre
In that case I would use Named Formula (Named Ranges) to define the ranges
By doing that each can be forced to be the same size, as well as be linkled to the data so that as the data changes so do the ranges
If your unfamiliar with these techniques, ask the question in the Forums
http://chandoo.org/forum/
and attach a file so that people can give you a specific answer
@Hui
Thanks!
Indeed I think I'll make a post on the forum, it'll be easier 🙂
@Pierre
As you have found out a number of people answer questions in the forums and so generally posts are answered with about 5 to 10 mins.
It is a lot harder to monitor posts here at the Blog site and apart from Authors who receive an email when a new post is made, I think Chandoo and I are the only ones who answer general posts here regularly and Chandoo is less than Regular!
Hey, save yourselves some typing when you want to see, for example all the fruit sold in one day, instead of having to type ((Fruit=Apple)+(Fruit=Pear)...) etc, as it is a Named Range just type (Fruit=Fruit).
Have never really 'got' SUMPRODUCT before now, think I will be able to make good use of it! Cheers, Al
Hi Chandoo,
How do you use sumproduct when you have two ifs and the ifs belong to two different tables?
So if criteria x is satisfied in table 1 and criteria y is satisfied in table 2, then do sumproduct of row x from table 1 and row y from table 2
how can i put it in formula
I've to count the number of machines having windows 7 installed with specific manufacturer say dell, specific model type say d630. The first column contains Manufacturer (dell, hp, lenovo, etc), the second column contains Model (D600, D630, 440, etc), and the third column contains Operating system information (Windows XP, Windows 7, Windows 8, Windows 10, Linux, Unix, etc). I can bring the result with the help of filtering each column get the final result. How to use Sumproduct to bring the count of operating system for the specific manufacturer, specific model.
@Jayakumar
It will be like:
To count the number of Dell's
=Sumproduct(--(A1:A100="Dell"))
To count the number of Dell's with Model=D600
=Sumproduct((A1:A100="Dell")*(B1:B100="D600"))
To count the number of Dell's with Model=D600 and Windows 10
=Sumproduct((A1:A100="Dell")*(B1:B100="D600")*(C1:C100="Windows 10"))
etc
Hi Hui,
Thanks for your reply. The formula works fine, it helps me a lot in getting results instead of creating pivot chart everytime.
Really excellent examples, thank you so much!
Hi All
I was trying to use this formula for the following data set range:
1. I have unique months ( Jan-15, Feb-15,...Dec-17..) in columns from Column E to column AQ.
2. I have unique names in rows ( A, B, C, D......) from Row number 9 to 98.
Now, I was trying the sumproduct formula to find out a unique cell that corresponds to a particular Name ( say, "F") and a particular month ( say, "Dec-16"). Unfortunately, I am getting a size limit issue if I use the data set array E9:AQ98. I am getting my answer in case I am using an array uptill E9:AQ21. This looks quite unreasonable as the data set is not very large.
Any help is appreciated in this matter.
@Anshuman
Can you post a sample file at: http://forum.chandoo.org/
This is such a clever and elegant method of performing a 2 way lookup. I have two questions:
1) Any thoughts on the relative efficiency of using this vs. the VLOOKUP/MATCH method? I'm a little concerned that in a big book, each cell can involve tons of calculations compared with VLOOKUP. I haven't used it in a big book, but I'm considering it. And I have no idea at what point even an "inefficient" formula would make a difference.
2) Any way to incorporate this into a VB user defined function? Something like: XLOOKUP(VerticalRange, HorizontalRange, DataArea, RowValue, ColumnValue)
Hello, I have a question, can you use the average function when using multiple values, I am also using an OFFSET function to grab the data from another tab. So if the value changes or you use multiple values in your selection criteria, will it avg all of the values together? Currently my SUMPRODUCT function just adds the multiple values together. example. I want the avg of apples, oranges and pears, sold in Store A on Saturday, currently the SUMPRODUCT just adds them together, can you avg them together? The data is coming from =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1) and the SUMPRODUCT formula is =SUMPRODUCT((ArbType29=$I$5:$K$5)*(ProductGroup29=$I$3)*(ClientName29=$I$4)*(DaysArbPlaceWrite29)) I would like for the SUMPRODUCT to give me the avg versus adding all of the values up. Thanks
@Ryan
If the sum is: =SUMPRODUCT((ArbType29=$I$5:$K$5)*(ProductGroup29=$I$3)*(ClientName29=$I$4)*(DaysArbPlaceWrite29))
Then the average is the Sum divide by the Count of the matching criteria
So I think it is:
=SUMPRODUCT((ArbType29=$I$5:$K$5)*(ProductGroup29=$I$3)*(ClientName29=$I$4)*(DaysArbPlaceWrite29))/SUMPRODUCT((ArbType29=$I$5:$K$5)*(ProductGroup29=$I$3)*(ClientName29=$I$4))
If that doesn't work can you post the question in the forums and attach a sample file
Please send me daily newsletter and post
F*ckin' remarkable issues here. I am very glad to see
your article. Thank you a lot and i am taking a look ahead to contact you.
Will you please drop me a e-mail?
Genial post. Gracias por compartirlo...Espero màs...
Saludos
Hello,
I'm trying to figure if you can use sumproduct with below data
each month has no of rooms sold and the average daily rate (adr) and listed in columns.
every row represents the channel
now, i'd like to get the total revenue of each row. manually, i will enter the formula (a1*b1)+(c1*d1) where (10*110)+(15*120)
can i use sumproduct on this case or is there another formula to use
january february...
market rooms adr rooms adr total revenue
transient 10 110 15 120 ???
OTA 20 120 25 115 ???
corporate 30 130 25 125 ???
consortia 40 140 10 115 ???
appreciate help from anyone.
many thanks!
ron
@Ron... Welcome to Chandoo.org and thanks for posting a comment. Interesting question.
You can use SUMPRODUCT like below. Assuming your data looks like this:
Use this formula
=SUMPRODUCT(($C$1:$E$1="rooms")*$C2:$E2,($D$1:$F$1="adr")*$D2:$F2)Hi Chandoo,
You got it right and the formula worked 🙂
Appreciate it very much
Cheers,
Ron
I have been using sumproduct with conditions, named ranges and drop down lists using data validation. For example, a list of names with a named range of "names", a list of towns "towns", a list of homes in that town "numhomes". So my forumla looks like this SUMPRODUCT((towns="Houston")*(names="Jason")*(numhomes)) which would return the number of homes that Jason owns in Houston. However, I want to be able to use my drop downs to select a value of "All". My formula might be: SUMPRODUCT((towns="Houston")*(names="All")*(numhomes)) in order to return all homes in Houston. There is no value in the "names" array for "All", so I will get 0. I know that I can change the formula to SUMPRODUCT((towns="Houston")*(numhomes)) to get the answer I want, but remember I want to keep using drop down lists where I can select "All" as a name and have it include all in that column. I am stumped...can you help?
Jason
You have had a year to sort your problem. Did you succeed or simply give up? For the record, the condition
( names=selectedName )
becomes
( (names=selectedName) + (selectedName="All") )
Hi, I have a data table contains ManagerName, OfficerName. I'm using "*" in dropdown list for OfficerName list like:
*
Officer1
Officer2
If I select *, then All value under ManagerName (Cross data table like yours) will sum for selected ManagerName. I've tried, but it's not working. Can you help me?