Slow Calculating and optimizing

[kurt]

Hi

I have been using the expression =SUMPRODUCT(--(file'!$P$1:$P
$65500="txt");'file'!$H$1:$H$65500)
to summarize all cells in column H where column P= "txt"

I have 1200 of theese expressions and it make the startup and
recalculating very slow (3-4 minutes)

The 65500 is choosed to be sure that I never ran out of Data and it
was not based on an analyze.

To optimize this cut the 65500 down to a relevant size ( ex. 10000)
and then the calculation time is reduced dramatically.

regards

Kurt

 

[Roger Govier]

Hi Kurt

Try creating dynamic ranges for your data
For example
Insert>name>define> Name colP Refers to =OFFSET($P$1,0,0,COUNTA($P:$P))

Repeat for colH

Then use
=SUMPRODUCT(--(colP=".txt");--(ColH))

To ensure that all of your ranges have the same length, use COUNTA($P:$P) in
all cases, or whichever column will not have any blank data within it

I usually prefer to have one named variable lrow where I would set it as
=COUNTA($P:$P)
Then all the other named ranges would be, for example
=OFFSET($P$1,0,0,lrow)

 

[Guest]

While Roger gives good advice for dynamic ranges, in this case, you don't
need to us sumproduct at all. Since you only have one condition, the much,
much faster

=Sumif('file'!P,"txt",'File'!H:H)

would work fast and doesn't restrict your range.

If 'file" will be closed, then Sumif does not work against a closed workbook
and you would have to go with Sumproduct and perhaps use Roger's suggestion.

 

[Guest]

There is a trade off to be had if you use the dynamic named range in that
offset is a volatile function. That means that all 1,200 functions become
volatile and while they will calculate faster they will also calculate more
often. This strategy could work for you or against you depending on the time
savings. If it does not work out then you might want to consider using some
code to redefine the range names when then overall number of rows of source
data increases or decreases.

 

[Roger Govier]

Hi

Whilst I agree entirely with Tom's advice to the OP about SUMIF, and that
this would speed up the calculations far more, and with your point about
volatility using OFFSET, I thought after posting, I should have offered the
OP INDEX instead as a non-volatile solution.
I went out immediately after posting and have only just returned.

Again define lrow as per my previous posting =COUNTA($P:$P)
Then use the following for the column name colP
=$P$1:INDEX($P:$P,lrow)