slow calc SUMPROD

[Mcstefan]

Hi,

I am using the formula below simply to add the numbers of one column if certain criterias are met. The file has a large number of formulas like this one and more than that, the data table has a large number of lines and columns. After updating the sql data, the SUMPROD below takes more than one minute to calculate. Any ideas of what could I replace this formula with to speed up the calculation?=SUMPRODUCT(--('SQL WRAP Data'!$A$3:$A$200867=$A$1)*('SQL WRAP Data'!$B$3:$B$200867=$A96)*('SQL WRAP Data'!$C$3:$C$200867=$B96)*('SQL WRAP Data'!$D$3:$D$200867=$B97),INDEX('SQL WRAP Data'!$I$3:$CB$200867,,MATCH(X$6,'SQL WRAP Data'!$I$2:$CB$2,0)))

Cristian.

 

[Caleeco]

SUMPRODUCT is notoriously slow. If you want to speed it up, you're better off using 'helper columns' to do the multiplicaton, and then a SUMIF at the end.

Alternatively, maybe a Pivot Table can produce the result you're looking for?

Caleeco

 

[Mcstefan]

Thank you