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
|