Dear Valko,
This is a weakness of sumproduct (i think!) in terms of data range, coz when
I use sumif, it allows (K:K) full range with minor twitches to the Dbase
(concatenate the criterias).
I will change it to sumif. I was trying out the sumproduct formula, I guess
the ranging of data is rather restrictive.
If I use sumif, I will need to concatenate(K & H) in the Dbase.
Thanks for the help!!!
"T. Valko" wrote:
P.S. - this is pretty much the definitive source for speed issues:
http://www.decisionmodels.com/
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
One thing you could do is put the VLOOKUP in another cell and then refer
to that cell:
A1:
=VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE)
Then:
=SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450)
Another way to create dynamic ranges (assumes no empty cells within the
range):
=A1:INDEX(A:A,COUNTA(A:A))
There is virtually no meaningful difference in calc times using this
method vs. using the OFFSET method (offset is actually a pretty fast
function) but the INDEX method is significantly faster on recalcs.
Biff
Microsoft Excel MVP
" wrote
in message ...
Dear Valko,
Yes, its something like that, however the Dynamic Range slows things down
(real slow), that is why I limit one workbook to one or two Dynamic range
only.
I have tried putting 2 more dynamic range for this $F & $K column, it's a
real turtle race damn slow due to calculation.
Thought that since CountA would result in current range size, maybe by
manipulating the array, could result in a similar result without the slow
effect.
Since CountA results in 6001, i have tried these stuff but it doesn't
work,
the array is not recognised:
1. Concatenate ("K",CountA(Dbase!A:A) result K6001
2. &"K"&Dbase!A:A also result K6001
if only can be that simple..hahaha!! ---
SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP
And the sumproduct does not allow K:K full range.
Is there another way to go around this!! Thanks.
"T. Valko" wrote:
In other words, you want to use a dynamic range. You would have to do
the
same thing for all of the ranges invloved: Dbase!$K$1:$K$5450,
Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450
See this:
http://contextures.com/xlNames01.html#Dynamic
Biff
Microsoft Excel MVP
"
wrote in
message ...
Correction;
From this: Original formula
SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP
To this: New Formula
SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP
[something like this]
How I wish it could be done this way:
The Array (Dbase!K1:K[CountA(Dbase!A:A)
So automatically becomes, K1:K6001
" wrote:
Dear Harlan,
It's not as straight forward as it seems! My posting is a little
confusing.
(my apology)
Lets try this:
From this: Original formula
SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP
To this: New Formula
SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP
[something like this]
I am hoping that the 5450 will be recognised with 6001, which is the
new
range (automatically).
Thanks.
"Harlan Grove" wrote:
"
wrote...
....
What I wanna do is to substitute the 5450 with 6001 (the formula
below).
Coz
the range has increased from 5450 to 6001. If I can slot this in,
then
I
won't have to set the range everytime it grows.
* CountA(Dbase!A:A) equals to 6001
Substiture K5450 and F5450
=SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1,
DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450)
Edit Replace, replacing $5450 with $6001.