Excel - Calculating formula taking too long. Is there an alternative to the below formula.

[mrsushi]

I have the below formula which references another sheet and returns the total summary value on sheet lp99. However, the sheet(Paste LP99) has over 200,000 lines of data and the calculation takes near enough 45min - 1 hour to calculate. Is there a alternative which can be used in order to speed up the calculation? Ive heard arrays could work but not used these before?


SUMIF('PASTE LP99'!A:A, A43, 'PASTE LP99'!L:L)
SUMIF('PASTE LP99'!A:A, A43, 'PASTE LP99'!V:V)


Any help would be much appreciated

Many thanks
M

 

[Fluff]

Glad to help & thanks for the feedback.

 

[mrsushi]

I've rerun the macro and just uncovered that the macro did populate results. However, a number of cells havent pulled any figures in. The below is the macro used

Sub mrsushi1()

   Dim Ary As Variant, Oary As Variant

   Dim Dic As Object

   Dim r As Long



   Set Dic = CreateObject("Scripting.dictionary")

   With Sheets("PASTE LP99")

      Ary = .Range("A2:L" & .Range("A" & Rows.Count).End(xlUp).Row).Value2

   End With

   For r = 1 To UBound(Ary)

      Dic(Ary(r, 1)) = Dic(Ary(r, 1)) + Ary(r, 12)

   Next r

   With Sheets("Available Prices")

      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2

      Oary = .Range("AA2:AA" & .Range("A" & Rows.Count).End(xlUp).Row).Value2

   End With

   For r = 1 To UBound(Ary)

      Oary(r, 1) = Dic(Ary(r, 1))

   Next r

   Sheets("Available Prices").Range("AA2").Resize(UBound(Oary)).Value = Oary

End Sub

Hi Fluff, Just going back to this formula. I've noticed that some random figures are being returned as the data appears not to be on the other sheet. How would the formula be amended to say - if value isnt on other sheet, then return with nothing?

Many thanks
Martin

 

[Fluff]

It's actually a macro & not a formula.

How would the formula be amended to say - if value isnt on other sheet, then return with nothing?

That's what it did originally, but you wanted that changed.

 

[mrsushi]

It's actually a macro & not a formula.

That's what it did originally, but you wanted that changed.

ok, will check back to see what the macro is doing.