SUMPRODUCTIF

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.

This entry was posted in Quick tips and tagged , , . Bookmark the permalink.

92 Responses to SUMPRODUCTIF

  1. Raf Van Aggelpoel says:

    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

  2. KiW says:

    Sweet! Works like a dream.

    Thnx

  3. Oleg says:

    Fantastic!

  4. Riaan says:

    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

  5. Mag says:

    Hello, there are mornings where everything works perfectly... thanks for your formula!

  6. MIHIR says:

    Fantastic, you have saved many hours of my life ! 🙂
    Thanks !
    added your page in my favorites..

  7. Blaine says:

    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.

  8. Dan says:

    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.

    • Anand says:

      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

      • Sai says:

        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)

  9. Josefina says:

    Hi Dan,
    Thanks for sharing the formula, but what what can I do if instead of one condition I have two or more?
    Thanks

  10. Dan says:

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

  11. Josefina says:

    Thanks Dan. I will have a go!

  12. Ben Briggs says:

    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.

  13. Peter says:

    Brilliant! Super clever, and thanks for publishing this tip!

  14. Alfredo says:

    Why the "-" minus before the condition, my total is negative now.
    I know how tom solve that but just curious.

  15. Jim says:

    This is one of the niftiest Excel tricks I have ever seen! Thank you for posting such a brilliant, elegant solution!

  16. Scott says:

    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?

    • Scott says:

      Nevermind, I got it...thanks!

      • Srihari says:

        Scott, can you share the solution for doing a SUMPRODUCT satisfying multiple criteria without the need for a dummy column.

        • Alexander says:

          Slowpoke, but someone, like me, might come across this and looking for a solution:

          =SUMPRODUCT(--(condition="London")*(condition2="Soho"),range1,range2)

          • Dan says:

            =SUMPRODUCT(--(city="London")*(area="Soho"),range1,range2)

          • Stan Dad says:

            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.

  17. anne says:

    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

    • Dan says:

      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.

  18. anne says:

    additional data, I have daily attrition % and HC

  19. Frank says:

    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%

    • Dan says:

      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)

  20. Joms says:

    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!

  21. Wicked formula. Much appreciated.

  22. Julia says:

    Thank you so much for posting this!!! Explanation for the double negative was very helpful.

  23. Jim Pelot says:

    I am so happy that experts like you solve tricky problems like this one - what a great solution.

    Thanks Dan

  24. Naz says:

    Thanks a hundred billion !!! I wish I had seen this earlier 🙂

  25. brent says:

    Holy moly.

    I honestly thought I'd be googling for hours to figure this out. You're a master!

  26. Mateo says:

    This is exactly what I needed! Thanks for the post!

  27. Jess says:

    You are totally awesome, I love this formula. Thank you for sharing your awesomeness!!!!!

  28. Simon says:

    This is great! Saved me so much time. Thanks!

  29. Willian Stein says:

    Thanks for the tip, it helped me a lot!

  30. John says:

    Brilliant, thanks!

  31. Zafar says:

    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

  32. Wes says:

    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.

    • Dan says:

      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.

  33. John says:

    I know this is an older post but this is an outstanding trick. Well described, easy to implement, and quite effective.

  34. thankyouverymuch says:

    You are a beautiful person!

  35. kamal says:

    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.

  36. Jeff says:

    Just piling on about 4 years after this originally posted to say thank you! I am now unstuck!

  37. Marinelly Alvarez says:

    Thanks. It was a very helpful recommendation

  38. Mostafa Hasan says:

    in a certain column, if it contains zero, i want to force the sum product to be zero. Is this possible?

    • Dan says:

      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.

      • Mostafa Hasan says:

        Thank you so much Dan. It was really a great help.

      • Mos says:

        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?

        • Dan says:

          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.

          • Mos says:

            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)

          • Mos says:

            Dan

            I solved it....

            Thank you so much for all the help. Ignore my previous text.

            Mos 🙂 🙂

  39. Ross says:

    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

  40. Masoud Samiee says:

    Thanks a lot

  41. Matt says:

    Great solution, thanks

  42. Marin Hardy says:

    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)

  43. Melll says:

    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)

  44. ali says:

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

  45. steve says:

    nice. This is great!

  46. DLM says:

    I love you, Man!

  47. Claire says:

    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

  48. Freda says:

    You're a star!!

  49. Gerardo Perez Vicenti says:

    What a logic!!! Superb!

  50. Since the admin of this web page is working, no doubt very shortly it will be famous, due to its quality contents.

  51. Stan Cullerne-Bown says:

    Amazing. Thank you.

  52. Vino says:

    Thank you. It was really helpful..

  53. Saurabh Jain says:

    Fantastic, Thank you.

  54. Aubry says:

    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?

    • Aubry says:

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

  55. Piru says:

    Thank you Dan,
    This has saved me a lot of complicated calculations.

  56. Marcus says:

    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)

  57. Apostolos Adamakos says:

    Thank you very much. Quite helpful.

  58. Stephen says:

    Super solution, thanks Dan

  59. Bert Boerman says:

    Amazing share, thanks a lot. Works like a charm.

  60. Michael says:

    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)

  61. Matt says:

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

  62. Alex says:

    Thanks a lot, Dan! You solved my problems)

  63. Priya says:

    This is absolutely fantabulous! I never expected such a function to exist. Thanks a ton!

  64. John says:

    How does this function read in common language?

    =IF(F29<>"",SUM($D$15:$D$18),0)+F43*SUM($G$15:$G$18)

  65. Ashleigh says:

    Thank you!!

  66. razz2k6 says:

    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

  67. oscar luppi says:

    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.

Comments are closed.