SubscribeWhat is Excel SUMPRODUCT formula and how to use it?
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
The 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 ...)
So, 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,
- Use an array formula
- Use a pivot table [what is a pivot table?]
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:D10is 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.
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
| ||||
|
| ||||
|
Leave a Reply
 |
PHD Gets a Facelift (again) | Chart this Sales Data and get an iPod Touch [Visualization Challenge #2] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
151 Responses to “What is Excel SUMPRODUCT formula and how to use it?”
Nice explanation Chandoo, I love it. It’s “almost” crystal clear for me.
Can you explain the use of — before the test ?
@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.
Another alternative for this example is to use SUMIFS function, but it is available only in excel 2007 & 2010
Nice post Chandoo!!
Is there any limit to the number of arguments we can use with SUMPRODUCT function?
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.
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?.
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 – - …”
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?
Great post. The array part of this post will come in extremely handy at work.
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
Seconding the use of SUMIFS if you have 2007 or higher. I personally find it much more intuitive.
Hi Brian, Hi Shandoo,
for more details and considerations …
here from an Excel Guru :
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
;o)))
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?
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…
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).
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
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.
@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.
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?
@Jeff, in the donut economy things never cancel out. They just get added up, because donuts are absolute(ly delicious).
I seriously wonder how many people-hours you saved with this one post alone Chandoo.
I guess it leaves more time for virtual donuts.
@Eric.. that is so sweet (and not in the heart killing donut sweet way). Thank you
[...] used SUMPRODUCT liberally to summarize the gift data to show us “how many people got the gifts”, [...]
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
bummer, example data formatted ugly….imagine rows and columns, rows and columns
@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.
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)))
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.
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.
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
[...] SUMPRODUCT | VLOOKUP | SUMIF & COUNTIF [...]
[...] 12: Introduction to SUMPRODUCT formula [...]
[...] 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 [...]
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…
@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 ?
[...] 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. [...]
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.
In my lists there are a lot of =NA() Values. Therefore Sumproduct returns NA aswell, is there any workaround besides converting to empty cells?
@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.
Thx Chandoo!
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
@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/
[...] Sumproduct: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/ [...]
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.
[...] SUMPRODUCT Formula [...]
[...] 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 [...]
[...] just love SUMPRODUCT Formula. So I use it whenever I can. But you may like other techniques. So please tell me how you would [...]
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.
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!
[...] use of the Sumproduct function for doing multiple criteria Sum If’s is possibly one of the greatest extensions of an Excel [...]
Nope i dont get it.
Large coffee and try again tommorow
[...] do you model this in excel fast? In this tutorial we understand how you can use SumProduct() formula to do [...]
Hello Mr. Chandoooo.com,
Thankx a ton!!!!
The formula works and it completes my work in secs…..
Thankx a lot one again
[...] wrap this in a lovely SUMPRODUCT formula so that we can check for palindrome-ness of B1 using =IF( SUMPRODUCT( ( [...]
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!!!!!
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
@LornaB
Check that all your Ranges are the same size (length and width)
Ok – So all the ranges are the same length. Still no go. So what would happen if there are blank fields?
@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?
=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!
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?
@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).
@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?
: (
@Lorna
Multiply all your data by 1 using paste Special Multiply
That will fix it.
@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……….
Hi Lorna,
I use data that is exported from a webpage and find that some data does not work with sumproduct until I clean it by running the Trimall macro from http://dmcritchie.mvps.org/excel/join.htm#trimall. Not sure if it will help but worth a try
What is the need for hte “–” in the sumproduct formula?
Also, having trouble getting an answer..I am getting an error. Please help.
@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?
@chandoo
can u give example work sheet of above example
@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
@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
@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
@Rahul
Try:
=SUMPRODUCT(1*(E2:E20=”tikka”),(H2:H20))
or
=SUMPRODUCT(1*(E2:E20=”tikka”),(S2:S20))
@hui
above formula
is showing same error #VALUE
When you copy the above , retype the ” characters manually
[...] recently asked for an example in Excels Sumproduct Formula post; Comment No. [...]
Hi!! Hui!!
What’s the formula if sumproduct return text?
@Ramki
Sumproduct is a Numerical Function that can return a number or True/False
Can you explain your application?
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
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?
@Greg
Try:
=SUMPRODUCT(([Test.xlsx]Sheet1!$C$2:$C$101792=A11)*[Test.xlsx]Sheet1!$F$2:$F$101792)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
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
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.
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!
Hi clif
in fact the above link was already here for a long time :
http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/#comment-84704
;o)))
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??
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
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
=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.
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
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?
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
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!
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!
Catherine
@Catherine
You may also want to have a look at:
http://chandoo.org/wp/2011/12/21/formula-forensics-no-007/
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
and
http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html
Hui,
Thanks a million. This is terrific information. I”m heading towards being an expert Excel user. Who knew?
Catherine
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.
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)
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
@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"))[...] 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 [...]
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!
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.
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
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)
Thanks FSJ,
It didn’t help, giving error “#Name?”.
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)) )
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?
Very neatly explained.
Thanks!
Once again I reference this page and learn more.
Hopefully one day I will be able to contribute and earn a donut!
Thanks, folks.
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
@Srinivas
Can you post a file showing this?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Can you please help me in deleting my second post where my intention was to provide details w.r.t my query but format was wrong
I have uploaded the images of two sheets .
URL:
http://tinypic.com/r/2mm5r7k/6
http://tinypic.com/r/2q03ihf/6
Sub test_2()
Status = Cells(3, 5).Value
MsgBox Status
Worksheets(“Sheet1?).Cells(4, 5).Value = _
[SUMPRODUCT(--(Sheet2!$G:$G=E3,--(Sheet2!$J:$J=1))]
End Sub
In place of “E3? i want to use “Status(variable)”
Worksheets(“Sheet1?).Cells(4, 5).Value = _
[SUMPRODUCT(--(Sheet2!$G:$G=Status,--(Sheet2!$J:$J=1))]
I am getting the result as ” #NAME?”
[...] Excel SUMPRODUCT Formula – what is it, how to use it and detailed examples. (more on sumproduct) [...]
[...] 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 [...]
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…
@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))=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.
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?@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
[...] 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 [...]
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)
@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))
Thanks, the — trick works great!!
Thanks for enlighting us!!!!
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
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
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
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
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.
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
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
[...] SUMPRODUCT formula: Unleash the full power of Excel array processing by using SUMPRODUCT. [...]
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.
[...] SUMPRODUCT formula [...]
[...] 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 [...]
=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
=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
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?
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.
Thanks FSJ, that worked! This forum is reallllly cool Chandoo B)
I came looking for some help, and you gave me “homework” instead of an answer?
Screw you.
Thank you! This is SO much better than the official Microsoft description of SUMPRODUCT — which is virtually useless.
Great article.
[...] 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… [...]
Chandoo, It’s a big help. Have been figuring out how to use sumproduct till I saw this site. Thanks a lot!
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
@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 ?
[...] Introduction to SUMPRODUCT formula [...]
Dear Chandoo
Very useful tip.