Use VBA to speed up formula

[Viking88]

Is there a way VBA can be used to speed up this formula:

=SUM(IF((Info!$F$1:$F$65000=$A8)*(INfo!$N$1:$N$65000<=D$6)*(Info!$N$1:$N$65000>B$6),(Info!$I$1:$I$65000)))

The reason for this is that on my spreadsheet this formula is dragged through cells b8 through bg32 and eats up a lot of time in calculating the results.

Any suggestions are welcome.

 

[MrKowz]

Why do you have that MASSIVE range defined in there? Try limiting the range to fit your data.

Also, native excel formulas will be faster than VBA. But alas, array-evaluated formulas are inheritantly slow.

What version of Excel do you use?

 

[Viking88]

The range of data actuall goes to 62,000 rows so I just used the entire column to make sure I pick up all the data month after month.

I am currently using Excel 2007.

 

[MrKowz]

Try a SUMIFs formula instead, it should evaluate MUCH faster

=SUMIFS(Info!$I:$I,Info!$F:$F,$A8,Info!$N:$N,"<="&D$6,Info!$N:$N,">"&B$6)

 

[Viking88]

Thanks I didn't even know the SUMIFS was in 2007.

 

[MrKowz]

Thanks I didn't even know the SUMIFS was in 2007.

No problem - thanks for the feedback.

SUMIFS and COUNTIFS was added in Excel 2007, which can use entire column references and also evaluates significantly faster than a SUMPRODUCT or SUM(IF()) formula.