Changing Array function to D function in Excel 2003

Posted on 2009-04-17
Last Modified: 2012-05-06
I'm trying to change array function to D function in Excel 2003 for our inventory.
As you can see in the attached file, sums of 4 arrays create inventory.

Array seems to be running very slow when there are a lot of items to be calculated.
And I got more than 1000 lines to be calculated.

It takes more than 1 minute to do the calculation everytime I add a line.

As far as I know =dsum runs much faster than =sum of arrays.
Not sure it's possible in this case though.
Any help would be appreciated.

Question by:Spicehead
    1 Comment
    LVL 85

    Accepted Solution

    I don't think a D function would work for you because you would need a separate criteria range for each function. You could use a pivot table to summarise your data, or you could add an index column to the data and then use a SUMIF formula. I've shown both in the attached workbook. Note that the pivot table can be smartened up a lot - that was just to show the idea! (you can also use the pivot table as a data source and use GETPIVOTDATA functions to extract the data you need directly from that.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now