+ Reply to Thread
Results 1 to 2 of 2

Sumproduct formulas & slow response

  1. #1
    Darby
    Guest

    Sumproduct formulas & slow response

    I have a spreadsheet that is about 8,500 KB that is painfully slow to
    calculate. While I can certainly set it manual calculation, my preference is
    to leave the calculation set to automatic. In doing a little testing on the
    file I discovered that the culpret seems to be multiple SUMPRODUCT formulas.

    I am using it in about 30 rows which include monthly amounts for 7 years, so
    there are over 2500 sumproduct formulas, each usind 3 different factors in
    the formula (e.g.
    =SUMPRODUCT(($C$7:$C$4426=2005)*($I$7:$I$4426="30")*($K$7:$K$4426="54")*(CT$7:CT$4426)).

    I've tried using the -- convention in the cells but it doesn't seem to speed
    it up.

    Another bit of information is that I have a similar, yet 3X larger file,
    using complicated SUMIF formulas to do the same type of calculations which
    doesn't have this slow calculation issue. I can switch back to the SUMIF
    method if I have to but SUMPRODUCT seems much cleaner and makes identifying
    formula errors much easier.

    Anybody have any suggestions as to how to speed up the calcualtions?

    Thanks for any help





  2. #2
    Bernie Deitrick
    Guest

    Re: Sumproduct formulas & slow response

    Darby,

    Scrap your SUMPRODUCT formulas and use a pivot table. Much faster, much
    more stable, much more auditable, etc., etc. AND you never need to worry
    about formula errors.

    HTH,
    Bernie
    MS Excel MVP


    "Darby" <Darby@discussions.microsoft.com> wrote in message
    news:6271C80B-0FD4-4C87-B408-C672BFA56BDD@microsoft.com...
    >I have a spreadsheet that is about 8,500 KB that is painfully slow to
    > calculate. While I can certainly set it manual calculation, my preference
    > is
    > to leave the calculation set to automatic. In doing a little testing on
    > the
    > file I discovered that the culpret seems to be multiple SUMPRODUCT
    > formulas.
    >
    > I am using it in about 30 rows which include monthly amounts for 7 years,
    > so
    > there are over 2500 sumproduct formulas, each usind 3 different factors in
    > the formula (e.g.
    > =SUMPRODUCT(($C$7:$C$4426=2005)*($I$7:$I$4426="30")*($K$7:$K$4426="54")*(CT$7:CT$4426)).
    >
    > I've tried using the -- convention in the cells but it doesn't seem to
    > speed
    > it up.
    >
    > Another bit of information is that I have a similar, yet 3X larger file,
    > using complicated SUMIF formulas to do the same type of calculations which
    > doesn't have this slow calculation issue. I can switch back to the SUMIF
    > method if I have to but SUMPRODUCT seems much cleaner and makes
    > identifying
    > formula errors much easier.
    >
    > Anybody have any suggestions as to how to speed up the calcualtions?
    >
    > Thanks for any help
    >
    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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