What is Excel SUMPRODUCT formula and how to use it?

Posted on November 10th, 2009 in Featured , Learn Excel - 196 comments

Today we will learn a new and exciting excel formula – the all powerful SUMPRODUCT.

At the outset SUMPRODUCT formula may not seem like all that useful. But once you understand how excel works with  lists (or arrays) of data, the SUMPRODUCT’s relevance becomes crystal clear.

SUMPRODUCT formula – syntax and usage

Excel SUMPRODUCT formula - tutorialThe sum-product formula syntax is very simple. It takes 1 or more arrays of numbers and gets the sum of products of corresponding numbers.

The syntax is =SUMPRODUCT (list 1, list 2 ...)

Excel SUMPRODUCT formula - examplesSo, for ex: if you have data like {2,3,4} in one list and {5,10,20} in another list, and if you apply SUMPRODUCT, you will get 120 (because 2*5 + 3*10 + 4*20 is 120).

At this point it might seem like an almost useless function. But all that will change in the next 2 minutes, keep reading.

SUMPRODUCT and Arrays

Lets say you have a list of sales data with columns Name, Region, Product and Sales. Now, you want to know how many units the sales person named “Luke” sold. This is simple, you will write a SUMIF formula [examples] and use the Name column as “criteria range” and Sales column as “sum range”.

But, wait a second, you want to find how many units sales person “Luke” sold in the region “west”.

Hmm…. we have 2 options,

Actually, there is a hidden third option, use SUMPRODUCT.

That is right, my friend, we can use SUMPRODUCT to do just this (and much more).

Using SUMPRODUCT as an array formula

Assuming, the data is in range A1:D10, with Name in column A, Region in B, Product in C and Sales in D, the SUMPRODUCT formula is,

=SUMPRODUCT(--(A1:A10="Luke Skywalker"),--(B1:B10="West"),D1:D10)

Okay, lets take a minute and try to understand WTF (what the formula) is doing.

  • The portion --(A1:A10="Luke Skywalker")is looking for Luke Skywalker across planetary systems in all universes ;) It is going to give us a bunch of ONEs and ZEROs, one if the cell has Luke, Zero if the cell has something else.
  • The portion --(B1:B10="West")is doing the same, but gets 1s when the value is “West”.
  • The portion D1:D10 is just returning all the sales figures.
  • When you put everything together and multiply, it just works. Why? That is your home work to figure out.

Excel SUMPRODUCT formula example and explanation

Share your SUMPRODUCT formula Tips & Tricks

SUMPRODUCT formula can do much more once you understand how it works. This post is meant to open the door for you. Go ahead and explore the possibilities, then come back and share your tips with us.

Recommended Reading

I suggest reading the excel array formula examples, sumif with multiple conditions and other excel formula tutorials.

This post is part of our spreadcheats series

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

196 Responses to “What is Excel SUMPRODUCT formula and how to use it?”

  1. Cyril Z. says:

    Nice explanation Chandoo, I love it. It’s “almost” crystal clear for me.

    Can you explain the use of — before the test ?

    • Ahmed says:

      Hi Chandoo!
      Your posts are really helpful, I have a querry, mentioned below.

      Wanted to know whether sumproduct function can work with filters.

      I have tried, however, the sumproduct values do not change as per list visible post filtering the column.

      Thanks

      Ahmed

  2. Chandoo says:

    @Cyril .. good point. the double hyphen is converting a list of boolean (true, false) values to ZEROs and ONEs. Each hyphen acts as a negation. When you negate something, excel converts the underlying values to numbers and then reverses the SIGN. So TRUEs become -1s and FALSEs become 0s. The second negation reverses this and leaves just numbers thus allowing sumproduct to actually multiply instead of throwing an error.

  3. Moatasem says:

    Another alternative for this example is to use SUMIFS function, but it is available only in excel 2007 & 2010

  4. Rakesh says:

    Nice post Chandoo!!
    Is there any limit to the number of arguments we can use with SUMPRODUCT function?

  5. Gerald Higgins says:

    Chandoo – good explanation. I’m still learning about sumproduct, arrays, and the function of — .

    I’d read elsewhere about using — to “coerce” some values – I know what coerce means but I didn’t really understand it in this context. I do understand your explanation though – basically it’s multiplying the results by -1. I tried using – instead of –, and it appears to work just the same, as long as you have an even number of arrays to multiply, as you do in your Luke Skywalker, West example.

  6. kaliman says:

    Hi all,
    I’ve seen the double hyphen (–) in all SUMPRODUCT tips, but haven´t used and so far my formulas seem to work fine (no errors)… any idea when it should really be used?.

  7. Gerald Higgins says:

    Kaliman – for me, Chandoo’s Luke Skywalker / West example doesn’t work unless I use the double hyphen – -

    I’ve just noticed in my earlier post, I had typed 2 hyphens and it came out looking just the same as 1. Apologies if anyone was confused by that :-)
    It should have read “…I tried using – instead of – - …”

  8. Brian S says:

    The double-negative on the TRUE/FALSE result is likely my favorite formula trick, and has always allowed me to stay away from arrays (which I find cumbersome and error-prone).

    Chandoo/Excel Gurus, do you believe the SumProduct usage is inherently faster than an array of SumIFs? I’ve read Excel forum threads that assert that the SumProduct performance difference is negligible because Excel was performing array activities behind the scenes, but those threads were a bit old (circa 2002/2003), and I never really bought into it. What has your experience been on the performance question?

  9. Finnur says:

    Great post. The array part of this post will come in extremely handy at work.

  10. mikii says:

    sumproduct() is my favourite multiple condition summing formula. but double”-” before the argument was a new thing for me as I usually used syntax like this =sumproduct((array1=”luke”)*(array2=”west”)*array3)
    however, sumproduct has one drawback – the array you want to sum must consist only from numericals. even if actual selections wont include cell like “n.a.” (might happen in datasets), sumproduct() will result in an error. find/replace helps just fine ;)

  11. Andy says:

    Seconding the use of SUMIFS if you have 2007 or higher. I personally find it much more intuitive.

  12. Modeste says:

    Hi Brian, Hi Shandoo,
    for more details and considerations …
    here from an Excel Guru :
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    ;o)))

  13. Kanti Chiba says:

    Great explanation Cahndoo,

    I have seen the — used often in the SUMPRODUCT formula but not the N() fucntion, is there a reason not to use the N() function?

  14. Social comments and analytics for this post…

    This post was mentioned on Twitter by r1c1: Checkout What is Excel SUMPRODUCT formula and how to use it? http://bit.ly/1EbIsU

  15. Sal Paradise says:

    Excel has trouble doing math with text values. It needs to turn them into numbers first.

    Type ’0 into a cell (mind the quote). This makes a text zero.

    Type =A1=0 into another cell, and you will get FALSE as the result.

    Type =A1-1 into another cell, and you will get -1

    But wait, if it’s not a number, why can it do math?!

    Well, Excel forces things to be numbers when you do math on them.

    So if you use math in sumproduct, you don’t need the double-minus:
    =SUMPRODUCT((A1:A10=”Luke Skywalker”)*B1:B10=”West”)*D1:D10)

    When it does the math for this, it will multiply all the zeroes and 1′s and values together, and will always come out with a number.

    If you don’t put them in:
    =SUMPRODUCT((A1:A10=”Luke Skywalker”),(B1:B10=”West”),D1:D10)

    You get 0 instead.

    So we need to turn them into numbers first. We can do that a lot of ways. For instance:
    =SUMPRODUCT((A1:A10=”Luke Skywalker”)+0,(B1:B10=”West”)+0,D1:D10)

    =SUMPRODUCT((A1:A10=”Luke Skywalker”)*1,(B1:B10=”West”)*1,D1:D10)

    =SUMPRODUCT(N(A1:A10=”Luke Skywalker”),N(B1:B10=”West”),D1:D10)

    But when it comes down to it, the people who are really smart at Excel figured out that Excel is fastest when it uses the unary operator (the ‘-’ sign) twice.

    So now people write:
    =SUMPRODUCT(–(A1:A10=”Luke Skywalker”),–(B1:B10=”West”),D1:D10)

    Feel free, if you have trouble, to use the *1 or +0 or N() if you’d prefer. They will work too.

    So, to conclude.

    If you use SUMPRODUCT with commas, you need to turn true/false values into numbers first by doing math or by using the N() function.

    If you use SUMPRODUCT with multiplication, you’re already doing math so you don’t need it.

    Most of the time it’s personal preference (I’m sure that there is a difference in speed in the long run, but most of us will probably never ever know about it).

  16. Yogesh Gupta says:

    Hello Chandoo

    – makes formula confusing for many users and it can be avoided if you replace , with * . The revised formula will be as under
    =SUMPRODUCT((A1:A10=”Luke Skywalker”)*(B1:B10=”West”)*(D1:D10))

    This formula is giving multiconditional sum and we have used only two conditions. However you can add as many conditions as you like provided the hight of each range is same.

    If you remove the last entry with does not have condition attached to it, the formula will do the multiconditional count.

    Formula for multiconditional count will be
    =SUMPRODUCT((A1:A10=”Luke Skywalker”)*(B1:B10=”West”))

    Thanks

    • Brenda says:

      Using the * will work for “and” criteria but you may not always want to use “and” in your formulas. To use “or” criteria you can use a + sign which is also doing math so you would not need to the the –.

  17. Simon says:

    Does this usage of SUMPRODUCT allow for multiple entries of the search criteria throughout a range?

    i’ve always been fond of the SUMPRODUCT((range=criteria)*(range=criteria)) to search for multiple variables: Saves on pesky pivot tables.

  18. Chandoo says:

    @Moatasem & Brian & Andy

    Yes, we can use SUMIFS in the same way. It is a better function, but not backward compatible with earlier versions of excel. That is why I wrote about this first. I will write about SUMIFS , AVERAGEIFS and COUNTIFS in an upcoming article.

    @Rakesh…

    You are welcome. Yes, There are limitations on SUMPRODUCT – It takes up to 255 arrays. It also slows down considerably once you have too many rows in the arrays (lists). I suggest using pivot tables or manual calculation mode if that happens.

    In general, SUMPRODUCTs hold up well even for fairly large data (like few hundreds of rows or even thousands).

    @Gerald

    it is better to use double hyphen (minus sign) before every array as it lets you add / remove arrays with out worrying about the even-ness of the number of attributes.

    @Brian S

    “Chandoo/Excel Gurus, do you believe the SumProduct usage is inherently faster than an array of SumIFs? …”

    I have not benchmarked SUMPRODUCT against other similar formulas like VLOOKUP, MATCH or Array SUM() formulas. But I have experienced considerable slow down when I have few thousand rows of data, no matter what formula is used. If I am writing the code behind these formulas, I would have used similar logic for all. So I dont see a reason why one formula should be faster / better than other. But I am as intelligent as a cabbage when I am talking about these things.. so…

    Here is a request to all.. “if you have benchmarked these formulas, please let us know the results thru comments”

    @Modeste … Thanks for the excellent link.

    @Kanti Chiba

    You can use N() in the same way. N() formula converts the input to number.

    @Sal .. awesome explanation. Donut for you.

    @Yogesh .. Again, super trick. I agree with * approach. Donut for you as well.

    @Simon

    You can use Array concepts like + operator to check for multiple conditions. For eg. ((A1:A10=”Luke Skywalker”)+(A1:A10=”Hansolo”)) should check for both Luke and Hansolo in the range A1:A10.

    • Brenda says:

      If you use a + sign then it reads as “or”. So what you are really saying is sum values for Luke Skywalker OR Hansolo. This is an important distinction! Using a * is read as “AND”.

  19. Jeff Weir says:

    Chandoo my man. I had to multiply some arrays today, and was momentarily scratching my head trying to remember how to do this with array formulas. I took a break, checked out your RSS feed, and whoa…you’ve taken my question right out of my mind and answered it.

    I owe you a donut for this, which cancels out the one I earnt the other day. Don’t you just love this weightless, low calorie economy?

  20. Chandoo says:

    @Jeff, in the donut economy things never cancel out. They just get added up, because donuts are absolute(ly delicious).

  21. Eric Lind says:

    I seriously wonder how many people-hours you saved with this one post alone Chandoo.

    I guess it leaves more time for virtual donuts. :)

  22. Chandoo says:

    @Eric.. that is so sweet (and not in the heart killing donut sweet way). Thank you :)

  23. [...] used SUMPRODUCT liberally to summarize the gift data to show us “how many people got the gifts”, [...]

  24. Jadam says:

    I have a sumproduct question!
    In my workbook i am working with 2 worksheets: “Hours” & “Variance”.

    I am essential developing a variance tool on the variance tab.

    I have a list of employees on the hours tab and the rests of the actual hours that they have worked by dated column.

    I am trying to write a formula that totals all of the hours for a single employee based on a given date. See example at the end.

    These formulas work as a stand alones:

    =ADDRESS(MATCH(A10,Hours!$M$621:$M$725,0)+ROWS(Hours!$L$1:$L$620),MATCH($C$2,Hours!$620:$620),4,1,”Hours”)

    =SUMPRODUCT(–(Hours!M621:M725=A10)*Hours!$N$621:$BZ$725)

    BUT, when I try to combine the two to get the date variability like I want, Excel says my formula contains an error. Here is what the formula looks like:

    =SUMPRODUCT(–(Hours!M621:M725=A10)*Hours!$N$621:ADDRESS(MATCH(A10,Hours!$M$621:$M$725,0)+ROWS(Hours!$L$1:$L$620),MATCH($C$2,Hours!$620:$620),4,1,”Hours”))

    Doughnuts all around for anyone who can help me on this one! I suspect that I have a syntax issue in my formula but not sure.

    Example:

    Variance Tab
    As of: 11/28/2009

    Resource Total hours worked Answer should be
    Billy 130
    Bob Formula 50
    Joe goes 80
    An here 90
    Jill 160

    Hours Tab

    Resource 11/14/2009 11/28/2009 12/12/2009
    Billy 40 90 30
    Bob 30 20 80
    Joe 50 30 80
    An 80 10 40
    Jill 80 80 50

  25. Jadam says:

    bummer, example data formatted ugly….imagine rows and columns, rows and columns

  26. Chandoo says:

    @Jadam… I think this is happening because you cannot define a range like a1:Address(xxx), it has to complete range or complete indirect. Why dont you use INDIRECT(“Hours!$N$621:”&ADDRESS(…)) in the sumproduct.

    Also make sure the INDIRECT is wrapped in ()s to avoid any confusion.

  27. Modeste says:

    Hi Chandoo, Hi Jadam

    ;o))) there is no use of SUMPRODUCT ….

    just you have to name some ranges
    ResDat =Hours!$B$1:$A$Z1 ( ie for 52 dates)
    ResNam =Hours !$A$2:$A$1000 ( ie for 1000 names)

    in Variance!B2 put this formula
    =SUMIF(WBK1!resDat,”<="&$A$10,INDIRECT("hours!"&ADDRESS(1+MATCH(A2,WBK1!resNam,0),2)&":"&ADDRESS(1+MATCH(A2,WBK1!resNam,0),52)))

    and pull it down as many time as ressource names
    beware :
    dateref of calculation is absolute $A$10
    current ressource name is relative : A2

    WBK1 is actual Workbook.name

    HTH
    ;o)))

  28. Hui... says:

    Supposedly — is slightly faster than any other way of converting the logicals to numbers.

    My preference is to use =SUMPRODUCT(1*(A1:A10… instead of =SUMPRODUCT(–(A1:A10=
    As it is easier to read, especially for those not so familiar with formulas.

  29. uktiMike says:

    Just fallen on this site – already hooked.

    Sumproduct is great. I used to struggle to add multipple criteria up in my spreadsheets, and then someone from UtterAccess.com taught me about Sumproduct. I can’t thank that person enough.

    Anyway…many articles to catch up on. Keep up the outstanding work.

  30. Daniel Ferry says:

    Chandoo:

    The SUMPRODUCT function is the most versatile in Excel.

    It can be used to do multicolumn sorts by array (with no human intervention or VBA required).

    It can determine if a number is prime.

    It can coerce bitwise operations in integers (i.e. bitwise logical AND, OR, XOR, IMP, EQ, and NOT).

    It can be used to supercharge lookup tables with bitmasks.

    It can find the dot product of two vectors.

    It can do the database calculations you addressed in this post and has the massive advantage over SUMIF and COUNTIF (and the Excel 2007 SUMIFS and COUTNIFS) that it can handle OR clauses in the criteria.

    It can replace most any Array-Entered Formula that uses SUM, and is usually about 10% faster.

    This list could go on for pages…

    I have a very detailed post on my blog:

    http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

    Regards,

    Daniel Ferry
    excelhero.com/blog

  31. [...] anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the [...]

  32. Teresa says:

    In support of @Eric and a HUGE thank you Chandoo! I was tasked with preparing a report with thousands and rows of data and compiling based on three variables and I had no idea how I was going to make it happen without half a dozen pivot tables and hours of time, but with this tutorial I got it done in about 2 hours and only one pivot table for check totals. My boss is beyond impressed. Thank you so very much for sharing this knowledge with us! Now I have plenty of time to go in search of these virtual donuts I read of… :-P

  33. Chandoo says:

    @Teresa… Wow.. I am so very proud of you. More success to you.

    Btw, I would love to know how you have arm twisted such massive amount of data in no time. If you have sometime, can you write a guest post on Chandoo.org explaining the problem and solution ?

  34. [...] The main formula being used is the SUMIFS(Sum range, Criteria range, criteria,…). This was a new formula in 2007 and provides a simpler solution to the SUMPRODUCT() formula. [...]

  35. John Franco says:

    Hi Chandoo,

    I have a comment for Brian…

    I have heard about the performance issues of SUMPRODUCT.

    I made a test on a 1 million rows table using SUMIFS and SUMPRODUCT side by side and didn’t notice any significant differences in calculation time.

  36. Chris says:

    In my lists there are a lot of =NA() Values. Therefore Sumproduct returns NA aswell, is there any workaround besides converting to empty cells?

  37. Chandoo says:

    @Chris.. you can use SUM () formula with arrays, like this:

    =SUM(IF(ISNA(F14:F20),””,F14:F20)) to check for NAs and replace them with empty spaces or zeros. You must ctrl+shift+enter that formula to get it work.

  38. Chris says:

    Thx Chandoo!

  39. CL says:

    Hi Chandoo,

    Came across your site when looking for help on sumproduct.

    Firstly, what does the “–” do in this formula?
    secondly, can I use Named Range in replace of “D1:D10″; giving your formula to change from:-
    “=SUMPRODUCT(–(A1:A10=”Luke Skywalker”),–(B1:B10=”West”),D1:D10)”
    to
    “=SUMPRODUCT(–(A1:A10=”Luke Skywalker”),–(B1:B10=”West”),SALES)”

    Thanks in advance.
    Cheers,
    CL

  40. Hui... says:

    @CL
    The two – - ‘s or double nagatives and aren’t required in your case, although it is good practice to use them all the time just in case.

    Sumproduct takes each set of (Range=something ) and converts each element in that range to a True/False.
    In your case you have 2 sets of (Range=something) and so when they are multiplied the result is converted True = 1 & False =0.
    If you only have 1 set of (Range=something) then you need to do some maths to that to convert the True/Falses to values for evaluation. Hence the use of – -.
    I prefer to use 1* as I think it reads easier and other do 0+, but all do the same thing they convert the True/False values in the array to 1′s and 0′s for subsequent use.

    It s good practice to use a – - or 1* as if someone else comes along and removes one set of (Range=something) then the remaining equartion without a – - wont work.

    Absolutely use Named Ranges, They will make your formulas so much easier to read and understand in 6 months when your trying to work out “What did I do here ?”

    Have a look at http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

  41. pfabi says:

    It works fine but I think it is prone to very popular mistake – spaces. Name “Luke Skywalker” can be written as “Luke Skywalker ” – this is very common mistake I deal with. Often people that are filling such sell-results-datasheets use copy-paste method (and copy too much). In the spreadsheet it looks the same:
    Luke Skywalker
    Luke Skywalker

    If the the table is large – few hundreds of records, it will be very difficult to find that there is mistake (e.g. one record with sales = 36, and total sum of Luke S. = 12 340, the potential accountant will not notice that – 36 too small comparing with 12 340, and he assumes that is OK).

    So I propose to slightly modify the formula. Instead of:

    =SUMPRODUCT((A2:A21=”Luke Skywalker”)*(B2:B21=”West”),C2:C21)

    use:

    =SUMPRODUCT((TRIM(A2:A21)=”Luke Skywalker”)*(TRIM(B2:B21)=”West”),C2:C21)

    There is also “Trim” with the region (West), just in case :). Of course it is possible to use some data validation when filling the spreadsheet, and then there will be no need for Trim.

  42. [...] SUMPRODUCT formula:=SUMPRODUCT((OrderPrice)*((Customer="Hansen")+(Customer="Jones")))Check also http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/  Comments are closed.Tagsbrazil cabot trail concert cycling excel holiday home joern kieler [...]

  43. [...] just love SUMPRODUCT Formula. So I use it whenever I can. But you may like other techniques. So please tell me how you would [...]

  44. kwasi says:

    sumproduct is awesome. you can use for condition sum and count and everything most things that deals with array. For example you can combine this with the functions row(), max() to get last item (row) of data. excel Help document underestimate sumproduct’s power.

  45. Nas says:

    Thank you v much!! your website has been a godsend for me. I just started work and have to use excel extensively. Your website gave me simple ways to learn the tricks to make life easier. Thanks!

  46. [...] use of the Sumproduct function for doing multiple criteria Sum If’s is possibly one of the greatest extensions of an Excel [...]

  47. Jonathan says:

    Nope i dont get it.
    Large coffee and try again tommorow

  48. [...] do you model this in excel fast? In this tutorial we understand how you can use SumProduct() formula to do [...]

  49. Anand says:

    Hello Mr. Chandoooo.com,

    Thankx a ton!!!!

    The formula works and it completes my work in secs…..

    Thankx a lot one again

  50. [...] wrap this in a lovely SUMPRODUCT formula so that we can check for palindrome-ness of B1 using =IF( SUMPRODUCT( ( [...]

  51. Estalta says:

    You’re a real teach. Amazing easy explanation!!!
    I spend a week reading forums and I didn’t get it.
    Today I decided to give it another try, found your site
    and in less that five minutes I understood it.
    THANKS SOOOOOO MUCH!!!!
    GOOD JOB!!!!!

  52. LornaB says:

    Hiya – am not getting the sumproduct formula to work in Excel 2007 – worked like a charm previously – so not sure what now?

    I urgently have to get a value total for all rows that qualify on two criteria, and your formula is giving me a #value error – I have tried with – - and without, replacing the commas with the asterisk – and no luck. Would be awesome if I could get it sorted as a potential deal depends on it : (

    Thanks for any help in advance

    LornaB

  53. Hui... says:

    @LornaB
    Check that all your Ranges are the same size (length and width)

  54. LornaB says:

    Ok – So all the ranges are the same length. Still no go. So what would happen if there are blank fields?

  55. Chandoo says:

    @Lorna… Are there some errors in the data range itself? If possible, can post your formula as is along with some sample data in the comment?

  56. LornaB says:

    =SUMPRODUCT((Data!$D$2:$D$500=A2)*(Data!$AE$2:$AE$500=”PROGRESS”)*(Data!$AD$2:$AD$500))

    Data Tab
    Column D =
    Status
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted
    Firm Quoted

    Column AE
    Item PayType

    PROGRESS
    SUBSCRIPTION
    SUBSCRIPTION
    PROGRESS
    SUBSCRIPTION

    PROGRESS
    SUBSCRIPTION

    Column AD
    Item Total

    8,000.00

    4,000.00
    1,000.00
    2,000.00
    20,000.00
    400

    10,000.00
    1,000.00

    On Main tab (where formula sits A2 = Firm Quoted

    I am thinking that the blank spaces or the actual format of the values (ColumnAD) are what is causing the grief……

    If you are wanting more data let me know and I will email the spreadsheet…..

    Thanks SO much for the help guys!

  57. LornaB says:

    Hey Chandoo,

    The data is not coming through properly here – as the blank rows are not being put in…. There are a couple of blank rows through out the columns – could that be causing the issue?

  58. Chandoo says:

    @Lorna.. I tried with some dummy data and it seems to work fine. Are you sure the numbers are numbers and not text looking like numbers?

    I ask this because you have some numbers with thousands separator & 2 decimal points and some without them (the 400 in middle).

  59. LornaB says:

    @Chandoo

    I think that may be exactly it, my problem is that the data comes off a webquery – which needs to be refreshed from a database and the values need to be calculated etc etc, so changing the format each time defeats the purpose.

    Absolutely any idea how I can circumvent this?
    : (

  60. Hui... says:

    @Lorna
    Multiply all your data by 1 using paste Special Multiply
    That will fix it.

  61. LornaB says:

    @Hui,
    Thanks for the info – my problem is that the data comes through a webquery – so any changes are lost with a refresh – so I need to find a solution in the actual formula : (

    Dunno if there is anything that can help?

    I would be more than happy to sent the spreadsheet to anyone that is interested……….

  62. Ash says:

    What is the need for hte “–” in the sumproduct formula?
    Also, having trouble getting an answer..I am getting an error. Please help.

  63. Chandoo says:

    @Ash… the the – (minus symbol) forces boolean values (TRUE, FALSE) to be converted to numbers. But since it is minus, it will negate them. So we use one more – sign (thus double minus) to convert TRUE to 1 and FALSE to 0.

    What error are you getting?

  64. rahul aggarwal says:

    @chandoo
    can u give example work sheet of above example

  65. rahul aggarwal says:

    @chandoo and hui
    i have criteria in E coloum and have value data in H and S column
    problem is whenever i m using sumproduct formula to get H column data it shows true result
    but when i apply it for S column data it show the #VALUE error
    i have checked it from all side but cant get sucess
    nd if i apply sumif formula then there is no issue

  66. Hui... says:

    @Rahul
    Check the following
    1. The range length in Columns E, H and S must be the same, eg: E2:E20, H2:H20 and S2:S20
    2. In Columns H & S check that the cells don’t contain a leading or trailing space
    3. If Column S is numbers, but not as a Formula, type 1 in a blank cell, copy it and Paste Special Multiply over the range in Column S, this will convert text that looks like numbers to numbers.
    If the above doesn’t help, can you post the two formulas and some data to show us what you have

  67. rahul aggarwal says:

    @hui
    thanx alot
    in my situation S column contained formula and after applying your third suggestion its working perfectly
    but there is any formula which can show result of contained of formula column
    i have try text formula as follows but showing errror
    =SUMPRODUCT((E2:E20=”tikka”)*(TEXT(H2:H20,”0″)))
    i am asking this because my data expand every hour and have to see result all time but doing this “copy paste and multiply always” is not convenient

  68. Hui... says:

    @Rahul
    Try:
    =SUMPRODUCT(1*(E2:E20=”tikka”),(H2:H20))
    or
    =SUMPRODUCT(1*(E2:E20=”tikka”),(S2:S20))

  69. rahul aggarwal says:

    @hui
    above formula
    is showing same error #VALUE

  70. [...] recently asked for an example in Excels Sumproduct Formula post;  Comment No. [...]

  71. hopeless says:

    due to my long conditions excel has already prompted me that I have a long formula… is there anyway I can get around? I only have one condition left

  72. Greg says:

    Can you please help me out with changing this formula to =SUMPRODUCT?

    I currently have this formula;
    =SUMIF([Test.xlsx]Sheet1!$C$2:$C$101792,A14,[Test.xlsx]Sheet1!$F$2:$F$101792)
    I have tried using this;
    =SUMPRODUCT(–([Test.xlsx]Sheet1!$C$2:$C$101792=A11),[Test.xlsx]Sheet1!$F$2:$F$101792) but it returns an a value of ’0′.

    Can you help me by telling me where my formula is wrong?

  73. Zafar says:

    Can someone help me with the following calculation? what is the formula? Example. Cell A1 (250) is greater then Cell A2 (150), the result in cell A3 will be 100, but if the Cell A1 is not greater then Cell A2, so the result must be in Cell A3 0 (zero). This is the point. Please help me. email: zafar_fafa@yahoo.co.uk

  74. Jeff Weir says:

    Zafar:
    =IF(A1 > A2,100,0)

    or you could do this:
    =(A1>A2)*100

    …because the (A1>A2) bit returns 1 if true, or zero if false, which you then multiply by 100. So if true, 1 * 100 = 100. If not true, 0 * 100 = 0

  75. FSJ says:

    I was looking for a work around for SUMIFS function to work on gDocs SpreadSheets.
    Unfortunately, SUMIFS is not available in gDocs, so I used the sumProduct function instead.
    In my example, I had ranges to work with

    Column A was minimum value of a range
    Column B was maximum value of range
    Column C was number of occurrences within the given Min and Max

    i wanted to summarize the data with new and more meaningful ranges.
    So, in column E, I defined the new Minimum Range
    in Column F i defined the new corresponding Maximum Range
    in Column G, i applied the following formula
    =SUMPRODUCT((COLUMN1>=E2)*(COLUMN2<=F2)*(COLUMN3))

    It does not take full advantage of SUMPRODUCT, but it is a handy workaround for SUMIFS in gDocs and older versions of Excel which dont support SUMIFS.

  76. Clif says:

    A very thorough explanation of SUMPRODUCT that I have found to be extremely helpful is at
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    I first discovered the xldynamic website when researching SUMPRODUCT on various MVP websites … there is a link on this page
    http://blog.contextures.com/archives/2009/06/17/excel-for-underdogs/

    HTH!

  77. Ksandra2901 says:

    So I have two problems with this.

    First I have data which is telling me whether or not a delivery was made on the correct delivery day (returning a Y or N), what month it was delivered in (Returning Jan, Feb, Mar) and the rate that was charged. The sumproduct is happily counting how many were not delivered on the right day in each month:

    =SUMPRODUCT((a:a=”n”)*(b:b=”jan”))

    However when I try to expand to total the rates for the month I get an value error.

    =SUMPRODUCT((a:a=”n”)*(b:b=”jan”)*(C1:C300))

    Any ideas?

    My next issue is that I need to do the same sums but for week numbers. I am assuming that my problem here is that you can’t have text and a number in the same formula.
    ie =SUMPRODUCT((a:a=”n”)*(b:b=”1″))

    I’ve tried using the – - to turn the text into 1′s like you say, but it is not having any of it…..

    Help??

    • FSJ says:

      for your problem 1:
      =SUMIFS(C:C,A:A,”n”,B:B,”jan”)
      this will calculate the sum of all values in column C where
      Column A contains “n”
      and Column B contains “jan”

      for problem 2, i would suggest you create another column for week number

      if that does not work, do drop in some more details of what the data looks like

      • Ksandra2901 says:

        Hi FSJ,

        Thanks for the swift response.

        Alas I am having the same issue as a lot of you, in that I did SUMIFS, sent to a colleague who was still running 2003, hence the need for something else! While sending her just the values answers her questions, I would like to master SUMPRODUCT as this is the first opportunity I have had to use it!

        My bad, the week numbers are in a different column. Sorry I didn’t make that clear. It is difficult to explain these type of problems clearly….

        Does that help? Thanks

        • FSJ says:

          =SUMPRODUCT((A:A=”n”)*(B:B=”jan”),C:C)
          this would return the same result as SUMIFS formula

          regarding the week grouping, say
          column A contains Y/n
          column B contains Month
          Column C contains the amounts
          Column D contains the week number

          =SUMPRODUCT((A:A=”y”)*(B:B=”jan”)*(D:D=1),C:C)

          this would extend the formula to cater for the week number given in column D.

        • FSJ says:

          i usually use the sumproduct formula in the following way
          =sumproduct( (contidion 1) * (condition 2) * … * (condition n) , (result array) )

          i.e., the conditions are multiplied and the comma comes before the resultant column(s)
          in your case, the revenue/amount column was the resultant
          month name, week number, delivery state (y/n) were your conditions

          but sumproduct is a lot more powerful than just the above stated usage

          • Ksandra2901 says:

            Hi FSJ,

            I appreciate youR time on this. I know how powerful excel is and have already learned loads just from visiting this site!

            I have fixed the first problem. I had to change the rate column format to “general” then it picked it up.

            But the second problem still confuses me as it is not even counting up :O(

            So, to clarify:

            Column C contains (Y/N)
            Column E contains Week Number (1,2,3)
            Column P Rates
            Cells all currently formatted to “General”

            =SUMPRODUCT((Data!$C:$C=”N”)*(Data!$E:$E=”1″)) is coming back with a 0. That’s before I even try to expand to the rates column?

          • FSJ says:

            Try removing the quotation marks around the 1
            =SUMPRODUCT((Data!$C:$C=”N”)*(Data!$E:$E=1))

            that should be a start

            also, i am assuming that you want to sum the Ns and not the Ys in column C

  78. Ksandra2901 says:

    Hi FSJ,

    I couldn’t reply to your reply.

    Just to say that cracked it, thank you for putting up with me. I owe you a coffee!

  79. Catherine says:

    Thanks a million for the clear ordinary language explanation of sumproduct.

    Yesterday I was stumped trying to match four criteria and return a number.

    This morning I thought, “sumproduct should work. I need to find a sumproduct tutorial.”

    I created the formula and then solved my own question on excelforum.com.

    I am pleased! :D

    Catherine

  80. Catherine says:

    Hui,

    Thanks a million. This is terrific information. I”m heading towards being an expert Excel user. Who knew?

    :D

    Catherine

  81. Jason says:

    Is it possible for sumproduct to return text? Using the sample data that began this thread I used the following array formula…
    =SUMPRODUCT(($C$2:$C$21=$I$1),–($B$2:$B$21=$H$1),–$A$2:$A$21)
    Unfortunately, this formula continues to give me a #value! error.

    • Jason says:

      To be more clear with my formula (the previous one contained references) here is the revised formula…
      =SUMPRODUCT(($C$2:$C$21=116),–($B$2:$B$21=”West”),–$A$2:$A$21)
       

  82. Ksandra2901 says:

    Me again!

    Trying to use this on a new report and it keeps returning a #NUM or #N/A error. What am I doing wrong? I just want to count how many BA’s have got a N for example.

    Column AL has list of depot codes ie BA, BG, BL…
    Column AP has Y/N responses in it.

    =SUMPRODUCT((‘Orders Extract Data’!$AL$3:$AL$21384=”BA”)*(‘Orders Extract Data’!$AP$3:$AP$3519=”N”))

    Any Ideas?

    K

  83. Hui... says:

    @K
    Your ranges need to be the same length
    eg:
    =SUMPRODUCT(('Orders Extract Data'!$AL$3:$AL$21384="BA")*('Orders Extract Data'!$AP$3:$AP$21384="N"))
    or

    =SUMPRODUCT(('Orders Extract Data'!$AL$3:$AL$3519="BA")*('Orders Extract Data'!$AP$3:$AP$3519="N"))

  84. [...] replacing the MATCH part of the single criterion formula with SUMPRODUCT array multiplication, as descibed by Chandoo. Very generically that looks like: =INDEX(ColumnToIndex,SUMPRODUCT(Multiply a bunch of columns and [...]

  85. Bob Dyjak says:

    The answer to my question may lie in the 100+ replies to this post- I printed them out and will read through them as I await your response – but my particular question is whether SUMIF and SUMPRODUCT can be use  d in the same formula.  I want to be able to do a SUMPRODUCT only if certain criteria are met in another column (or columns) within the spreadsheet.  Is that possible?  I’m trying to avoid adding columns to do an intermediate calculation as it would be an extremely tedious task at this point. THANKS!

    • Bob Dyjak says:

      You can ignore this post – figured it out after reading through that SUMPRODUCT “does it all”.  Not sure I understand the need for the double minus sign, however – seems to work fine without it.

  86. Ahmed says:

    Handy info here, Thanks a lot to all for sharing their expertise. 

    I am stuck getting a sum of values based upon certain month. i.e. 

    Column A got Expense dates (e.g., 20-May-12, 01-Jun-12, 04-Jun-012 etc) 
    Column B got nature of expense (e.g., Business, Personal, etc)
    Column C got amount.

    I wanted to put all expenses under business head which occurs in the month of Jun in a particular cell. I am using following formula but without any luck. Any suggestion what i am doing wrong. 

    SUMPRODUCT(–(MONTH(E_Date)=6),–(Charged_To=”Company”),Amount)
    or 
    SUMPRODUCT(–(MONTH(A1:A10)=6),–(B1:B10=”Company”),C1:C10)

    Thanks
     

    • FSJ says:

      you can try the following formula

      Column A contains dates
      Column B contains nature of expense
      Column C contains amounts

      SUMPRODUCT((MONTH($A$2:$A$76)=6)*($B$2:$B$76=”Business”)*($C$2:$C$76)) 

      i have limited the data to 76 rows… you can change that value or try any other range (as long as all the ranges are the same) 

       

      • Ahmed says:

        Thanks FSJ, 

        It didn’t help, giving error “#Name?”.

        • FSJ says:

          SUMPRODUCT((MONTH($A$2:$A$76)=D2)*($B$2:$B$76=D5)*($C$2:$C$76)) 

          D2 = 6
          D5 = Business 

          if this does not help, try putting in
           arrayformula(SUMPRODUCT((MONTH($A$2:$A$76)=D2)*($B$2:$B$76=D5)*($C$2:$C$76)) )

          • Ahmed says:

            Thanks a Lot FSJ, Was out of town for some time, so couldn’t check it earlier. 

            Got it working now. However still unable to understand why we need to multiply each criteria. Shouldn’t sum product be doing this automatically? 

             

  87. Very neatly explained.
    Thanks!

  88. Marilyn Fleming says:

    Once again I reference this page and learn more.
    Hopefully one day I will be able to contribute and earn a donut!
    Thanks, folks.

  89. Srinivas says:

     Sub test_2()
    Status = Cells(3, 5).Value
    MsgBox Status
    Worksheets(“Sheet1″).Cells(4, 5).Value = _
    [SUMPRODUCT(--(Sheet1!$G:$G=E3,--(Sheet1!$J:$J=1))]
     End Sub
     In place of “E3″ i want to use “Status”

    Worksheets(“Sheet1″).Cells(4, 5).Value = _
    [SUMPRODUCT(--(Sheet1!$G:$G=Status,--(Sheet1!$J:$J=1))]

    I am getting the result as ” #NAME?” 

    Can someone help me with this

  90. [...] Excel SUMPRODUCT Formula – what is it, how to use it and detailed examples. (more on sumproduct) [...]

  91. [...] Without seeing your spreadsheet, my guess is you need to use SUMPRODUCT. Have a read of: Excel SUMPRODUCT formula – Syntax, Usage, Examples and Tutorial | Chandoo.org – Learn Microsoft Exce… It gives a good example and explanation of how it [...]

  92. KevinA says:

    Here’s one for you all… I have the following formula, already working in Excel 2010; BUT I need to ‘downgrade’ it to be compatible in 2003 (yes, s/w nine years old! hey-ho).
    =AVERAGEIFS(Data!S:S,Data!L:L,”Atherstone”,Data!T:T,”Service”,Data!C:C,”>=”&K3,Data!C:C,”<=”&K4)
    K3 is the start of a date range and K4 is the end.
    I’ve been told that SUMPRODUCT is the answer to all my woes, but I can’t for the life of me work it out. I know this a bit of a “do my homework for me?” question, but this is one of only 140 cells I need to convert (upgrading is off the table), so if someone fixes this for me, they can have my first-born (no, really – she’s a menace!).
    Think of it as a ‘challenge’, if that helps. Me, I’m on my third Martini already…
     

    • Hui... says:

      @KevinA
      Try: =SUMPRODUCT(Data!S:S* (Data!L:L="Atherstone")* (Data!T:T="Service")* (Data!C:C>=K3)* (Data!C:C< =K4)) / SUMPRODUCT((Data!L:L="Atherstone")* (Data!T:T="Service")* (Data!C:C>=K3)* (Data!C:C< =K4))

    • FSJ says:

      =AVERAGEIFS(B1:B14,A1:A14,C1)
      =SUMPRODUCT( (A1:A14=C1)*B1:B14)/COUNTIF(A1:A14,C1)

      same result

      see if you can extend it to fit your needs. 

  93. Mike T says:

    I don’t get the syntax of the double “–” in this formula..  (--(A1:A10="Luke Skywalker"),--(B1:B10="West")
    What are the "--"'s in front of (A1:A10) and (B1:B10) needed for?

     

  94. Hui... says:

    @Mike together double unwary serves to convert the arrays of True/False to an array of 1/0′s
    it could have been done as
     ((A1:A10="Luke Skywalker")*(B1:B10="West")

    Noting that multiplying the array by each other does the same thing 

  95. [...] to arrange two columns? A SUMPRODUCT formula can probably answer this for you, have a read of: Excel SUMPRODUCT formula – Syntax, Usage, Examples and Tutorial | Chandoo.org – Learn Microsoft Exce… Otherwise, include a screenshot of your spreadsheet before and after indicating what you are [...]

  96. Sadhana Singh says:

    Is it possible to alternately multiply and add in a single column.
    i.e I want to know the formula of =(A1*A2)+(A3*A4)+(A5*A6)+……(A101+A102) 

    • Chandoo says:

      @Sadhana: Welcome to Chandoo.org and thanks for your question.

      You can do it using a SUMPRODUCT and MOD formulas like this:
      =SUMPRODUCT(A1:A101, A2:A102, MOD(ROW(A1:A101),2))

  97. tbond says:

    Thanks, the — trick works great!!

  98. Kishore says:

    Thanks for enlighting us!!!!

  99. Zeynep Y?lmaz says:

    Dear List,
    I do need urgent help about SUMPRODUCT formula. My excel table has 2 numeric rows and 1 text row showing criteria. I want to multiply these numeric rows, according to the criteria row. The following formula didn’t work:
    =SUMPRODUCT((D1:BX1=”SCI”)*(D2:BX2)*(D4:BX4)) 
    I tried with comma instead of *
    May I request your help please.
    Thank you very much.
    Zeynep 

    • Hui... says:

      Zeynep
      Your formula should work ok
      So maybe check that your data doesn’t have any text values in the Numeric Rows or error messages in any of the rows

      Also check that calculation is set to Automatic

      If this doesn’t help, can you copy/paste say A1:J3 here so we can see some data

      • Zeynep Y?lmaz says:

        Hui
        I checked, there is no text in the cells except criteria row. My sample data,
            A     B      C      D    E    F        G    H      I       J      K      L….. CA  CB
        1                SCI SCI CONF OTH SCI SCI CONF CONF OTH OTH   Weighted 
        2                0,2  0,1  0,3   1,1   1,3   0,5 0,0   1,2     0,4  1,9   SCI CONF
        3            ——  activity heading row   ——–                           TOTALS
        4    name-1  1      1                   2                    1       1    1 ….. formula          
        5    name-2  8      2     1   1       3     1       2                      ……formula
        …. name-n   1            3   2       1      5                              ……formula
         
        (Col.A idle)
        I try to calculate : for each name, weighted activity by SCI, CONF and OTH on this data matrix which has ~100 names and ~80 activities.
        Thanks again 

        • Zeynep YILMAZ says:

          Hui,
          Problem is solved when I read the examples definedin the previous messages.
          The double hyper completed the formula, the last view :
          =SUMPRODUCT(–($C4:$BX4*$C$2:$BX$2);–($C$1:$BX$1=”SCI”)) for SCI case.
          Thanks a lot

           

  100. Zach says:

    I have tried using your formula in different forms but it doesn’t seem to be working, can anyone help me out?

    I have this in my cell:
    =SUMPRODUCT(–(Sheet1!$F$3:$F$19=Sheet2!B11),Sheet1!$I$3:$I$19)

    the first list is an array of months in the exact same format as Sheet2!B11, I’ve even done a cell to cell check to make sure it comes back as 1 which it does.  The second list is numbers which need to be added.  

    Want to add up total expenses for each month, but for some reason it just keeps coming back $0 when it should be $525.  Any help would be much appreciated.  Thx. 

  101. Zach says:

    Nevermind.  got it!  Needed to put the MONTH in there to compare apples to apples.  Final equation is this:
     =SUMPRODUCT(–(MONTH(Sheet1!$F$3:$F$19)=MONTH(Sheet2!B11)),Sheet1!$I$3:$I$19)

    Thanks again 

    • Dante S. Valenzuela says:

      As my best practice, I always segregate each array with closed parenthesis () and put a space between each array to avoid confusion and better readability.

      =SUMPRODUCT( (MONTH(Sheet1!$F$3:$F$19)=MONTH(Sheet2!B11)) * (Sheet1!$I$3:$I$19) )

      Likewise, I find the use of named array very helpful especially for variable source.  So if you create a named variable range as below:
      Date = OFFSET(Sheet1!$I$3,0,0,COUNTA(Sheet1!F:F)-1,1)
      Amount = OFFSET(Sheet1!$F$3,0,0,COUNTA(Sheet1!F:F)-1,1)
      Note that I use COUNTA(Sheet1!F:F)-1 for both so that height are equal for all array in the sumproduct formula.

      Below will arrive at same result as above.
      =SUMPRODUCT( (MONTH(Date)=MONTH(Sheet2!B11)) * (Amount) ) 

      Try adding records after Sheet1!F19 

  102. [...] SUMPRODUCT formula: Unleash the full power of Excel array processing by using SUMPRODUCT. [...]

  103. mybaso says:

    Hi, I need help.
    I have a table of data. On the left side is Site and cost centre name then we have actual cost by month, budget by month for each of this cost centre by site.
    Esample ( In here showing only two months but in reality I have twelve months data for actual and for budget) 
    Site          cost centre    Oct12Act   Nov12Act    Oct12budget   Nov12Budget 
    Auckland   Sales            $100        $200           $150             $150
    Auckland  Marketing       $200        $250           $500             $500
    Hamilton   Sales             $50          $75              $100           $100
    On another worksheet I have a dashboard report. When user select the month of Oct12, I want to be able to bring the ttal Actual for Auckland site (total sales & Marketing together) in the Actual column of te dasboard. SImilary, the budget column in the dasboard should be populated by the total budget for Oct for Auckland (total sales & Marketing).   
    I am managed to bring the result of Oct act and Oct budget if only I want total for Auckland Sales. But I cannot do total Auckland sales & Marketing.
    Would appreciate any help.
     

  104. [...] and was elated to find out that MS had built this function into 2013 pivot tables. I generally use SUMPRODUCT to get to my answer, but some of my files take hours to calculate with this formula. Since I am [...]

  105. senthil says:

    =IF(G$2<>”X”,””,IF(SUMPRODUCT((ListFiles_Step30!$A17:$A10016=G$3)*((ListFiles_Step30!$B17:$B10016=ETL_FLB_Instances!$B22))*((ListFiles_Step30!$J17:$J10016<>”Error_Format”)))=G$1,”OK”,”KO”))
     
     
    i need to know functionality of this formula plz explain

  106. senthil says:

    =IF(G$2<>”X”,””,IF(SUMPRODUCT((ListFiles_Step30!$A17:$A10016=G$3)*((ListFiles_Step30!$B17:$B10016=ETL_FLB_Instances!$B22))*((ListFiles_Step30!$J17:$J10016<>”Error_Format”)))=G$1,”OK”,”KO”))
     
     
    how it is working in array ? please explain me 
     
     

  107. NickE says:

    This formula was a godsend, thank you!  after two days of googling and trying multiple formulas, this one worked a charm. Only one problem now though – every time I make any change at all the file takes 5 minutes to save, as it recalculates all the formulas. I would prefer not to hard number the formluas as I want to maintain the lookup function so any underlying data changes are captured in my result. Any ideas how I can speed things up?

    • FSJ says:

      Hi NickE,
      One of the main reasons for slow response on sum-product (in my case) and performance issues of excel itself is when i set the entire column as data rather than just the required range.
      something like A1:A155 rather that A:A *might* be the solution. it worked for me.

  108. NickE says:

    Thanks FSJ, that worked! This forum is reallllly cool Chandoo B)

  109. edna says:

    I came looking for some help, and you gave me “homework” instead of an answer?
    Screw you.
     

  110. Mike says:

    Thank you!  This is SO much better than the official Microsoft description of SUMPRODUCT — which is virtually useless.
     
    Great article.

  111. [...] feedback. The double negative — converts the TRUE and FALSE within the formula to 1's and 0's. Excel SUMPRODUCT formula – Syntax, Usage, Examples and Tutorial | Chandoo.org – Learn Microsoft Exce… [...]

  112. Bart says:

    Chandoo, It’s a big help. Have been figuring out how to use sumproduct till I saw this site. Thanks a lot!

  113. Vincent says:

    Dear Sirs,

    I would like to get a multiplication value as per the following

    c1 having value (10) in sheet1 * c1 (20) in sheet2, if a1(“pet”) in sheet1 matches with a1(“Pet”) in sheet2 and give the result in d1 in sheet1.

    Kindly help me with the formula.

    Vincent

    • Hui... says:

      @Vincent

      I think this is what you said:
      Sheet2!C1 = 20
      Sheet1!C1 = 10*Sheet2!C1 = 200
      Sheet1!A1 = Pet
      Sheet2!A1 = x

      Sheet1!D1 = You want an answer here

      You haven’t told us:
      1. What the relationship between C1 (on either sheeet) or how that relates to A1 (also on either sheet)
      2. What do you want in Sheet1!D1 when something happens ?

  114. Manoj Gupta says:

    Dear Chandoo
    Very useful tip.

  115. Bhushan says:

    We cam also use the DSUM formula i guess. to find out the lookup of sevral conditions and sum based on conditions, the formula is helpful for ddesigning dashboards

  116. Thiago says:

    Ok maybe this is well known but I’ve decided to leave it here as I use it in a daily basis.

    The sumproduct + responsive named ranges is one the most powerful tools in Excel.

    The sumproduct is really well explained by Chandoo here so I will just talk about the responsive named ranges.

    For example you want to build a resume table form a constantly growing set of data (both horizontally and vertically), where the first column has repeating identifiers that you use for your calculations and the first row has all the attributes you have from those identifiers set these 3 named ranges:

    identifiers
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$900000))

    attributes
    =OFFSET(Sheet1!$B$1,0,0,1,COUNTA(Sheet1!$B$1:$ZZ$1))

    data
    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$900000),COUNTA(Sheet1!$B$2:$ZZ$2))

    Then simple sumproduct it to fill your resume table with (considering column A has the identifiers and row 1 the attributes you want to pull)

    =sumproduct((identifiers=$a2)*(attributes=b$1),data)

    Then just copy it on all the cells you want filled.

    Hope it helps

  117. Abigail says:

    Thank you for this tutorial! I am so excited to have it up my sleeve now, it is EXACTLY what I was looking for! :D

  118. Charles A says:

    I have written a nice working SUMPRODUCT formula – =SUMPRODUCT(–([DLP1.xlsx]InvRegister!$B$4:$B$99=”Architect”),–([DLP1.xlsx]InvRegister!$J$4:$J$99=G$2),–([DLP1.xlsx]InvRegister!$K$4:$K$99=G$3),–([DLP1.xlsx]InvRegister!$L$4:$L$99=$A6),([DLP1.xlsx]InvRegister!$N$4:$N$99))

    I’d like to replace “Architect” in first part of formula with a cell reference (A1) so I can change the role in A1 and have formula change accordingly.

    When I put A1 in place of “Architect” then I get no values returned by formula…

  119. Charles A says:

    Brainwave, solved it myself replaced Architect in A1 with =”Architect” and it works, is there a way to just have Architect in A1 or is my workaround the solution?

  120. Laszlo says:

    Usually if I use (array1=value1) in my formula it counts as an array formula, but this example works just by pressing enter. Why is that?

  121. Mohammed says:

    Thank you. This works

  122. Umang says:

    Hi Chandu, thanks for the explanation above. It proved very beneficial.
    Further, i have a query in the example explained above:

    Can i use the sumproduct formula if i have two conditions in the SAME COLUMN. Like:
    to calculate the total sales for Luke and Hansolo both inclusive, in the region WEST..
    I am using this formula but not getting 0 as the answer:
    SUMPRODUCT((A1:A10=”Luke”)*(A1:A10=”Hansolo”)*(B1:B10=”West”)*C1:C10)

    • Hui... says:

      @Umang
      Yes you can use multiple Conditions
      But you can’t do it as you suggested
      =SUMPRODUCT((A1:A10=”Luke”)*(A1:A10=”Hansolo”)*(B1:B10=”West”)*(C1:C10))
      This is because the the * between each range is acting like an “And”
      So A1:A10 can be = to Luke but it can’t also be equal to Hansolo at the same time

      You can try something like:
      =SUMPRODUCT((A1:A10=”Luke”)*(B1:B10=”West”)*(C1:C10))+SUMPRODUCT((A1:A10=”Hansolo”)*(B1:B10=”West”)*(C1:C10))

      You can also do 2D lookup/sums
      You can read about those here:
      http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

  123. umang says:

    @Hui
    Thanks for explaining where I was going wrong. Ideally what I was doing should throw an error because that is not a viable condition. Neway I understood the alternative u suggested. Thank you :-)

  124. Tim says:

    Hey Chandoo. This is great. I have a slightly different issue I am currently dealing with.

    My current formula is this:
    =SUMPRODUCT(($AK$1:$DS$1=$A23)*($AB$2:$AB$4066=C$1)*$AK$2:$DS$4066)
    and that works perfectly. The formula works to find the values in Columns B, D and F as they are named uniquely (in the formula they are referred to in A23, A24 and A25).

    However, I also need to return the values for column C, E and G which are all named identically. Is there way to offset the above formula? Or to look at the column next to where the condition is met?

    Thanks.

  125. Pushpa says:

    Hi Chandoo,

    I was looking for a formula where you can find the value of a cell based on two criterias – one in a row and the other in a column (i.e. the intersection of the row and column).

    Region ABC XYZ PQR MNO EFG
    West 15000 17000 20000 150 9800
    East 11650 14500 56900 8640 8940
    South 5200 8960 23560 96230 36450
    North 7450 23000 9770 74500 78940
    Mid 6900 1740 6630 2560 25630

    Region ABC EFG MNO PQR XYZ
    West ??
    East ??
    South
    North
    Mid

    Like the one stated above. I think it can be done using Index and Match function but I am finding it confusing. Can’t do it manually since the data on which I am working is huge. Can you help me?

    • FSJ says:

      =VLOOKUP(G2,A2:E5,MATCH(H1,B1:E1,0)+COLUMN(B1)-(COLUMN(B1)-1),FALSE)

      where
      G2 contains required row header (north)
      A2 to E5 is your complete data set with row headings (exclusive of column headers, i.e. ABC, EFG,…)
      H1 contains the column head required (i.e. PQR)
      B1 to E1 has the actual column heads (i.e. ABC, EFG,..)
      B1 is the first column heading cell

      • Pushpa says:

        Hi Chandoo,

        Thanks for the prompt reply. It does work but can you elaborate on how the formula works? Also how would you use the above formula to auto fill the other cells in the example (i.e. we drag or copy the formula to other cell to give the result for the modified criteria automatically)

        • Supplantor says:

          Hi Pushpa,
          You can do this using index(match(),match())

          =INDEX($C$3:$G$7,MATCH($B12,$B$3:$B$7,0),MATCH(C$11,$C$2:$G$2,0))

          Where your top table starts at B2 & your bottom table starts at B11 the above formula goes in C12.
          This can be copied to other cells in the target table.

  126. […] a "–" for the second BOOLEAN half of the formula. Here are a couple of URL's which might help Excel SUMPRODUCT formula – Syntax, Usage, Examples and Tutorial | Chandoo.org – Learn Microsoft Exce… CPearson.com Excel Newsletter […]

  127. ajs432 says:

    I see a huge potential with my work to use this formula especially when you replace “Luke Skywalker” with a reference cell, essentially using this as a Vlookup with multiple arguments.

    I’ve tried it twice now and just can’t get to work. The test VLOOKUPS I tried to make sure that there wasn’t some sort of formatting error making it a reference error. but I keep getting the same #REF.

    • ajs432 says:

      Let me Explain Further
      I have Sheet Data
      Column A is Branch
      Column B is Product Class
      Column C is SKU
      Columnc D is QTY Billed

      Sheet Report
      Column A is Branch
      Column B is Product Class
      So my formula (in Sheet Report) looks like
      =SUMPRODUCT(–(Data!A$2:A$1000=A2), –(Data!B$2:B$1000=B2), Data!D$2:D$1000)

      A Vlookup calling A2 in the same range works and so does B2, but get #REF when I use this.

  128. HMM says:

    Hello,

    I have been attempting to get this figured out and I can not seem to figure it out it’s not doing what I want it to :(

    I am grabbing data from one tab to display on another tab:

    I am needing if a range of cells =”Jane Doe” and another range of Cells =”January” to also grab to points from another range adding them up so the end tells me Jane Doe in the month of January had a total of 600 points received…

    Any help is greatly appreciated

  129. HMM says:

    I have been attempting to get this figured out and I can not seem to figure it out it’s not doing what I want it to :(

    I am grabbing data from one tab to display on another tab:

    I am needing if a range of cells =”Jane Doe” and another range of Cells =”January” to also grab to points from another range adding them up so the end tells me Jane Doe in the month of January had a total of 600 points received…

    Any help is greatly appreciated

    • Hui... says:

      @Hmm

      Can you post a sample file ?

    • fsj says:

      there a few limitations when doing this.
      1. the selected data in column 1 (names), column 2 (months) and column 3 (points) should be the same number of rows.

      2. assuming that the data is such that Sheet1 has 3 columns:
      A: names of people
      B: names of months
      C: points earned

      in sheet 2, we can write
      =SUMPRODUCT ( (Sheet1!A2:A26=Sheet2!A2) * (Sheet1!B2:B26=Sheet2!B2) * Sheet1!C2:C26 )
      Given that in Sheet 2, cell A2 contains the name of the person you want the data for (for example Frank Doe) and B2 contains the month for which you want the data (for example March).

      you can also do

      =SUMPRODUCT((Sheet1!A2:A26=”Frank Doe”)*(Sheet1!B2:B26=”March”)*Sheet1!C2:C26)

  130. Lee says:

    I am attempting the following:

    =SUMPRODUCT(–($D$2:$D$15=J$3),–($E$2:$E$15=K$2),$F$2:$F$15)

    But i am getting 0 returned

    Is it possible to reference a cell rather than a string like:

    =SUMPRODUCT(($D$2:$D$15=”FR”)*($E$2:$E$15=”Pierre”)*($F$2:$F$15))

    Which does return data?

  131. Lee says:

    When I try:

    =SUMPRODUCT(($D$2:$D$15=”FR”)*($E$2:$E$15=”Pierre”)*($F$2:$F$15))

    I get a result but when I change the strings to a cell reference it returns a 0.

    Any idea what I am doing wrong?

    • Hui... says:

      @Lee
      I’d try:
      =SUMPRODUCT(($D$2:$D$15=”FR”)*($E$2:$E$15=”Pierre”),($F$2:$F$15))
      Copy and paste this
      Then edit the formula and manually retype the ” characters

      Also check the Data in Column D & E doesn’t have spaces or other non-printing characters in the cells
      Check that Column F has numbers and that they aren’t text
      Select two cells in Column F, the lower right Status Bar of the Excel screen should show the total if they are numbers and the count if they are text

      Can you post a sample file for more specific help ?

    • fsj says:

      1. make sure that you are using the same text case (upper case and lower case)
      2. make sure there are no spaces in the reference cells (at the start or at the end of the text)
      3. try CTRL+SHIFT+ENTER (entering an array formula)

      can you share the data is possible?

    • fsj says:

      1. make sure that you are using the same text case (upper case and lower case)
      2. make sure there are no spaces in the reference cells (at the start or at the end of the text)
      3. try CTRL+SHIFT+ENTER (entering an array formula)

      can you share the data if possible?

  132. Lee says:

    I was just testing SUMPRODUCT as it is a formula I’ve never used before and was running into a few issues – a Network reboot has left me without data so let me try the suggestions above and will report back

  133. […] Function SUMPRODUCT can help, have a read of: Excel SUMPRODUCT formula – Syntax, Usage, Examples and Tutorial | Chandoo.org – Learn Microsoft Exce… […]

  134. […] vogel997, it worked!! Thank you very much. I'm now reading this site to understand how it works. (Excel SUMPRODUCT formula – Syntax, Usage, Examples and Tutorial | Chandoo.org – Learn Microsoft Exce…). Have a great day, guys! Thanks for your […]

  135. Mike says:

    Hi,

    i’ve tried using this to return a time value for the total logged on time of call centre agents, but it is returning a zero. I have checked it against the number of calls and that works fine, but for some reason with a time value it is returning zero – any ideas?

    thanks
    Mike

  136. Sachi says:

    Thanks..That was really helpful

  137. Fazil says:

    nice one, very use full

  138. Ali says:

    Hello,
    This sumproduct formula is not working with me in excel 2010. I tried the same way as it has been taught above, but however the value or the answer returned is always zero. If you can please help me out.

    Thanx

  139. Mustaque says:

    Hi Chandoo and Team,

    I am learning this Sumproduct with one of your sample data (vlookup-on-multiple-conditions.xl).

    SUMPRODUCT(lstNetSales,–(lstSalesman=”Joseph”),–(lstRegion=”North”),–(lstProduct=”FastCar”), –(lstMonth=valMonth)) returns 1592

    whereas with the same formula when I replace ONLY the valMonth with the actual date format (1/1/2007) it returns 0
    SUMPRODUCT(lstNetSales,–(lstSalesman=”Joseph”),–(lstRegion=”North”),–(lstProduct=”FastCar”), –(lstMonth=”1/1/2007″)).

    Also when I use the table reference
    SUMPRODUCT(tblSales[Net Sales],–(tblSales[Salesman]=”Joseph”),–(tblSales[Region]=”North”), –(tblSales[Product]=”FastCar”), –(tblSales[Month]=valMonth)) returns 1592

    SUMPRODUCT(tblSales[Net Sales],–(tblSales[Salesman]=”Joseph”)*–(tblSales[Region]=”North”)*–(tblSales[Product]=”FastCar”)*–(tblSales[Month]=-valMonth)) returns 0.

    Please let me know how this calculates.

    Appreciate your help.

    Thanks
    Mush

  140. Mustaque says:

    Any help on the previous queries I posted with using the variable and the actual values with SumProduct ?

Leave a Reply