Getting #Value error on Sumproduct with non-contiguous cells
 

I'm trying sumproduct, where one of the arrays is non-contiguous:

SUMPRODUCT(($AB$607:$AB$615=TRUE)*(AY583,BD583,BE5 83,BF583,BG583,BH583,BK583,BL583,BM583))

The result in receiving a #Value error. Any suggestions or workarounds.

Thanks...AJ

Getting #Value error on Sumproduct with non-contiguous cells
 

AJ,

SUMPRODUCT appears to require you to have matching array sizes AND they must be contiguous. If you only have those 9 pairs to calculate you can do something like =AB607*AY583+AB608*BD583+...AB615*BM583

Your other option is to recreate the data elsewhere in the sheet by linking the cells. So if the new location is going to be at the beginning of a sheet make A1 =AB607, B1 =AY583, etc. and then you can use SUMPRODUCT(A1:A9,B1:B9). A variant on that would be to construct the array on a separate worksheet and pass the value back to the location you want on the original worksheet.

Hope that helps you