Formula make slow process

[Daniel Valle]

Hi, I'm Using

=SUMPRODUCT((I:I=I2)*(J:J=1)*(O:O>="M"))

In 30,000 rows and the process is very slow and sometimes excel doesn't work

Can I use another option?

 

[Hui]

Simply limit the ranges
Instead of I:I try I1:I100
Etc
Adjust to the size of your data

 

[Daniel Valle]

Thank you HUI,,,, is better now

 

[jeffreyweir]

Even though it's better, you should never use so many SUMPRODUCTS in a workbook if you can help it.

Alternatives include:
1. Use a PivotTable (recommended)
2. Use Excel's DSUM function (Google to see examples)
3. Sort your data alphabetically and use something called Range Slicing.