Alternative to Sumproduct

[Corleone]

hi
I have a spread sheet which has a few thousand formulas similar to the one below causing the spreadsheet to take a while to calculate
whenever an entry is made
is there an alternative to the formula below - im using excel 2010


=SUMPRODUCT((ACTIVITIES!$C$2:$C$5000=$A7)*(ACTIVITIES!$N$2:$N$5000=CI$1))

Thanks

 

[Joe4]

Take a look at the COUNTIFS formula: https://www.techonthenet.com/excel/formulas/countifs.php

 

[Corleone]

Cheers Joe
I have just tried the countifs formula which works fine, however the calc duration is about the same

 

[Joe4]

Are you saying that you have a few thousand of these SUMPRODUCT or COUNTIF formulas?
That probably would tax the system.
So you have any VBA code running in the background too?

It sounds like you *may* possibly have a database-type environment (I don't know enough about your project to say for sure, but the fact you may be using thousands of formulas like that suggests that might be the case).
While you can use Excel for it, that really isn't what it was designed for, so performance may be slow and clunky. You may have better results using a database program like Access, SQL, or Oracle.

 

[Tetra201]

Have you looked into DCOUNTA function?