Macro Alternative to this Formula?

[hrithik]

I have a million rows and the formula "=SUMIF(A2:$A$924823, A2, B2:$B$924823)" just takes forvever.

I just need to calculate this once.....Is it possible to have a faster method?

I know Dictionary scripting is faster but I have no experience with it......experts please help!

Related thread:
http://www.mrexcel.com/forum/showthread.php?t=547028

 

[Jonmo1]

do you really have data in ALL 924822 Cells?

Well, VBA won't calculate it any faster than the formula..
However, it can be advantageous to be able to calculate "On Demand" instead of every time a cell changes...

Try

this puts the result in C2

Sub Test()
Range("C2").Value = Application.Sumif(Range("A2:A924823"), Range("A2"), Range("B2:B924823"))
End Sub

 

[hrithik]

Yes, I have valid data in all the 924823 cells

 

[hrithik]

Dictionary experts please help on this!