Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.I recently found out how to do the equivalent of a SUMPRODUCTIF, a formula that doesn't exist in Excel. Here’s the detail on how.
First of all, some context.
SUM does exactly what it says on the tin. It sums a range of cells.
SUMPRODUCT is a lesser-known function, and sums a set of products. (a1*b1*c1) +(a2*b2*c2) + [...] + (an*bn*cn)
SUMIF sums a range of cells if a certain condition is true. For example, sum people’s salaries in a column if they are based in London.
I wanted a way of putting a condition on the SUMPRODUCT function. That is, I wanted to do a sum of the products only where a condition for those rows held true.
To make the formulae more manageable, let’s assume I’ve named a few ranges:
- conditions is the column of data that I want to validate the condition against
- range1 and range2 are the two ranges that I want to do the conditional SUMPRODUCT on.
And let’s assume I only want to do this if the values in the conditions range are "London".
The formula would read:
=SUMPRODUCT(--(conditions="London"),range1,range2)
Basically, the first term acts as a range in its own right, taking the value of 1 (for London) and 0 (for anything else). This means that it’s not actually doing a conditional SUMPRODUCT, but instead it’s multiplying the entries for which the condition fails by zero, which has the same effect. The double minus at the beginning is to ensure that the first argument is read as a formula.
Hi Dan. Thanks for posting this solution as I was facing exactly the same problem and looking for the same solution. Great work. After implementing your formula into my spreadsheet I got exactly what I was looking for. Thanks a million. Raf
Sweet! Works like a dream.
Thnx
Fantastic!
Hi Dan,
I have a formula that I am having tremendous difficulty with. I keep records for a cricket team on overs bowled (6 balls make 1 over) and have used the following formula to great succes so far - =SUMPRODUCT(INT($H$17:$H$57))+INT(SUMPRODUCT(MOD($H$17:H57,1))/0.6)+MOD(SUMPRODUCT(MOD($H$17:$H$57,1)),0.6)
The problem now is that I wish to record the statistics for both home and away games and therefore need to include a sumif scenario to which I have had no luck to date.
Any suggestions?
Best regards,
Riaan
Hello, there are mornings where everything works perfectly... thanks for your formula!
Fantastic, you have saved many hours of my life ! 🙂
Thanks !
added your page in my favorites..
What if the condition was not a static? Like if instead of "London" in your example, I wanted ">= cell A3"? In a normal array function, I would use ">="&A3, but that is not working. Thanks.
Hi Blaine,
I just saw your comment on this post:
https://www.wizardofexcel.com/2011/05/09/sumproductif/
The formula works without the quotes.
=SUMPRODUCT(--(A1:A3>=A7),B1:B3,C1:C3)
So if "London" is in A7, then this picks up "London" and "New York" (given that N is "greater than" L).
Hope that helps.
how to put multiple condition with sumproduct
SUMPRODUCT(--('CMB SS data'!A:A=Sheet1!B12)AND('CMB SS data'!AD:AD=PP)),'CMB SS data'!N:N,'CMB SS data'!F:F)
This is not working
Try using like this. Instead of AND, make them two conditional arrays. SUMPRODUCT(--('CMB SS data'!A:A=Sheet1!B12),--('CMB SS data'!AD:AD=PP),'CMB SS data'!N:N,'CMB SS data'!F:F)
Hi Dan,
Thanks for sharing the formula, but what what can I do if instead of one condition I have two or more?
Thanks
I think the best approach here, Josefina, would be to create a dummy column using an IF. =IF(and(A1="London",A2>=100),"Yes","No")
Thanks Dan. I will have a go!
Thank you Dan that was very helpful and saved me from writing some VBA code for this small project! I like the approach and am sure I can apply it to other "if" situations in the future.
Brilliant! Super clever, and thanks for publishing this tip!
Why the "-" minus before the condition, my total is negative now.
I know how tom solve that but just curious.
The double minus at the beginning is to ensure that the first argument is read as a formula. Note, it's a double minus, not a single minus.
Stupid question then - why not a plus?
I have no idea. Please direct that question to Microsoft. 😉
The double-minus is the confusing bit.
This is one of the niftiest Excel tricks I have ever seen! Thank you for posting such a brilliant, elegant solution!
Hi Dan, love this solution. However, I need to expand it to include multiple conditions, and I can't use the dummy column for my spreadsheet. Any other solutions?
Nevermind, I got it...thanks!
Scott, can you share the solution for doing a SUMPRODUCT satisfying multiple criteria without the need for a dummy column.
Slowpoke, but someone, like me, might come across this and looking for a solution:
=SUMPRODUCT(--(condition="London")*(condition2="Soho"),range1,range2)
=SUMPRODUCT(--(city="London")*(area="Soho"),range1,range2)
Hey Dan.
You're the bees knees: this works a treat for me. It also tickles my fancy as it is a neat solution that does away with a helper column.
Hi Dan,
Need your help in getting the weighted attrition %, from a daily data to monthly. Ex I have Jan -Dec data and I need to get the monthly attrition rate.
Thanks in advance
Apologies for the delayed response.
There are various methodologies for creating attrition percentages. The most common is to use the average of the HC from the beginning of the period and the end of the period as the basis. So, for example, if you had 10 leavers in a quarter, and the HC at the beginning of the quarter was 90 and at the end it was 110, then you'd use (90+110)/2, or 100 as the basis. So your attrition rate for the quarter would be 10%; or 40% when annualised.
Much better to go off raw attrition numbers rather than daily percentages if you have those figures.
additional data, I have daily attrition % and HC
I have data (example below) with #VALUE! error that I would like to sumproduct. Data is much longer with many #VALUE! errors that I cannot correct. Can you help?
0.208% #VALUE!
0.193% 82.2%
0.285% 44.1%
0.150% #VALUE!
0.054% 31.6%
Very delayed response. Sorry. The best way here is to create a dummy column that replaces the #VALUE! entries with zeroes.
=IF(ISERROR(B2),0,B2)
Hi Dan - Been working on numbers for the past couple of years and it's the first time i came across this formula. Very helpful! Thanks muchos!
Wicked formula. Much appreciated.
Thank you so much for posting this!!! Explanation for the double negative was very helpful.
I am so happy that experts like you solve tricky problems like this one - what a great solution.
Thanks Dan
Thanks a hundred billion !!! I wish I had seen this earlier 🙂
Holy moly.
I honestly thought I'd be googling for hours to figure this out. You're a master!
This is exactly what I needed! Thanks for the post!
You are totally awesome, I love this formula. Thank you for sharing your awesomeness!!!!!
This is great! Saved me so much time. Thanks!
Thanks for the tip, it helped me a lot!
Brilliant, thanks!
I am using below formula
SUMPRODUCT(--($B1=Sheet1!$G$10:$G$155),sheet1!$Q$10:$Q$155)
This is correct working for sum of more than one material from another sheet. but now I want to add a condition i.e. if result of this formula is >0 then ok otherwise 0
IF(SUMPRODUCT(--($B1=Sheet1!$G$10:$G$155),sheet1!$Q$10:$Q$155)>0,"OK",0)
Hi Dan,
I am using below formula and keep getting a #VALUE!
=SUMPRODUCT(--('Daily Entry'!$A:$A>=SOP),--('Daily Entry'!$A:$A<=EOP),'Daily Entry'!B6:B9,'Daily Entry'!C6:C9) SOP and EOP are dates so essentially trying to multiply two columns between the specified date ranges.
The ranges need to be the same size. So you either need:
=SUMPRODUCT(--('Daily Entry'!$A:$A>=SOP),--('Daily Entry'!$A:$A<=EOP),'Daily Entry'!B:B,'Daily Entry'!C:C) or =SUMPRODUCT(--('Daily Entry'!A6:A9>=SOP),--('Daily Entry'!A6:A9<=EOP),'Daily Entry'!B6:B9,'Daily Entry'!C6:C9) Hope that helps. Dan.
I know this is an older post but this is an outstanding trick. Well described, easy to implement, and quite effective.
You are a beautiful person!
i have one list of 35 product and in which list i distribute 100 percent budgut value of two condition. one condition is that 30 persent of budgut value is given only 6 product and remain 70 persent is given remain product what formulla i can put to solve this problem in excel.
Difficult to say without some further details, Kamal. Feel free to email me.
Just piling on about 4 years after this originally posted to say thank you! I am now unstuck!
Thanks. It was a very helpful recommendation
in a certain column, if it contains zero, i want to force the sum product to be zero. Is this possible?
Certainly:
=IF(COUNTIF(C:C,0)>=1,0,SUMPRODUCT(--(conditions="London"),range1,range2)
This checks for a zero in column C. If there is one or more, then it'll return a zero, otherwise, it'll return the SUMPRODUCT.
Hope this helps, Mostafa.
Thank you so much Dan. It was really a great help.
Hello Dan
in a certain column, if it (column D)contains zero along another column (column C) that has a value of 0 or 1, i want to force the sum product to be zero, iff the other column (column C) contains a 1. If column C contains a 0 than it should not affect the sumproduct even if column D contains a 0. Is this possible?
Could you include MOD($C:$C+1,2) as another argument in your sumproductif?
This would return a 1 where column C was 0 (therefore not affecting the product for that record) or a 0 where column C was 1, thus making the product 0.
Hello Dan
How could I incorporate the MOD into the below formula?
["in a certain column, if it (column D)contains zero along another column (column C) that has a value of 0 or 1, i want to force the sum product to be zero, iff the other column (column C) contains a 1. If column C contains a 0 than it should not affect the sumproduct even if column D contains a 0. Is this possible?"]
=IF(COUNTIF(C:C,0)>=1,0,SUMPRODUCT(--(conditions="London"),range1,range2)
Dan
I solved it....
Thank you so much for all the help. Ignore my previous text.
Mos 🙂 🙂
I have a slight variation on the equation that is giving me trouble.
if I want the >= to be variable I get issues.
=SUMPRODUCT(--(L:L&F47&(G47*225)),L:L,M:M)
Where F47 will be >= or <= or > depending how I drag the formula. I get a #Value error.
This works great:
=SUMPRODUCT(--(L:L>=(G47*225)),L:L,M:M)/225
Why can't I reference a cell to the >= sign? If I follow steps of the formula, I see it's in there as expected.
Works on sumif as my condition:
=SUMIF(L:L,F46&G46*225,N:N)/225
Thanks a lot
Great solution, thanks
I'm trying to get this to work with a wildcard for the condition e.g. "SUB*" for any value that starts with SUB, but it only calls the cells that only include SUB, not those that say SUB - A1 or SUB - A2. Any advice?
=SUMPRODUCT(--(C18:C29="SUB*"),F18:F29,AL18:AL29)
Never mind I got it.
=SUMPRODUCT(--(ISNUMBER(FIND("SUB -",C18:C29))),F18:F29,AL18:AL29)
Hello I have my array in another tab in the same workbook and when I adapt the formula, it returns me "#VALUE!", any help would be greatly appreciated!
=SUMPRODUCT(--('2016 HCY (IG)'!I6:I500=A21),'2016 HCY (IG)'!P6:P500,'2016 HCY (IG)'!Q6:Q500)
Hey dan
Got a question. I am trying to do a sumproduct of two rows given that a third is not zero. if that third row is zero i want it to skip it and do sum product of the remaining rows. I have this till now. is there any way i can make it work having trouble.
=IF(COUNTIF(C2:N2,0)>=1,0,SUMPRODUCT(--(C22:N22>"0"),$C$7:$N$7,C23:N23))
nice. This is great!
I love you, Man!
Hello Dan, Thanks for the formula!
I'm having an issue with my formula returning a decimal, (only 0.5 off what the number should be). I'm trying to count unique text values within the past year from a table. I track the amount of times our clients are arrested and I'm using this formula to identify the number of clients arrested in past year given that some of them are arrested more than once in a year, and unfortunately there can't be half a person counted. Let me know what you think 🙂
=SUMPRODUCT(--(Table1[Arrest Date]>=A33),(Table1[Full Name]<>"")/COUNTIF(Table1[Full Name],Table1[Full Name]&""))
Additional Info:
A33 is =TODAY()-365
You're a star!!
What a logic!!! Superb!
Since the admin of this web page is working, no doubt very shortly it will be famous, due to its quality contents.
Amazing. Thank you.
Thank you. It was really helpful..
Fantastic, Thank you.
Help, please! This is my function, which counts cells in one dynamic range if they do not appear in another dynamic range.
=SUMPRODUCT(--(ISNA(MATCH(OFFSET('HAW PWP Data'!$B$2,0,0,COUNTA('HAW PWP Data'!$B$2:'HAW PWP Data'!$B$1000),1),OFFSET('HAW Screening Data'!$A$2,0,0,COUNTA('HAW Screening Data'!$A$2:'HAW Screening Data'!$A$1000),1),0))))
It works as it is above, but I need to isolate those in my initial range that have a value of 1 in column E. To add this condition, I tried to use your method, as below:
=SUMPRODUCT(--('HAW PWP Data'!E2:E251=1),(ISNA(MATCH(OFFSET('HAW PWP Data'!$B$2,0,0,COUNTA('HAW PWP Data'!$B$2:'HAW PWP Data'!$B$1000),1),OFFSET('HAW Screening Data'!$A$2,0,0,COUNTA('HAW Screening Data'!$A$2:'HAW Screening Data'!$A$1000),1),0))))
This does not give me an error but returns 0, when the real answer is 113. Can you help me out?
Nevermind, I got it! I just needed to put "--" dougble negatives before EACH array. Here is the solved and properly functioning formula:
=SUMPRODUCT(--(OFFSET('HAW PWP Data'!$E$2,0, 0, COUNTA('HAW PWP Data'!$E$2:'HAW PWP Data'!$E$1000),1)=1),--(ISNA(MATCH(OFFSET('HAW PWP Data'!$B$2,0,0,COUNTA('HAW PWP Data'!$B$2:'HAW PWP Data'!$B$1000),1),OFFSET('HAW Screening Data'!$A$2,0,0,COUNTA('HAW Screening Data'!$A$2:'HAW Screening Data'!$A$1000),1),0))))
Thank you Dan,
This has saved me a lot of complicated calculations.
Why is the result of this formula multiplying not adding? =SUMPRODUCT(--('Full Stock List'!$A$1:$A$4000=$A443),'Full Stock List'!$AL$1:$AL$4000,'Full Stock List'!$AL$1:$AL$4000)
Thank you very much. Quite helpful.
Super solution, thanks Dan
Amazing share, thanks a lot. Works like a charm.
Hi, I am trying to implement this function, but I receive an #Value! Error:
=SUMPRODUCT(--(monthscondition<=27), D80:O80,Q80:AB80,AD80:AO80,AQ80:BB80,BD80:BO80)
I'm trying to calculate a weighted score based on multiple criteria... been combing the comments to get this formula to work... maybe a little help?
=IFERROR(SUMPRODUCT(D211:D213,E211:E213)/SUMPRODUCT(--(C211:C213="<>")*(C211:C213="<>N/A"),E211:E213),"-")
Thanks a lot, Dan! You solved my problems)
This is absolutely fantabulous! I never expected such a function to exist. Thanks a ton!
How does this function read in common language?
=IF(F29<>"",SUM($D$15:$D$18),0)+F43*SUM($G$15:$G$18)
This won't work, I don't think.
Thank you!!
Perfect
Trying to had a certain column looking for a certain text but its giving #value =SUMPRODUCT(--($AZ$2:$EU$2=$EV$5),$AZ$21:$EU$21) this works but now i want to add and certain column looking for a certain text . Can anyone help what and where to input that info, to this existing formula (example column is at D and text BFT2
Excellent formula but it has one flaw.
If your sumproductif is necessary to skip cells that have errors in the formula, the filter works (just place an iferror() but the product resulting from the other two parameters will have an error anyhow since the calculation will be 0 x second par x third par.