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!
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. |