Apply sumifs in VBA on very large range

[Groovicles]

Hi everyone,

I have a sumifs formula that needs to be applied to about 130,000 rows across about 560 columns (total is just under 73 million cells).

=sumifs('Sheet1'!$L$2:$L$130000,'Sheet1'!$J$2:$J$130000,$A2,'Sheet1'!$B$2:$B$130000,B$1) - Note, $A2 and B$1 are in the worksheet that I want the results in (I.e. Sheet2)

I have a feeling it would be extremely slow if I just applied the formula in excel 2010 directly.

How can I apply a sumifs formula in VBA for such a range?

Thanks!!

[mikeTRON]

I would try doing this in Powerpivot as it seems to handle millions of calculations better than excel.

[Leith Ross]

Hello Groovicles,

I can tell you from experience VBA will not be of much help. Files this large will either hang or crash Excel because of memory resource limitations. This has nothing to do with your RAM or hard disk space. The memory resources are set in the Excel application and there is no way to change them.