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.