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_Cat egory=$F$500)*$E$1)

But returns #Value! error

Also tried

=SUMPRODUCT((WORTotals_Units=$A501)*(WORTotals_Cat egory=$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