Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Changing Array function to D function in Excel 2003

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.

Inventory7.xls
0
Spicehead
Asked:
Spicehead
1 Solution
 
Rory ArchibaldCommented:
Hi,
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.
Regards,
Rory

Inventory7.xls
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now