Hello

I am using Excel 2007. I have a worksheet with 200,000 rows of data in it- it was not my choice to set it up this way, I inherited the project!

The last column in the range of 200,000 rows is a calculated field that uses SUMPRODUCT to match certain column values for the current row against values in the entire data set.

Here is an example of the function. It is copied down the entire column for all 200k rows:

=IF(
ISERROR(V7/SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0))),0,V7/SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0)))

The IF uses ISERROR function to evaluate the expression using values relative to the current row. If the expression results in an error zero is displayed, else the expression is used. The expression divides the value in column V for current row by the total number of rows that that match the current row based on the values in three coulmns (J,E, and V). The function must loop over the entire range of rows for EACH row and as you can imagine this really sucks. It takes >10 hours to recalculate the workbook.

Can anyone recommend a better solution to achieve what I've done with the above formula using VBA or anything else? I would really appreciate the assistance!

Thanks!

-TT