Dear Whizbang AND MikeRickson,

This thread has been so far very informative and revolutionary as I was always using the INDIRECT function and now on I can always try the INDEX function to get the Exclusive Range...

Now I was trying to get the Range for the Sumproduct and i changed the code to this but it still gives an VALUE ERROR..


=SUMPRODUCT(--(PhaseRng=MATCH(L$1,$L$1:$N$1,0)),(SQFTRng))

where the PhaseRng is to be D3:D210 and then the Formula is to be used in the CELL - L2

PhaseRng = =INDEX(Inventory!$D:$D,3,1):INDEX(Inventory!$D:$D,COUNTA(Inventory!$D:$D),1)

SQFTRng = =INDEX(Inventory!$G:$G,3,1):INDEX(Inventory!$G:$G,COUNTA(Inventory!$G:$G),1)
Is it because the Starting Range is D3 and the Used Range is L2..

Warm Regards
e4excel