fbpx
Search
Close this search box.

Advanced Sumproduct Queries

Share

Facebook
Twitter
LinkedIn

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

102 Responses to “Advanced Sumproduct Queries”

  1. Lee says:

    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!

  2. Lee says:

    I couldn't download the files either Chandoo?

  3. Stefan says:

    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

  4. Hui... says:

    @Lee
    I should hire you to proof read for me.
    I have fixed those few errors, thanx

    Both files are downloading ok for me ?

  5. Lee says:

    I get a "Sorry, but I cant find the page you are looking for" message, Hui?

  6. Hui... says:

    @Lee
    Should be fixed now?

  7. Lee says:

    Yep, that's working now. Thanks!

  8. Aj says:

    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.

  9. Luke M says:

    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...

  10. Martin says:

    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 !

  11. Artem says:

    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

  12. Artem says:

    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.

  13. Patricia says:

    Hui- Great explanation and nice walkthrough of all the logic. You can do so many things with this formula.

  14. Chandoo says:

    @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... 🙂

  15. Bryan Steffen says:

    Great post! I have used this a couple of times, but never really known how it works. Excellent explanation.

  16. Clayton says:

    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

  17. Fred says:

    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??

  18. SteveT says:

    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.

  19. Kevin says:

    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.

  20. G says:

    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!

  21. DQKennard says:

    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.

  22. Prem Sivakanthan says:

    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! 🙂

  23. bill says:

    thanks! great topic, great treatment.

  24. Manasa Veena says:

    Hi Chandoo,,,thanks for sharing ur knowledge..It is very helpful.....Hope u r very much satisfied by sharing this kind of info..

  25. Lee says:

    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)

  26. Marilyn Fleming says:

    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.

  27. Martin says:

    @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?

  28. Kevin says:

    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.

  29. SteveT says:

    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!

  30. Ryan says:

    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.

  31. Hui... says:

    @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

  32. 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..

  33. Chandoo says:

    @William... Please email your file to chandoo.d @ gmail.com

  34. Shekhar says:

    it's very good. the thinking behind it is excellient and the way it is implimented is very good and appreciable

  35. Michael Azer says:

    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))

  36. Shair says:

    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!!

  37. BILL says:

    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

  38. Lee says:

    pm me with a copy of your worksheet and I'll have a look for you Bill

    larmitage at heathlambert dot com

  39. BILL says:

    Thank, Lee. I have.
    //b

  40. Brandon says:

    Enjoyed it very much my man!

  41. Martin says:

    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.

  42. Jaime says:

    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.

  43. Hui... says:

    @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

  44. [...] Advanced Sumproduct Queries by Ken on Oct 3, 2011 • 6:51 pm No Comments Original Post Click Here [...]

  45. Tanner says:

    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!

  46. Hui... says:

    @Tanner
    Thanx for the comments
    You will enjoy next weeks Formula Forensics !

  47. Hutch says:

    Where have you been all my life, SUMPRODUCT?! I'm smitten.

  48. Hutch says:

    Where have you been all my life, =SUMPRODUCT((Dates=D17)*(Cars=D18)*CarData)?! I'm smitten.

  49. Greg says:

    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?

  50. vicktor says:

    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.  
     

  51. jackjack says:

    A work of art, mate - very helpful!

  52. Steve says:

    Great explanation on a seemingly difficult to understand topic from the offset!  Thank you!

  53. KKRAUSE says:

    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.

  54. KKRAUSE says:

    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.

    • Hui... says:

      @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

  55. Sanjukta Samom says:

    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!!

  56. Demid says:

    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?")

  57. Martin says:

    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!)

  58. Doug says:

    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.

    • Hui... says:

      @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

  59. […] 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. […]

  60. Rohit says:

    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

  61. Rohit says:

    Oops messed up the rows thr..but you guys get the point :/

  62. Rohit says:

    seriously NO ONE ?

  63. Radjesh says:

    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

  64. himanshu jain says:

    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

  65. Pierre says:

    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

    • Hui... says:

      @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

  66. Pierre says:

    @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?

    • Hui... says:

      @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

  67. Pierre says:

    @Hui
    Thanks!

    Indeed I think I'll make a post on the forum, it'll be easier 🙂

    • Hui... says:

      @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!

  68. Alison Gane says:

    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

  69. Anon says:

    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

  70. Jayakumar Krishnamoorthy says:

    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.

    • Hui... says:

      @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

      • Jayakumar says:

        Hi Hui,

        Thanks for your reply. The formula works fine, it helps me a lot in getting results instead of creating pivot chart everytime.

  71. FM says:

    Really excellent examples, thank you so much!

  72. Anshuman Bhar says:

    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.

  73. Jonathan says:

    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)

  74. Ryan says:

    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

    • Hui... says:

      @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

  75. Arun says:

    Please send me daily newsletter and post

  76. 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?

  77. Saber mas says:

    Genial post. Gracias por compartirlo...Espero màs...

    Saludos

  78. Ron says:

    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

    • Chandoo says:

      @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:

      Sumproduct Alternative Columns

      Use this formula

      =SUMPRODUCT(($C$1:$E$1="rooms")*$C2:$E2,($D$1:$F$1="adr")*$D2:$F2)

  79. Jason says:

    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?

  80. Peter Bartholomew says:

    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") )

  81. 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?

Leave a Reply