SUMPRODUCT calculation speed issues

**richardj** · 12-12-2012, 06:09 PM

Hi Guys,

I'm currently using a SUMPRODUCT formula in a spreadsheet (in approx 130,000 cells) and it is slowing the calculation down immensely! I've restricted the formula to look at specific cells not full columns etc but calculation is still slow! The formula is as below:

=IF($A13>"",(SUMPRODUCT((Sheet2!$A$6:$A$506=$E13&$G13)*(Sheet2!$CW$1:$ER$1=$C13&H$12),Sheet2!$CW$6:$ER$506)),"")

Is there a faster formula that will do do the calculation that you know of?

Any help would be much appreciated!!

Thanks

**daddylonglegs** · 12-12-2012, 06:51 PM

Is the formula picking up a single value from the table (at the intersection of the matching row and column)? If so try using INDEX/MATCH like this

=IF($A13="","",INDEX(Sheet2!$CW$6:$ER$506,MATCH($E13&$G13,Sheet2!$A$6:$A$506,0),MATCH($C13&H$12,Sheet2!$CW$1:$ER$1,0)))

**richardj** · 12-12-2012, 07:09 PM

Hi Daddylonglegs,

You've cracked it (again!) as that is definately quicker but I'm getting N/A's when there is no match, what can I add to make these a 0?

Thanks again for your help

**daddylonglegs** · 12-12-2012, 07:40 PM

Which version of Excel are you using? In Excel 2007 or later you can use IFERROR like

=IF($A13="","",IFERROR(INDEX(Sheet2!$CW$6:$ER$506,MATCH($E13&$G13,Sheet2!$A$6:$A$506,0),MATCH($C13&H$12,Sheet2!$CW$1:$ER$1,0)),0))