Match - Array Problem
Sumproduct is not that fast when it works as an array horse like in
sumproduct(--(range1=x),--(range2=y),range3)
Sumproduct could be used in this case if the values that you want to
retrieve in Data!V1:V5000 are numerical? If so use
=SUMPRODUCT(--(Data!U1:U5000=A2),--(Data!A1:A5000=B1),Data!V1:V5000)
which still probably would be slow but most likely faster than the
INDEX(MATCH combo
A much faster option but quite labour intensive to setup would be to use for
instance a hidden column (you would hide it when you are done with the
setup) and use a single formula per row, basically
=IF(AND(Data!U1=$A$2,Data!A1=$B$1),Data!V1,"")
copy down 5000 rows and then simply sum the whole column of help formulas.
That would be much faster than a single array formula
--
Regards,
Peo Sjoblom
"nospaminlich" wrote in message
...
Many thanks Peo. That works a treat although as you predicted it is a bit
slow as it's a busy workbook.
I assume it can't be done without an array formula in the same way
Sumproduct can be used as an alternative when calculating results?
Thanks again
|