formula slowing up processing

[gregork]

I have the following formula in a list that takes up to 15 seconds to
calculate. When I am inputting new entries into the list everything freezes.
I know its this formula because when I remove it everything runs smoothly.
The formula is used to lookup a number on another list and then sum all the
values returned for that number. Can anyone see a problem with the
formula...it does work...just excruciatingly slowly:

SUMIF('Blending Details'!$A:$A$,A2,'Blending Details'!$E:$E)

 

[RagDyeR]

Have you tried reducing the size of the range?

Do you really need 65,536 rows?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

I have the following formula in a list that takes up to 15 seconds to
calculate. When I am inputting new entries into the list everything freezes.
I know its this formula because when I remove it everything runs smoothly.
The formula is used to lookup a number on another list and then sum all the
values returned for that number. Can anyone see a problem with the
formula...it does work...just excruciatingly slowly:

SUMIF('Blending Details'!$A:$A$,A2,'Blending Details'!$E:$E)

 

[gregork]

Thanks for the reply RD. Yes I have tried reducing range but it is still
slow as.

Regards
gregorK

 

[gregork]

I have had another look at it and I think it is actually a problem with the
code I have for the user form I am using to input the data. I'll make a new
post in excel.programming.

Cheers
gregorK

 

[David McRitchie]

I don't think there is anything wrong with the range, unless you
really do have all those rows actually populated with data.
This is builtin to Excel and Excel will only check the used range.

Possibly could be problem if you are doing this for every single row.
I would think you might really only want to do the SUM if a COUNTIF
shows that you have the first occurrence of the value you are looking for.

But do check your lastcell usage Ctrl+End if that is out of whack
take a look at
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

 

[David McRitchie]

Hi Greg,
If it is programming code involving every cell in a range of an entire column
then you could definitely have a problem. Program would have to limit the
scope to the used range in some manner. Obviously you are not doing the
same thing in the program as what you posted for your Worksheet code.
Take a look at
http://www.mvps.org/dmcritchie/excel/proper.htm
paying particular notice of SpecialCells