"hgrove " schrieb im
Newsbeitrag ...
[...]

Why would you believe this last formula is more efficient? Because it
lacks the string concatenation operations of your INDIRECT formula?
There are 12 of them, and there are then the four INDIRECT calls.
However, if the rows to be used were far smaller than 1000, your
later
formula would do a lot of work multiplying by zeros and adding the
resulting zeros to the sum.

OFFSET would be more efficient than either.

=SUMPRODUCT(
(OFFSET(SummaryAll!$I$1:$I$1000,A2-1,0,A4-A2+1)=$B8)
*(OFFSET(SummaryAll!$J$1:$J$1000,A2-1,0,A4-A2+1)=$C8)
*(OFFSET(SummaryAll!$G$1:$G$1000,A2-1,0,A4-A2+1)=$E$7)
*OFFSET(SummaryAll!$L$1:$L$1000,A2-1,0,A4-A2+1))

I've confirmed this from some light testing of similar but not exact
formulas evaluated in VBA using the macro


Hi Harlan
couldn't resist to test that either. I used FastExcel V.2 to compare
the different formulas (inclduding the Indirect type):
- 2000 formulas each
- range of 2000 cells within the SUMPRODUCT function
- evaluating only row 1-1000 of this 2000 cell range

some results:
1. The Indirect and Offset functions were nearly identical regarding
speed with an small advantage (<5%) for the INDIRECT formula
2. My row comparison formula were approx. 3 times slower than the other
formulas (so a stupid assumption on my side)
3. I also used a variation of the formulas: Using the '--' syntax style
instead of multiplying. The corresponding INDIRECT and OFFSET formulas
were approx. 4% faster than their '*' counterparts



Anyway, the OFFSET formula seems to be roughly 25% faster than
SUMPRODUCT(...(ROW(...)=...).

In my test even slower :-(

Frank