Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumproduct (Excel 2003)

    Good morning

    I developed a workbook that is working quite well and return all the answers I want but has become very slow.

    If I am correct then it is the Sumproduct formulas that slows the recalculation down. The full size of the workbook is 2.7MB, the input sheet normaly has more than 8,000 lines and there are about 20 sheets in the workbook. I did a rough calculation and found that there are about 150-200 sumproduct formulas on each of the 20 sheets giving a total of nearly 4,000 sumproduct formulas.

    I attach a small extract from the full workbook for review. I coloured the areas where the sumproduct formulas are used in light green for easy reference.

    I was wondering if there is a way in speeding up the workbook by using a different kind of formulas to speed up recalculation. Any adice or ideas will be appreciated.

    Regards

    Kobus
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct (Excel 2003)

    You could use pivot tables - see the attached version.
    Pivot tables don't use formulas, you have to refresh them when the data have changed.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct (Excel 2003)

    Hans

    Thank you very much, it works fine so far with the Pivot Tables in the sample.

    I do however have other sheets in the workbook that feed of these first answers, Will a Refresh update the whole workbook or will I have to update the sheets separately?

    Otherwise, what is the next best solution?

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct (Excel 2003)

    In the workbook that I attached, both pivot tables use the same "pivot cache", so if you refresh one of them, the other will be refreshed too.
    If you have formulas that refer to cells in one of the pivot tables, they will be recalculated automatically.

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct (Excel 2003)

    Hans,

    Thank you, that will be much faster than my current setup.

    Thank you again for the help and the great work you are doing.

    Enjoy your day

    Regards

Posting Permissions

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