Does SUMPRODUCT work with "INDIRECT"?

[David]

I have 2 cells: A2,A4 which calculate the beginning and
the end of a (dynamic) range I use in the following
formula:

SUMPRODUCT((SummaryAll!$I$5:$I$202=$B8)*(SummaryAll!
$J$5:$J$202=$C8)*(SummaryAll!$G$5:$G$202=$E$7)*(SummaryAll!
$L$5:$L$202))

5 being the beginning of the range calculated in A2
202 being the end of the range calculated in A4

Now, instead of manually replacing 5 and 202 by the new
values in A2 and A4. I would like my formula to update
itself with the help of INDIRECT.

If this works, I would like some help with the syntax, I
have tried almost everything but nothing works so far.
The formula itself is OK for it gives me accurate results
when I update it manually.

Or, if there is a "leaner" shorter formula that could work
I would gratefully appreciate any pointers.

Thanks in advance.

 

[Frank Kabel]

Hi
try:
SUMPRODUCT((INDIRECT("SummaryAll!$I$" & A2 & ":$I$" &
A4)=$B8)*(INDIRECT("SummaryAll!$J$" & A2 & ":$J$" &
A4)=$C8)*(INDIRECT("SummaryAll!$G$" & A2 & ":$G$" &
A4)=$E$7)*(INDIRECT("SummaryAll!$L$" & A2 & ":$L$" & A4)))


But you could also use OFFSET (could be a little bit faster). I would
prefer the following formula (if you don't have error in your data
range) as I suspect this is more efficient than using INDIRECT (not
tested though):
SUMPRODUCT(--(SummaryAll!$I$1:$I$1000=$B8),--(SummaryAll!
$J$1:$J$1000=$C8),--(SummaryAll!$G$1:$G$1000=$E$7),--(ROW(SummaryAll!$I
$1:$I$1000)>=A2),--(ROW(SummaryAll!$I$1:$I$1000)<=A4),SummaryAll!$L$1:$
L$1000)

 

[David]

The second one looks good, I'll try it and kepp you posted.

Thanks...

 

[hgrove]

Frank Kabel wrote...

try:
SUMPRODUCT(
(INDIRECT("SummaryAll!$I$"&A2&":$I$"&A4)=$B8)
*(INDIRECT("SummaryAll!$J$"&A2&":$J$"&A4)=$C8)
*(INDIRECT("SummaryAll!$G$"&A2&":$G$"&A4)=$E$7)
*(INDIRECT("SummaryAll!$L$"&A2&":$L$"&A4)))

But you could also use OFFSET (could be a little bit faster). I
would prefer the following formula (if you don't have error in
your data range) as I suspect this is more efficient than using
INDIRECT (not tested though):

SUMPRODUCT(--(SummaryAll!$I$1:$I$1000=$B8),
--(SummaryAll!$J$1:$J$1000=$C8),
--(SummaryAll!$G$1:$G$1000=$E$7),
--(ROW(SummaryAll!$I$1:$I$1000)>=A2),
--(ROW(SummaryAll!$I$1:$I$1000)<=A4),
SummaryAll!$L$1:$L$1000)

...

Why would you believe this last formula is more efficient? Because i
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 late
formula would do a lot of work multiplying by zeros and adding th
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 exac
formulas evaluated in VBA using the macro


Code
-------------------

Sub testem()
Const MAXITER As Long = 100000

Dim inct As Double, cumt As Double, n As Long, x As Double

cumt = 0
For n = 1 To MAXITER
'ActiveSheet.Calculate
inct = Timer
x = ActiveSheet.Evaluate( _
"=SUMPRODUCT(--(A6:A15=A1),B6:B15)" _
)
cumt = cumt + Timer - inct
Next n
Debug.Print "baseline: " & Format(cumt, "0.00")

GoTo NextBlock
cumt = 0
For n = 1 To MAXITER
'ActiveSheet.Calculate
inct = Timer
x = ActiveSheet.Evaluate( _
"=SUMPRODUCT(--(INDIRECT(""A"" & 6 & "":A"" & 15)=A1),INDIRECT(""B"" & 6 & "":B"" & 15))" _
)
cumt = cumt + Timer - inct
Next n
Debug.Print "INDIRECT: " & Format(cumt, "0.00")

NextBlock:
cumt = 0
For n = 1 To MAXITER
'ActiveSheet.Calculate
inct = Timer
x = ActiveSheet.Evaluate( _
"=SUMPRODUCT(--(A3:A22=A1),--(ROW(A3:A22)-2>=4),--(ROW(A3:A22)-2<=10),B3:B22)" _
)
cumt = cumt + Timer - inct
Next n
Debug.Print "SUMPROD: " & Format(cumt, "0.00")

cumt = 0
For n = 1 To MAXITER
'ActiveSheet.Calculate
inct = Timer
x = ActiveSheet.Evaluate( _
"=SUMPRODUCT(--(OFFSET(A3:A22,4-1,0,13-4+1)=A1),OFFSET(B3:B22,4-1,0,13-4+1))" _
)
cumt = cumt + Timer - inct
Next n
Debug.Print "OFFSET: " & Format(cumt, "0.00")
End Sub

-------------------


I discovered during this that VBA's Evaluate doesn't handle INDIRECT
at least not on my system under XL97 SR-2, so I wasn't able to use thi
approach to check recalc time for the INDIRECT formula.

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

 

[Frank Kabel]

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