+ Reply to Thread
Results 1 to 15 of 15

Alternative to sumproduct

  1. #1
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Alternative to sumproduct

    I suspect that a sumproduct formula is what is heavily weighing down a sheet i have. I have attached a sample that has only 3 rows however the real document has thousands. Does anyone have an idea of a better, more efficient formula I can use? The cells with the sumproduct formula are highlighted blue.

    The goal of the replacement formula:

    For each item, add up the total for each week from the inventory sheet. For example, the total for cheese is 600 in week 1 because it is listed two times with on the inventory sheet with 200 and 400 so (200+400 = 600).

    Hopefully that makes sense...

    Thank you in advance to anyone who responds.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,846

    Re: Alternative to sumproduct

    Hi Questionz,

    Pivot Tables are my alternative to SumProduct. You need to format your data a little differently. If you have the Power Query AddIn it takes 6 mouse clicks to get it from what you have to a better table. Then Pivot takes about 4 more clicks.

    See the attached where I've done what I think you want.
    PT is Sumproduct alternative.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,967

    Re: Alternative to sumproduct

    or in B5, copied across and down:

    =IFERROR(1/(1/SUMIFS(Inventory!B:B,Inventory!$A:$A,Totals!$A5)),"")
    Glenn



  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,000

    Re: Alternative to sumproduct

    @MarvinP / other forummembers

    you get those weeks in your Pivot Table.

    1 10 2 3 4 5 6 7 8 9

    They are not in the right order.

    I always change the format of the datacells to get the right order.

    right click =>

    Format cells =>

    Value (1'st tab) =>

    Differant(12'tab) =>

    Type =>

    Change the inputbox in 00 (zero zero)

    See the result in the attachment.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Re: Alternative to sumproduct

    thank you Marvin for that consideration. My document isn't flexible to where I can rearrange it into a pivot table. I have other rows with data but i took them away so my sample question might be easier to follow.

    I can keep this in mind for future reference. Thank you for your valuable time!

  6. #6
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Re: Alternative to sumproduct

    Quote Originally Posted by Glenn Kennedy View Post
    or in B5, copied across and down:

    =IFERROR(1/(1/SUMIFS(Inventory!B:B,Inventory!$A:$A,Totals!$A5)),"")


    Hi Glenn, thank you for responding! You may have just lifted a huge weight off of my shoulders! I hope to rebuild my larger document and see how it performs. In the meantime, is there a way to have the sumifs to take the week number that's above it and then match it on the Inventory page before adding the values. I attached an updated sample of what I mean. The issue is that the week numbers on both pages have to always be aligned for the sumifs to work. Is there a way for sumifs to still work if the first week on the totals page started with 2 while the weeks started with a 1 on the inventory page?

    This was the sumproduct formula in the first cell B5: =SUMPRODUCT((Inventory!$B$2:$AG$2=B$2)*(Inventory!$A$3:$A$243=$A5)*Inventory!$B$3:$AG$243)

    The Inventory!$B$2:$AG$2=B$2 part grabbed the week number that's above it and then matched in on the inventory page no matter what column the week was in. I tried to do a = B$2 but it's not working like in the sumproduct. Can you or anyone else help me please?

    That was the only benefit to the former sumproduct formula but even if I have to use the current alternative, i'm still happy to abandon the sumproduct.

    Thank you for that fast and relieving and kind help!
    Attached Files Attached Files
    Last edited by Questionz; 05-21-2017 at 04:17 AM.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,000

    Re: Alternative to sumproduct

    With a pivot table.

    You just deselect the week 1 in the pivot table (see the red text columnlabel).

    See the attached file.

    I suggest you learn pivot table, since it is a very powerfull tool, which expands in every new Excel-version.
    This is the way MS tells us, this is the way to go.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,967

    Re: Alternative to sumproduct

    Firstly, I would delete the empty rows in your results table. Calculating a pile of nothing wastes time. If you want more space, simply increase the row height.

    You could then try this formula:

    =IFERROR(SUMIF(Inventory!$A$3:$A$7,Totals!$A5,INDEX(Inventory!$B$3:$K$3,MATCH(Totals!B$2,Inventory!$B$2:$K$2,0))),"")

    It might be quicker than your SUMPRODUCT, but I wouldn't guarantee it. If you REALLY MUST have the empty rows, adapt the formula:

    =IFERROR(1/(1/SUMIF(Inventory!$A$3:$A$7,Totals!$A5,INDEX(Inventory!$B$3:$K$3,MATCH(Totals!B$2,Inventory!$B$2:$K$2,0)))),"")

    BtW, is it just my imagination, or is your sheet polluted by squillions of autoshapes???
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,967

    Re: Alternative to sumproduct

    it is horribly polluted by them....

    F5/special/Objects/OK and then hit backspace. Voila. they're gone.

  10. #10
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Re: Alternative to sumproduct

    Hi Glenn :-)

    Thank you yet again. Yes, I do need the "empty rows", my real document has other formulas and information in them empty cells.png.

    So, I won't be copying the formula across the whole result area. I just drag across the row and then copy the whole row and paste it down the page where it belongs. Since there are thousands of items/rows, I use a autohotkey script to paste the formulas only in the rows where they are needed. So there is not normally any extra cells with formulas not being used. That said, would you change your mind and say that this recent formula with INDEX MATCH might have a chance at still being faster than sumproduct if it'll only be copied in the necessary cells? I have about 3600 rows.
    Last edited by Questionz; 05-21-2017 at 07:23 AM.

  11. #11
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Re: Alternative to sumproduct

    Quote Originally Posted by Glenn Kennedy View Post
    it is horribly polluted by them....

    F5/special/Objects/OK and then hit backspace. Voila. they're gone.
    Thanks a million! I have no clue how those got there and they were annoying me but i thought it was another weird thing excel might be doing because my computer was running slow. I might have accidently generated all those somehow.

    Thanks for caring, I appreciate your kind helps.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,967

    Re: Alternative to sumproduct

    I don't use formula speed calculators... but they are out there if you care to look. Have you looked at the steps the SUMPRODUCT formula goes through by comparison with the SUMIF/Index Match.... (Formulas/evaluate formula/evaluate/evaluate/etc.) The Index-match is only looking at two rows and resolves to a single cell reference. Compare that to the processor mashing to-ing and fro-ing that SUMPRODUCT generates. On that basis, I'd expect the SUMIF to be faster. Others, more into formula speed assessment may leap in to contribute.

    Isn't there a much easier way to get your formulae in there?? I guess it depends on what your formula is. But if it's copyable across and down, it'd be easier to enter "my" formula in B2 and your "mystery" formula in C2. Select BOTH cells and drag them across and down. Job done.

    Regarding your autoshapes, I was struggling to select a particular cell in your sheet, and I always seemed to get an autoshape, not the cell. I recalled that someone else had had an infestation of the wee brutes a year or so ago... and I remembered how to cull them.

    In your other formulae, are you by any chance using any whole column references (they're OK in some functions like INDEX or SUMIF/COUNTIF, etc, but NOT good in MATCH, SUMPRODUCT, VLOOKUP, etc, unless you really do have 1,000,000 plus rows of data. Or are you using a lot of volatile functions (INDIRECT, OFFSET, etc) which recalculate every time something is changed in the sheet?






    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  13. #13
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Re: Alternative to sumproduct

    Hi Glenn,

    You were right. I got to test it and the sumsif is significantly definitely better speedwise. I also made sure to specifically tighten up all my match formulas to not reference whole columns. Thanks for all your help.

    Also, can you please pretty please now just explain to me the benefit of the 1/ portion? I did EVALUATE FORMULA and it seems like it's a mathematical division of 1/result and that takes that answer and divides it into 1 again and reverses the process. Is that a way to make sure excel will read it as a number? Is that same concept as doing the double negative(--) so for example: =IFERROR(--SUMIFS(Inventory!D:D,Inventory!$A:$A,Totals!$A5),"")

    when you mentioned "If you must have blank rows....," what concern would you have if the (1/) or (--) part wasn't included?






    Quote Originally Posted by Glenn Kennedy View Post
    Firstly, I would delete the empty rows in your results table. Calculating a pile of nothing wastes time. If you want more space, simply increase the row height.

    You could then try this formula:

    =IFERROR(SUMIF(Inventory!$A$3:$A$7,Totals!$A5,INDEX(Inventory!$B$3:$K$3,MATCH(Totals!B$2,Inventory!$B$2:$K$2,0))),"")

    It might be quicker than your SUMPRODUCT, but I wouldn't guarantee it. If you REALLY MUST have the empty rows, adapt the formula:

    =IFERROR(1/(1/SUMIF(Inventory!$A$3:$A$7,Totals!$A5,INDEX(Inventory!$B$3:$K$3,MATCH(Totals!B$2,Inventory!$B$2:$K$2,0)))),"")

    BtW, is it just my imagination, or is your sheet polluted by squillions of autoshapes???

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,967

    Re: Alternative to sumproduct

    The 1/(1/formula) thing is just one way to suppress zeros. IMHO a pile of zeros add clutter and no information. This divides 1 by th result of the formula and divides 1 by the answer. 1/(1/2) =2. No change. But 1(1/0)=#DIV0 error, which is mopped up by IFERROR to give a nice, tidy, blank cell.

  15. #15
    Registered User
    Join Date
    09-02-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    50

    Re: Alternative to sumproduct

    that makes sense. Things turned out well and I have been grateful for you help. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Alternative to SUMPRODUCT
    By matt45 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-07-2016, 09:08 AM
  2. [SOLVED] Alternative to SUMPRODUCT
    By jiteshmehta2k1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-23-2015, 11:23 PM
  3. Sumproduct Alternative
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2014, 04:57 PM
  4. Alternative to SUMPRODUCT???
    By VTdude12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2011, 04:03 PM
  5. Alternative to SUMPRODUCT?
    By cjrhoads in forum Excel General
    Replies: 26
    Last Post: 05-14-2010, 03:32 PM
  6. Sumproduct alternative
    By DKerr in forum Excel General
    Replies: 6
    Last Post: 01-09-2009, 11:25 AM
  7. Sumproduct - Alternative
    By Harlequin in forum Excel General
    Replies: 2
    Last Post: 08-09-2007, 12:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1