Excel formula sumproduct consuming more time (EPPlus)

Question

I have a workbook, which contains 2 sheets. comments and Summary

In comments sheet I have columns - category, type and status

category takes string values like comments, security, control flow etc..
type takes values as P or C
status takes values as Y or N

In my summary, I have columns - category, P_type, C_Type, total_status

category contains same strings as in comments sheet
P_type - no of p-type of corresponding category
C_type - no of c-type of corresponding category
total_staus - no of Y's of corresponding category of P and C type

for that I have filled each cell with formulas as

if (j == 3)
{
    cellRange.Formula = "SUMPRODUCT((Comments!B:B=B" + i + ")*(Comments!D:D=\"P\"))";
}

if (j == 4)
{
    cellRange.Formula = "SUMPRODUCT((Comments!B:B=B" + i + ")*(Comments!D:D=\"C\"))";
}

if (j == 5)
{        
    cellRange.Formula = "SUMPRODUCT((Comments!B:B=B" + i + ")*(Comments!G:G=\"Y\"))";
}

Note: comments sheet gets filled using database and it can have any number of rows. In summary sheet, column B contains category strings In comments sheets, column D and Column G contains type and status respectively

whenever I open excel sheet or change any cell values in column type and status, it takes noticeable amount of time.
No of category is 14, so there are total 42 SUMPRODUCT formulas.

I have change the excel settings to use all processors; in my system 8 are available.

any suggestions to reduce these sumproduct calculations.

Thanks

atulya atulya · Accepted Answer · 2014-05-29T11:44:51

I added row limit in the search range for each formula. Now there is a significant change in time. Much better.

 cellRange.Formula = "SUMPRODUCT((Comments!B7:B"+nTotalRows+"=B" + i + ")*(Comments!D7:D"+nTotalRows+"=\"P\"))";

Excel formula sumproduct consuming more time (EPPlus)

1 Answers