+ Reply to Thread
Results 1 to 7 of 7

More efficient SUMPRODUCT formula?

  1. #1
    Registered User
    Join Date
    02-11-2019
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    8

    More efficient SUMPRODUCT formula?

    Hello,

    I have the following formula which works in my current excel spreadsheet, but it is calculating very slowly. The named ranges are single columns (it references the entire column but only has ~10k rows with data for now). The formula is repeated hundreds of time on the slow tab by varying the row referenced in '$A9' and the first part of the sumproduct 'FGA_Adj'.

    Please Login or Register  to view this content.
    Is there another way to write this using a sumif(s) or some other efficient way? I have read that a sumif would be a much more computationally efficient solution.


    Thanks,
    Mark

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,776

    Re: More efficient SUMPRODUCT formula?

    Make the Named Ranges into Dynamic Named Ranges based on whichever of the columns is fully populated. Full column references used to error in older versions of Excel. They are allowed now but they WILL be SLOW.


    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-11-2019
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    8

    Re: More efficient SUMPRODUCT formula?

    Thank you! I am familiar with dynamic named ranges so I will try this first and report back.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,776

    Re: More efficient SUMPRODUCT formula?

    OK. Look forward to hearing from you.

  5. #5
    Registered User
    Join Date
    02-11-2019
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    8

    Re: More efficient SUMPRODUCT formula?

    That was a huge help - it went from taking ~2 minutes to compute to 2-3 seconds which I can definitely deal with and scale from. Thank you!!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,776

    Re: More efficient SUMPRODUCT formula?

    You're welcome.


    2-3 seconds
    do you have other complex formulae? Or a lot of them? Sounds like there could be more efficiencies to make.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,776

    Re: More efficient SUMPRODUCT formula?

    Thanks for the rep.

+ 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. Replies: 2
    Last Post: 07-06-2018, 10:07 PM
  2. Most efficient sumproduct with criteria?
    By TechRetard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-16-2013, 09:14 AM
  3. [SOLVED] Sumproduct - formula more efficient/faster?
    By Gti182 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-17-2013, 07:45 AM
  4. Is there a more efficient formula?
    By Uziel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2011, 05:56 AM
  5. formula help, anything more efficient than 3 IFs?
    By TechRetard in forum Excel General
    Replies: 0
    Last Post: 02-03-2011, 01:17 PM
  6. Replies: 13
    Last Post: 01-26-2010, 05:29 PM
  7. IF, SUMPRODUCT, VLookup-most efficient?
    By excellicious in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2008, 09:59 PM

Tags for this Thread

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