referencing a range in Sumproduct function

  • Thread starter Thread starter caroline
  • Start date Start date
C

caroline

hello,
I am using a lot of sumproduct functions which are using the same range as
last item.
the range is defined by a rather complicated formula as I need a lot of
variables:
(INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRECT("'" & $B$9 &
"'!C:C",TRUE),0),14):INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRECT("'" & $B$9 &
"'!C:C",TRUE),0)+31,14))

example of SUMPRODUCT function I am using:
SUMPRODUCT((Input!$C$16:$C$47="Yes")*(Input!$E$16:$E$47="nonLCM")*(INDEX(INDIRECT("'"
& $B$9 & "'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRECT("'" & $B$9 &
"'!C:C",TRUE),0),14):INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRECT("'" & $B$9 &
"'!C:C",TRUE),0)+31,14)))

It works fine but the calculation is very slow.
i thought that a way round would be to define the range once. for instance
in A1 writing (INDEX(INDIRECT etc........and then reference it in all my
SUMPRODUCT functions
SUMPRODUCT((Input!$C$16:$C$47="Yes")*(Input!$E$16:$E$47="nonLCM")*A1)

How can I do that?
 
Hi,

One thing you might try is creating a range name equal to a portion of the
function that is being used repeatedly. Also, exact matches tend to be
rather slow although there are ways to improve them.

The range name idea is similar to the suggestion you are making. In other
words someting that could be calculated once outside the formula and then
referenced should improve the results.

It looks as though

(INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRECT("'" & $B$9 &
"'!C:C",TRUE),0),14):INDEX(INDIRECT("'" & $B$9 &
"'!$A$1:$AF$5000",TRUE),MATCH(AnalysisItem2,INDIRECT("'" & $B$9 &
"'!C:C",TRUE),0)+31,14))

is fixed onless you are copying the formula to the right? If so you could
define that portion as a range name or put it in a cell and reference it.
I'm not sure if one is faster than the other.
 
The problem is in using the INDIRECT function.

How many different sheets will you be referencing in B9?
 
Ok, this may be hard to explain (and undertand)...but here goes...

Let's assume the sheets you want to reference are named:

Sheet2
Sheet3
Sheet4

In your formula you're referencing the 14th column of a range on each of
those sheets. That 14th column is the range N1:N5000

This formula is entered on Sheet1. So, Sheet1 B9 will contain the sheet name
of either Sheet2, Sheet3 or Sheet4.

Create these defined names.
Goto Insert>Name>Define

Name: Sheet
Refers to: =MATCH(Sheet1!$B$9,Sheets,0)

Name: Sheets
Refers to: ={"Sheet2","Sheet3","Sheet4"}

Name: Rng1
Refers to: =Sheet2!$N$1:$N$5000

Name: Rng2
Refers to: =Sheet3!$N$1:$N$5000

Name: Rng3
Refers to: =Sheet4!$N$1:$N$5000

Name: SheetRng
Refers to: =CHOOSE(Sheet,Rng1,Rng2,Rng3)

Then, this will replace your current (INDEX(INDIRECT(......)) expression:

INDEX(SheetRng,MATCH(AnalysisItem2,CHOOSE(Sheet,Sheet2!C:C,Sheet3!C:C,Sheet4!C:C),0)):INDEX(SheetRng,MATCH(AnalysisItem2,CHOOSE(Sheet,Sheet2!C:C,Sheet3!C:C,Sheet4!C:C),0)+31)

This will allow you to still copy the formula across and the reference to
C:C will increment as needed.

It's a bit shorter and it gets rid of those volatile function calls.
 
Back
Top