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
Insert>Name>Define>
Name Date
Refers to $B$5:INDEX($B:$B,COUNTA($B$5:$B$65536))
Insert>Name>Define>
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 said:
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?
מיכ×ל (מיקי) ×בידן said:
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
:
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
:
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
