Help with reducing calculation time in formulas

[jhigashi]

Hi!

I've been trying to create a spreadsheet that calculates counts based on provided stats. I currently have a working spreadsheet, but it takes over an hour to calculate. Need some recommendations on what I can do to reduce the calculation time. There are a lot of things going on, so please bear with me.

Below is a snapshot of what the spreadsheet would look like.

Each cell has a calculation that references another spreadsheet based on the month. It does a count if the Agency(Agency1) and Error(E1) match. There can also be multiple Agencies I need to lookup for each Error. I also do SUM functions for the Total column.

This is what is in each cell to calculate the count:

Please Login or Register  to view this content.

The INDIRECT cell in Row3 has the path to the other spreadsheet. And I basically just add another SUMPRODUCT formula if there are multiple agencies I need to lookup.

spreadsheet.gif

Thanks!

[TMS]

The first thing to try is changing the full column references to limited ranges, or, ideally, making them dynamic named ranges.

So, $E:$E would become $E$2:$E$1000, for example.


Regards, TMS

[jhigashi]

Ok thanks! Also, is it better to use the SUMPRODUCT or COUNTIFS function? Or is there a better way to get the counts?

[TMS]

You're welcome. Thanks for the rep.

With the advent of COUNTIFS in Excel 2007, it was less necessary to use SUMPRODUCT. But it is still a viable option, especially if you want the formula to be backwards compatible. In theory, it should be better to use COUNTIFS, but, who knows ... I think you're better off doing the comparison once you've eliminated the more obvious reasons for slow performance.

Regards, TMS