Speedier Alternatives to SUMIFS

[Tarver]

I have a fairly large dataset (350,000 rows) for which I need to sum data for 12,000 customers. Using SUMIFS is a long process and can take up to 10 minutes, because the same calculations are being repeated over and over again.

Is there a faster way?

Attached is a look at a sample of my data and a small table of the output I'm attempting to create. Your ideas on a faster way to calculate this result are welcomed! Thanks!

Predictive AR Model v1.xlsx
	A	B	C	D	E	F	G
1	Account no	Posting date	Document Type	Amount
2	1000096	7/1/2021	SI	$955.00		Start Date	7/5/2021
3	1000084	7/2/2021	SI	$960.00		End Date	7/16/2021
4	1000084	7/2/2021	SI	$1,395.00
5	1000084	7/2/2021	SI	$793.95		Customer	Amount
6	1000084	7/2/2021	SI	$1,700.00		1000052	$0.00
7	1000084	7/2/2021	SI	$375.00		1000084	$20,428.00
8	1000084	7/2/2021	SI	$360.00		1000096	$0.00
9	1000084	7/2/2021	SI	$1,235.00
10	1000084	7/2/2021	SI	$960.00
11	1000084	7/9/2021	SI	$740.35
12	1000084	7/13/2021	SI	$618.00
13	1000084	7/13/2021	SI	$1,728.00
14	1000084	7/13/2021	SI	$713.00
15	1000084	7/16/2021	SI	$424.00
16	1000084	7/16/2021	SI	$470.00
17	1000084	7/16/2021	SI	$2,610.00
18	1000084	7/16/2021	SI	$572.00
19	1000084	7/16/2021	SI	$1,055.00
20	1000084	7/16/2021	SI	$25.00
21	1000084	7/16/2021	SI	$3,745.00
22	1000084	7/9/2021	SI	$465.65
23	1000084	7/16/2021	SI	$6,560.00
24	1000084	7/16/2021	SI	$702.00
25	1000052	7/19/2021	SI	$822.00
26	1000084	7/23/2021	SI	$3,190.00
27	1000084	7/23/2021	SI	$835.00
28	1000084	7/23/2021	SI	$1,070.00
29	1000084	7/23/2021	SI	$4,480.00
30	1000052	7/23/2021	SI	$358.25
31
Sheet1

Cell Formulas
Range	Formula
F6:F8	F6	=SORT(UNIQUE(Table4[Account no]))
G6:G8	G6	=SUMIFS(Table4[Amount],Table4[Account no],F6#,Table4[Posting date],">="&$G$2,Table4[Posting date],"<="&$G$3)
Dynamic array formulas.

 

[kevin9999]

One more point of follow-up for you: I've now updated my sheet to run your macro code when the desired date range is updated and I'm using it twice to get an average historical sales trend and then again to get a recent sales trend to compare the two. Ultimately the project is identify customers who may have financial issues, who have either slowed the rate at which they pay invoices or have suddenly spiked in new orders. Your contribution was a big help!

Thanks for letting me know

 

[Tarver]

What's weird here is that when I call this code from another piece of code that looks for changes, it always hangs up on this line with a "Method 'Range" of object '_Worksheet' failed: