Dear Valko,

Last Question:

=TYPE(CONCATENATE("Dbase!$K$1:","$K$",COUNTA(Dbase !A:A))) equals to 2 or text

I think if I can convert it to array, 64, then the formula should work. Is
there a way to convert it?


"T. Valko" wrote:

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

It's not just SUMPRODUCT, it's any array formula. Although you don't have to
array enter SUMPRODUCT it still calculates like an array entered formula.

Array formulas calculate *every* cell that's referenced. A function like
SUMIF only calculates the used range. So, if you have data in A1:A100 and
have this: =SUMIF(A:A,"0"), it only calculates A1:A100. A101 to the bottom
of the column are ignored.

Biff
Microsoft Excel MVP

" wrote in
message ...
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.