I am using a range in a formula, but in order to shorten
the calculatioon time I would like for the range to be
selected "dynamically" according to 2 criteria.

a) Start the range at the first value that is equal to a
given date
b) End the range at the last value in the column

The formula will be on one sheet and the range is on
another within the same workbook.

The first value used in the range should give me the row
number I will be using for transposing that range in other
columns.


I tried to use Indirect/match wich gave me the row number,
but I cannot integrate it into my formula which uses the
sumproduct function.

The formula looks like this:
SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data!$K$sx:$K$e x=Csx).

I want to replace the "sx" by the starting row in the range
and "ex" by the ending row in the range.

The Worksheet/Range that hold the criteria is Data!
H10:H10000.

Thanks for any help.

Hi Victor
to be honest I doubt you will gain any performance improvements. At
least the start/end calculation has to search through the whole range
and if you don't have that much SUMPRODUCT formulas this could even
slow you down (INDIRECT is not that fast). But if you want to try try
something like the following:
1. Get the row numbers (separate formulas in different cells).
Condition is in cell X1
1.a. Starting row, in cell X2
=MATCH(X1,Data!H10:H10000,0)+9
1.b. ending row in cell X3: Enter the following array formula (entered
with CTRL+SHIFT+ENTER
=MAX(IF(Data!H10:H10000=X1,ROW(Data!H10:H10000))

2. Now use the following SUMPRODUCT formula (not sure why you have used
Bsx in your example as comparison value:
=SUMPRODUCT((INDIRECT("Data!$J$" & X2 & ":$J$" &
X3)=B1)*(INDIRECT("Data!$K$" & X2 & ":$K$" & X3)=C1))

But as said if you have only some SUMPRODUCT formulas this would not
gain much performance. I would use the following instead:
=SUMPRODUCT((Data!$J$10:$J$10000=B1)*(Data!$K$10:$ K$10000=C1)*(Data!$H$
10:$H$10000=your_condition))
or
=SUMPRODUCT(--(Data!$J$10:$J$10000=B1),--(Data!$K$10:$K$10000=C1),--(Da
ta!$H$10:$H$10000=your_condition))


--
Regards
Frank Kabel
Frankfurt, Germany


Victor wrote:
I am using a range in a formula, but in order to shorten
the calculatioon time I would like for the range to be
selected "dynamically" according to 2 criteria.

a) Start the range at the first value that is equal to a
given date
b) End the range at the last value in the column

The formula will be on one sheet and the range is on
another within the same workbook.

The first value used in the range should give me the row
number I will be using for transposing that range in other
columns.


I tried to use Indirect/match wich gave me the row number,
but I cannot integrate it into my formula which uses the
sumproduct function.

The formula looks like this:
SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data!$K$sx:$K$e x=Csx).

I want to replace the "sx" by the starting row in the range
and "ex" by the ending row in the range.

The Worksheet/Range that hold the criteria is Data!
H10:H10000.

Thanks for any help.

You may be right I'll try your last advice.
Add a date condition in a sumproduct statement.

But everytime I change anything in that sheet, I have to
wait between 1 to 2 minutes for recalc, so what I did is I
put the sheet on manual calc.
-----Original Message-----
Hi Victor
to be honest I doubt you will gain any performance
improvements. At
least the start/end calculation has to search through the
whole range
and if you don't have that much SUMPRODUCT formulas this
could even
slow you down (INDIRECT is not that fast). But if you
want to try try
something like the following:
1. Get the row numbers (separate formulas in different
cells).
Condition is in cell X1
1.a. Starting row, in cell X2
=MATCH(X1,Data!H10:H10000,0)+9
1.b. ending row in cell X3: Enter the following array
formula (entered
with CTRL+SHIFT+ENTER
=MAX(IF(Data!H10:H10000=X1,ROW(Data!H10:H10000) )

2. Now use the following SUMPRODUCT formula (not sure why
you have used
Bsx in your example as comparison value:
=SUMPRODUCT((INDIRECT("Data!$J$" & X2 & ":$J$" &
X3)=B1)*(INDIRECT("Data!$K$" & X2 & ":$K$" & X3)=C1))

But as said if you have only some SUMPRODUCT formulas
this would not
gain much performance. I would use the following instead:
=SUMPRODUCT((Data!$J$10:$J$10000=B1)*(Data!
$K$10:$K$10000=C1)*(Data!$H$
10:$H$10000=your_condition))
or
=SUMPRODUCT(--(Data!$J$10:$J$10000=B1),--(Data!
$K$10:$K$10000=C1),--(Da
ta!$H$10:$H$10000=your_condition))


--
Regards
Frank Kabel
Frankfurt, Germany


Victor wrote:
I am using a range in a formula, but in order to shorten
the calculatioon time I would like for the range to be
selected "dynamically" according to 2 criteria.

a) Start the range at the first value that is equal to a
given date
b) End the range at the last value in the column

The formula will be on one sheet and the range is on
another within the same workbook.

The first value used in the range should give me the row
number I will be using for transposing that range in
other
columns.


I tried to use Indirect/match wich gave me the row
number,
but I cannot integrate it into my formula which uses the
sumproduct function.

The formula looks like this:
SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data!
$K$sx:$K$ex=Csx).

I want to replace the "sx" by the starting row in the
range
and "ex" by the ending row in the range.

The Worksheet/Range that hold the criteria is Data!
H10:H10000.

Thanks for any help.

.

Hi
do you use a lot of these SUMPRODUCT statements. If yes you may
consider if a pivot table might be of use. Also see the following site
for some otimisation tips:
http://www.decisionmodels.com/optspeed.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Victor wrote:
You may be right I'll try your last advice.
Add a date condition in a sumproduct statement.

But everytime I change anything in that sheet, I have to
wait between 1 to 2 minutes for recalc, so what I did is I
put the sheet on manual calc.
-----Original Message-----
Hi Victor
to be honest I doubt you will gain any performance improvements. At
least the start/end calculation has to search through the whole
range
and if you don't have that much SUMPRODUCT formulas this could even
slow you down (INDIRECT is not that fast). But if you want to try
try
something like the following:
1. Get the row numbers (separate formulas in different cells).
Condition is in cell X1
1.a. Starting row, in cell X2
=MATCH(X1,Data!H10:H10000,0)+9
1.b. ending row in cell X3: Enter the following array formula
(entered with CTRL+SHIFT+ENTER
=MAX(IF(Data!H10:H10000=X1,ROW(Data!H10:H10000))

2. Now use the following SUMPRODUCT formula (not sure why you have
used Bsx in your example as comparison value:
=SUMPRODUCT((INDIRECT("Data!$J$" & X2 & ":$J$" &
X3)=B1)*(INDIRECT("Data!$K$" & X2 & ":$K$" & X3)=C1))

But as said if you have only some SUMPRODUCT formulas this would not
gain much performance. I would use the following instead:
=SUMPRODUCT((Data!$J$10:$J$10000=B1)*(Data!
$K$10:$K$10000=C1)*(Data!$H$ 10:$H$10000=your_condition))
or
=SUMPRODUCT(--(Data!$J$10:$J$10000=B1),--(Data!
$K$10:$K$10000=C1),--(Da ta!$H$10:$H$10000=your_condition))


--
Regards
Frank Kabel
Frankfurt, Germany


Victor wrote:
I am using a range in a formula, but in order to shorten
the calculatioon time I would like for the range to be
selected "dynamically" according to 2 criteria.

a) Start the range at the first value that is equal to a
given date
b) End the range at the last value in the column

The formula will be on one sheet and the range is on
another within the same workbook.

The first value used in the range should give me the row
number I will be using for transposing that range in other
columns.


I tried to use Indirect/match wich gave me the row number,
but I cannot integrate it into my formula which uses the
sumproduct function.

The formula looks like this:
SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data! $K$sx:$K$ex=Csx).

I want to replace the "sx" by the starting row in the range
and "ex" by the ending row in the range.

The Worksheet/Range that hold the criteria is Data!
H10:H10000.

Thanks for any help.

.