How to Speed Up the File Processing i.e Formulas Vs VBA ?

To do a dynamic range, i.e. allow for growth while keeping the smallest possible range, use this formula:

=A1:Index(A:A,Counta(A:A))

Since Index returns a reference, you can use it on either side of the colon, to signify the start or end of a range. The above formula will give you the entire contents of the A column (limited to used cells) without resorting to Offset or Indirect. Also, even though this uses the entire column, it is not the same as using the entire column in an array formula or Sumproduct formula. Counting an entire column is MUCH quicker than processing that same column in an array formula.

Use the above dynamic range as a named formula so that your array/sumproduct formulas will look like this:

=Sumproduct(--(Name="Test"), --(Amount > 5000))

Rather than

=Sumproduct(--(A1:Index(A:A,Counta(A:A)) = "Test"), --(B1:Index(B:B,Counta(A:A)) > 5000))


Also, using named ranges will allow Excel to calculate the size of the range only once, rather than every for every cell that uses your sumproduct formula.

See this article for using Index to make dynamic named ranged:
http://www.excelhero.com/blog/2011/0...ing-index.html