File running slow!
 

I have a excell file which has alot of "SUMPRODUCT" formulas. I know using
this formula slows down the calculation by quit abit.....so I have been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

File running slow!
 

Tell us what you use the SUMPRODUCT formulas for, so we can think about
possible other solutions....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"hoyos" wrote in message
...
I have a excell file which has alot of "SUMPRODUCT" formulas. I know using
this formula slows down the calculation by quit abit.....so I have been
told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

File running slow!
 

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


"hoyos" wrote:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know using
this formula slows down the calculation by quit abit.....so I have been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

File running slow!
 

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation and
not after entering/changing cell values.
Micky


"מיכאל (מיקי) אבידן" wrote:

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


"hoyos" wrote:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know using
this formula slows down the calculation by quit abit.....so I have been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

File running slow!
 

Thanks for replying,
Here are two samples of formula I am using:

=IF(AF16="",NA(),SUMPRODUCT(--(YEAR(Orders!$B$5:$B$65001)=YEAR(AF16)),--(MONTH(Orders!$B$5:$B$65001)=MONTH(AF16)),--(DAY(Orders!$B$5:$B$65001)=DAY(AF16)),Orders!$J$5: $J$65001))
and
=SUMPRODUCT((Orders!$B$3:$B$64988=DATE($B$2,1,1)) *(Orders!$B$3:$B$64988<=DATE($B$2,1,31))*(Orders!$ C$3:$C$64988=$B5))

At the moment I have 14 worksheets of data. Would it help to have as much on
one sheet as possible or does that not make a difference?





"מיכאל (מיקי) אבידן" wrote:

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation and
not after entering/changing cell values.
Micky


"מיכאל (מיקי) אבידן" wrote:

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


"hoyos" wrote:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know using
this formula slows down the calculation by quit abit.....so I have been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

File running slow!
 

Hi

Firstly the first formula can be simplified to
=IF(AF16="",NA(),SUMPRODUCT(--(Orders!$B$5:$B$65001=AF16),Orders!$J$5:$J$65001)

Secondly, you are giving Sumproduct almost a whole column to be calculating
each time, when most of the cells will be empty.

Create some Dynamic ranges to use within your formulae.
For example if column B is date and column J is value
InsertNameDefine
Name Date
Refers to $B$5:INDEX($B:$B,COUNTA($B$5:$B$65536))

InsertNameDefine
Name Value
Refers to $J$5:INDEX($J:$J,COUNTA($B$5:$B$65536))

(Note, the Counta is based on column B in both cases, to ensure that the
ranges are of the same length)

Then, make your formula
=IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value)

The calculations will only be performed upon the used range of data.

For more information on creating Dynamic ranges, I have a page on Debra
Dalgleish's site and a downloadable file showing how to create your Dynamic
ranges with a macro at
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier

"hoyos" wrote in message
...
Thanks for replying,
Here are two samples of formula I am using:

=IF(AF16="",NA(),SUMPRODUCT(--(YEAR(Orders!$B$5:$B$65001)=YEAR(AF16)),--(MONTH(Orders!$B$5:$B$65001)=MONTH(AF16)),--(DAY(Orders!$B$5:$B$65001)=DAY(AF16)),Orders!$J$5: $J$65001))
and
=SUMPRODUCT((Orders!$B$3:$B$64988=DATE($B$2,1,1)) *(Orders!$B$3:$B$64988<=DATE($B$2,1,31))*(Orders!$ C$3:$C$64988=$B5))

At the moment I have 14 worksheets of data. Would it help to have as much
on
one sheet as possible or does that not make a difference?





"מיכאל (מיקי) אבידן" wrote:

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation
and
not after entering/changing cell values.
Micky


"מיכאל (מיקי) אבידן" wrote:

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


"hoyos" wrote:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know
using
this formula slows down the calculation by quit abit.....so I have
been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

__________ Information from ESET Smart Security, version of virus
signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com

File running slow!
 

Thank you Roger I shall try that.
I will come back with a feedback.

"Roger Govier" wrote:

Hi

Firstly the first formula can be simplified to
=IF(AF16="",NA(),SUMPRODUCT(--(Orders!$B$5:$B$65001=AF16),Orders!$J$5:$J$65001)

Secondly, you are giving Sumproduct almost a whole column to be calculating
each time, when most of the cells will be empty.

Create some Dynamic ranges to use within your formulae.
For example if column B is date and column J is value
InsertNameDefine
Name Date
Refers to $B$5:INDEX($B:$B,COUNTA($B$5:$B$65536))

InsertNameDefine
Name Value
Refers to $J$5:INDEX($J:$J,COUNTA($B$5:$B$65536))

(Note, the Counta is based on column B in both cases, to ensure that the
ranges are of the same length)

Then, make your formula
=IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value)

The calculations will only be performed upon the used range of data.

For more information on creating Dynamic ranges, I have a page on Debra
Dalgleish's site and a downloadable file showing how to create your Dynamic
ranges with a macro at
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier

"hoyos" wrote in message
...
Thanks for replying,
Here are two samples of formula I am using:

=IF(AF16="",NA(),SUMPRODUCT(--(YEAR(Orders!$B$5:$B$65001)=YEAR(AF16)),--(MONTH(Orders!$B$5:$B$65001)=MONTH(AF16)),--(DAY(Orders!$B$5:$B$65001)=DAY(AF16)),Orders!$J$5: $J$65001))
and
=SUMPRODUCT((Orders!$B$3:$B$64988=DATE($B$2,1,1)) *(Orders!$B$3:$B$64988<=DATE($B$2,1,31))*(Orders!$ C$3:$C$64988=$B5))

At the moment I have 14 worksheets of data. Would it help to have as much
on
one sheet as possible or does that not make a difference?





"מיכאל (מיקי) אבידן" wrote:

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation
and
not after entering/changing cell values.
Micky


"מיכאל (מיקי) אבידן" wrote:

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


"hoyos" wrote:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know
using
this formula slows down the calculation by quit abit.....so I have
been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

__________ Information from ESET Smart Security, version of virus
signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com