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!!!
:
P.S. - this is pretty much the definitive source for speed issues:
http://www.decisionmodels.com/
Biff
Microsoft Excel MVP
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
"(e-mail address removed)" <
[email protected]>
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.
:
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
"(e-mail address removed)"
<
[email protected]>
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
:
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.
:
"(e-mail address removed)"
<
[email protected]>
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.
