Macro of SUMIFS Date & Time cell reference for large data

[ibmy]

Hi,
I have a large data ranging between 200k-400k row every weekday to analyze.
I need to get result for Column P, Q, R, S, T, U , V, W, X, Y from Row 2 to Row 7201 (sometimes until Row 1441 , 481 ,241 ,121 depend on what kind of analyze is).
Current formula I use is " =SUMIFS($B$2:$B$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2) " for Columm P.
Every Column P, Q, R, S, T, U , V, W, X, Y use same Date&Time cell reference Column N and 0
Date and Time data is on Column A while data for Column P, Q, R, S, T, U , V, W, X, Y are Column B, C, D, E, F, G, H, I , J ,K respectively.

Current time to complete calculation for one Column P is 3:30 minute, total of 9 column is average of 30 minutes.

This is some sample from data :

sumifs ab.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	local time	a0+1	a0-1	b0-1	b0+1	a+	a-	a0	b-	b+	b0			.	M1	A0+1	A0-1	B0-1	B0+1	a+	a-	a0	b-	b+	b0
2	18.10.21 5:00													18.10 5:01	18.10 5:00	1	2	1	3	10	7	3	3	6	11
3	18.10.21 5:00						1			1				18.10 5:02	18.10 5:01
4	18.10.21 5:00						1				1			18.10 5:03	18.10 5:02
5	18.10.21 5:00					1					1			18.10 5:04	18.10 5:03
6	18.10.21 5:00					1					1			18.10 5:05	18.10 5:04
7	18.10.21 5:00				1			1		1				18.10 5:06	18.10 5:05
8	18.10.21 5:00	1				1					1			18.10 5:07	18.10 5:06
9	18.10.21 5:00				1	1				1				18.10 5:08	18.10 5:07
10	18.10.21 5:00						1		1					18.10 5:09	18.10 5:08
11	18.10.21 5:00					1					1			18.10 5:10	18.10 5:09
12	18.10.21 5:00					1					1			18.10 5:11	18.10 5:10
13	18.10.21 5:00						1				1			18.10 5:12	18.10 5:11
14	18.10.21 5:00					1					1			18.10 5:13	18.10 5:12
15	18.10.21 5:00						1				1			18.10 5:14	18.10 5:13
16	18.10.21 5:00				1	1				1				18.10 5:15	18.10 5:14
17	18.10.21 5:00					1			1					18.10 5:16	18.10 5:15
18	18.10.21 5:00							1		1				18.10 5:17	18.10 5:16
19	18.10.21 5:00		1				1				1			18.10 5:18	18.10 5:17
20	18.10.21 5:00			1		1			1					18.10 5:19	18.10 5:18
21	18.10.21 5:00							1		1				18.10 5:20	18.10 5:19
a0+1b0-1

Cell Formulas
Range	Formula
P2	P2	=SUMIFS($B$2:$B$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
Q2	Q2	=SUMIFS($C$2:$C$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
R2	R2	=SUMIFS($D$2:$D$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
S2	S2	=SUMIFS($E$2:$E$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
T2	T2	=SUMIFS($F$2:$F$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
U2	U2	=SUMIFS($G$2:$G$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
V2	V2	=SUMIFS($H$2:$H$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
W2	W2	=SUMIFS($I$2:$I$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
X2	X2	=SUMIFS($J$2:$J$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)
Y2	Y2	=SUMIFS($K$2:$K$400000,$A$2:$A$400000,">="&O2,$A$2:$A$400000,"<"&N2)

 

[Dermot]

Try this custom code below. It runs very fast.
It takes 3 parameters
rangeToSum = the range to sum, B2:K400000
rangeTime = the list of times, A2:A400000
rangeLow=the list of times you want to match, O2:O50000 (assuming you have 50000 rows in O, change this)
So for your spreadsheet, use this formula in P2 (leave Q2, etc blank)

=xsumifs(B2:K400000,A2:A400000,O2:O50000)

Assuming you have a reasonably up to date Excel version, It should fill the range P2:Y2 and the rows below, ie one formula does it all

Function xSumiFS(rangeToSum, rangeTime, rangeLow)
  Dim arrSum, i As Long, j As Long, k As Long, n As Long, arrLow, arrTime, dict, sumCols
  arrSum = rangeToSum
  arrLow = rangeLow
  arrTime = rangeTime
  Set dict = CreateObject("scripting.dictionary")
  sumCols = UBound(arrSum, 2)

  For i = 1 To UBound(arrLow)
    dict.Add arrLow(i, 1), i
  Next i

  'now we run through the data
  ReDim A(1 To UBound(arrLow), 1 To sumCols) As Single 'output list
  For i = 1 To UBound(arrSum)
    j = dict(arrTime(i, 1))
    If j > 0 Then
      For k = 1 To sumCols
        A(j, k) = A(j, k) + arrSum(i, k)
      Next k
    End If
  Next i
  xSumiFS = A
End Function

 

[kevin9999]

I think Dermot's answer is the way to go. I did the following purely as a coding exercise. Tested with 400K of data in col A x 7201 rows in col P. Takes about 45 seconds to do all 10 columns. Assumes columns AA:AK are free to use as helper columns.

Sub ibmy()
    Dim rng As Range, r As Range, ws As Worksheet
    Dim i As Long, j As Long, n As Long, lr1 As Long, lr2 As Long, x As Long
    Dim txt As String
    Dim ar, t
    t = Timer
    
    Application.Calculation = xlManual
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
    
    Set ws = Sheet1 '<~~ change to suit
    Set rng = ws.Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    ar = ws.Range("A1").CurrentRegion

    With CreateObject("scripting.dictionary")
        For Each r In rng
            txt = r.Value
        If Not .Exists(txt) Then
            n = n + 1
            .Add txt, n
            For j = 1 To UBound(ar, 2)
                ar(n, j) = r.Offset(, j - 1)
            Next j
        Else
        For i = 2 To UBound(ar, 2)
            ar(.Item(txt), i) = ar(.Item(txt), i) + r.Offset(, i - 1)
        Next i
        End If
        Next

    ws.Range("AA1").Resize(n - 1, UBound(ar, 2)) = ar
    End With
    
    lr1 = ws.Cells(Rows.Count, 15).End(xlUp).Row
    lr2 = ws.Cells(Rows.Count, 27).End(xlUp).Row
    
    n = 2
    x = 2
    For n = 2 To 11
    With ws.Range(ws.Cells(2, n + 14), ws.Cells(lr1, n + 14))
        .FormulaR1C1 = "=if(vlookup(RC15,R2C27:R" & lr2 & "C27,1)=RC15,vlookup(RC15,R2C27:R" & lr2 & "C37," & x & "),"""")"
        .Value2 = .Value2
    End With
        x = x + 1
    Next n
    
    ws.Range("AA1:AK1").EntireColumn.ClearContents
    Application.Calculation = xlAutomatic
    Application.EnableEvents = True
    MsgBox Timer - t
End Sub