Dynamic range names, multiple criteria, sumproduct

[goss9394]

Hi all

I'm trying to use dynamic ranges in a in a sumproduct formula
A. compare for unit
B. compare for category
C. Which range to use for "Adjacent" values) Stored in $E$1 from Lookup

Here is what I have so far

=SUMPRODUCT((WORTotals_Units=$A501)*(WORTotals_Category=$F$500)*$E$1)

But returns #Value! error

Also tried

=SUMPRODUCT((WORTotals_Units=$A501)*(WORTotals_Category=$F$500)*INDIRECT(($E$1)))

But returns #Ref! error

Here are my dynamic ranges (if I did everything correctly)

WORTotals_Category
=OFFSET(WOR_Totals!$D$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
WORTotals_Units =OFFSET(WOR_Totals!$A$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
WorTotalWk1 =OFFSET(WOR_Totals!$I$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
'$E$1 from Lookup
WorTotalWk2 =OFFSET(WOR_Totals!$J$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
'$E$1 from Lookup
WorTotalWk3 =OFFSET(WOR_Totals!$K$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
'$E$1 from Lookup
WorTotalWk4 =OFFSET(WOR_Totals!$L$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
'$E$1 from Lookup
WorTotalWk5 =OFFSET(WOR_Totals!$M$2,0,0,COUNTA(WOR_Totals!$A:$A),1)
'$E$1 from Lookup


Thanks
-goss

 

[Biff]

Hi!

Normally, you would use Indirect, however, because you're using dynamic
ranges the range name referenced by Indirect resolves to the =Offset(....)
formula and is not a usable text representation of a range.

One way to get around this is to use the Choose function in combination with
Match.

=SUMPRODUCT((WorTotals_Units=$A501)*(WorTotals_Category=$F$500)*CHOOSE(MATCH($E$1,$G$1:$G$5,0),WorTotalWK1,WorTotalWK2,WorTotalWK3,WorTotalWK4,WorTotalWK5))

The named ranges are in a list - G1:G5

You can make the formula shorter by creating a named formula that refers to:

Name: WorTotal

=CHOOSE(MATCH(E1,G1:G5,0),WorTotalWK1,WorTotalWK2,WorTotalWK3,WorTotalWK4,WorTotalWK5)

Then:

=SUMPRODUCT((WorTotals_Units=$A501)*(WorTotals_Category=$F$500)*WorTotal)

Biff