Hi Kurt

Try creating dynamic ranges for your data
For example
Insertnamedefine 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)

--
Regards
Roger Govier



"kurt" wrote in message
ps.com...
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