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

 

[mart37]

And how long takes:
SUMIF('PASTE LP99'!A1:A200000, A43, 'PASTE LP99'!L1:L200000)

 

[mrsushi]

And how long takes:
SUMIF('PASTE LP99'!A1:A200000, A43, 'PASTE LP99'!L1:L200000)

Still takes a bit of time tbh. However, restricting it to x number of rows may speed it up slightly.

 

[Fluff]

I don't think you'll find a faster formula than sumif.
How many cells do you have those formulae in?

 

[mrsushi]

I don't think you'll find a faster formula than sumif.
How many cells do you have those formulae in?

Worksheet 1 in Column A has 55K ID's, WorkSheet 2 has 250,000 rows of data in Column L

 

[Fluff]

Ouch, you could possibly use a macro, but that would not update automatically if the data changed.

 

[mrsushi]

Ouch, you could possibly use a macro, but that would not update automatically if the data changed.

I've incorporated the formula into a macro, however, that makes no difference to the speed. I heard arrays could solve this calculation issue?

 

[Fluff]

Incorporating that formula into a macro, would be even worse than using the formula.
Maybe something like

Sub mrsushi()
   Dim Ary 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("sheet1")
      Ary = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      Ary(r, 2) = Dic(Ary(r, 1))
   Next r
   Sheets("Sheet1").Range("A2").Resize(UBound(Ary), 2).Value = Ary
End Sub

Change sheet1 name to match your sheet & this will put the result of the sumif in col B

 

[mrsushi]

Incorporating that formula into a macro, would be even worse than using the formula.
Maybe something like

Sub mrsushi()
   Dim Ary 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("sheet1")
      Ary = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      Ary(r, 2) = Dic(Ary(r, 1))
   Next r
   Sheets("Sheet1").Range("A2").Resize(UBound(Ary), 2).Value = Ary
End Sub

Change sheet1 name to match your sheet & this will put the result of the sumif in col B

the sheet1 where my formula is to return the value of the sumifs is in Column AA (starts from AA2). Which parts of the above code need to be changed pls?

Sheets("Sheet1").Range("A2").Resize(UBound(Ary), 2).Value = Ary
to be changed to
Sheets("Sheet1").Range("A2").Resize(UBound(Ary), 27).Value = Ary

Any others i need to change pls?

 

[Fluff]

How about

Sub mrsushi()
   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("sheet1")
      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("Sheet1").Range("AA2").Resize(UBound(Oary)).Value = Oary
End Sub