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:

=IF(ISERR(SUMPRODUCT((INDIRECT(D$3&"$E:$E")=$A$5)*(INDIRECT(D$3&"$H:$H")=$C5))),"N/A",SUMPRODUCT((INDIRECT(D$3&"$E:$E")=$A$5)*(INDIRECT(D$3&"$H:$H")=$C5)))
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!