Using VBA to limit the range of SUMIF formulas

[Moosles]

I have a workbook with 2 tabs that are relevant to my query, 'Workings' and 'Data'.

On the 'Workings' tab I have an array of ~3000 SUMIF formulas that looks at ~12000 rows of data on the 'Data' tab. As this cumulatively looks at ~37M rows of data this is slowing down my workbook, so I want to use a macro to limit the range and sum_range within these SUMIF formulas to just the rows in which the first and last instance of each criteria appears. For example if criteria X in (cell A1 of the 'Workings' tab) first appears in row 2040 of the 'Data' tab and last appears in row 2825 of the 'Data' tab, I want to limit the SUMIF formula to be:

=SUMIF('Data'!A2040:A2825,'Workings'!A1,'Data'!B2040:B2825)

Rather than the altogether more inefficient:

=SUMIF('Data'!A1:A12000,'Workings'!A1,'Data'!B1:B12000)

Not all of the criteria in the ~3000 rows of SUMIF formulas will actually be in the 'Data' tab, and so if that is the case I want to limit the SUMIF ranges to a single row to minimise impact. I've done a crude experiment using INDIRECT and this allows me to reduce the total number of rows of data considered within the SUMIF formulas to ~300k, which has a noticeable impact on file performance, but I want to avoid using INDIRECT where possible.

To find the rows in the 'Data' tab that first and last instance of a criteria appears, I've created named ranges in the 'Workings' tab called FirstInstanceRange (column AK in my workbook) and LastInstanceRange (column AL in my workbook), located adjacent to each other. I've named the criteria range in the 'Data' tab (column A in my workbook) CriteriaRange. The range in the 'Workings' tab that has the ~3000 SUMIF formulas is called FormulaRange.

I want to apply the code to assign the value for each respective row to the SUMIF formula, but I can't work out how to obtain XXXX (the row number of the first instance) or YYYY (the row number of the last instance)
The code I have so far is:

Sub ApplyReduction()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("FirstInstanceRange").FormulaR1C1 = "=IFERROR(MATCH(RC1,CriteriaRange,0),1)"
Range("LastInstanceRange").FormulaR1C1 = "=IF(RC[-1]=1,RC[-1],SUMPRODUCT(MAX(ROW(CriteriaRange)*(RC1=CriteriaRange))))"

Range("FormulaRange").FormulaR1C1 = "=SUMIF('Data'!R" & XXXX & "C1:R" & YYYY & "C1,RC1,'Data'!R" & XXXX & "C2:R" & YYYY & "C2)"

Range("FirstInstanceRange").Clear
Range("LastInstanceRange").Clear

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Any suggestions welcome

 

[JackDanIce]

Hi, it's a little unclear what the purpose of this is.

If it's having dynamic rows of data, are you using tables?
Tables adjust for end row, i.e. you won't need to calculate the row number which may save switching between VBA and sheet calculation.

Can you post an example of the file?

 

[Moosles]

Hi JackDanIce,

A bit of context might help here. The 'Data' tab is a budget tab that has ~12000 rows of tasks, and each task is broken down by resource and region. The 'Data' tab has a concatenate in column A combining the region and resource, with columns for hours, rates and costs. I want to sum the hours (column B). The region for each resource is variable depending on inputs added via a separate sheet. The number of rows in the 'Data' tab is static, but the row number of the first and last instance of a particular concatenate of region and resource is variable. Adding a table in here is not an option because it is dependent on so many independent inputs and the end users would probably have a fit trying to adjust to it.

Likewise, the number of rows in the 'Workings' tab is also static, at ~3000 rows, and the column with the SUMIF formula currently looks at all ~3000 combinations of Resource and Region, all of which are possible. This SUMIF formula is a bottleneck in the calculation time of the workbook, mainly because such a high % of the 3000 rows are not always needed, and a high % of the rows within the SUMIF ranges themselves are not required, so limiting the ranges to just the required rows would cut down on a ton of processing time. In the instances where the combination of resource and region is not present I want the range of the SUMIF formula to only look at a single row. Doing so could cut down on over 95% of the rows to be calculated in a lot of cases, resulting in noticeable improvement in calculation speed. Adding a table in here also is not an option, again because of the amount of push back that I'd get from the end users trying to adjust to the new appearance. As much as the idea of tables might be beneficial, there's only so much change to their day to day that they will accept (very much learnt from experience!)

So my proposed solution was to have a macro that would run whenever the 'Workings' tab is selected, as this would have next to no impact on the overall user experience and wouldn't require the user to continually run macros themselves if input information gets changed.

The first formula to be added by the macro calculates the first row that a particular concatenate of region and resource occurs within the 'Data' tab, in column AK of the 'Workings' tab, and returns 1 if this concatenate is not present. The second formula added by the macro calculates the last row that a particular concatenate of region and resource occurs within the 'Data' tab, in column AL of the 'Workings' tab, and returns 1 if this concatenate is not present.

I've attached a much simplified version of what I'm working on below

Example Workbook.xlsb
	A	B	C
1	Concatenate	Total Hours	Task
2
3
4		0.00	A1
5	Medical Liaison_South America	0.00
6	Medical Liaison_Western EMEA	0.00
7	Medical Liaison_Eastern EMEA	0.00
8	Medical Liaison_Australia	0.00
9	Medical Liaison_India	0.00
10		46.00	A2
11	Project Assistant_South America	0.00
12	Project Assistant_Western EMEA	32.00
13	Project Assistant_Eastern EMEA	0.00
14	Project Assistant_Japan	0.00
15	Project Assistant_Asia	8.00
16	Project Assistant_Australia	6.00
17	Project Assistant_India	0.00
18		29.00	A3
19	Project Manager_Western EMEA	29.00
20		2,770.30	A4
21	Project Manager_Western EMEA	1,216.00
22	Medical Liaison_North America	0.00
23	Medical Liaison_South America	0.00
24	Medical Liaison_Western EMEA	0.00
25	Medical Liaison_Eastern EMEA	0.00
26	Medical Liaison_Japan	1,554.30
27	Medical Liaison_China	0.00
28	Medical Liaison_Asia	0.00
29	Medical Liaison_Australia	0.00
30	Medical Liaison_India	0.00
31		3,559.71	A5
32	Project Manager_Western EMEA	2,128.00
33	Medical Liaison_North America	0.00
34	Medical Liaison_South America	0.00
35	Medical Liaison_Western EMEA	0.00
36	Medical Liaison_Eastern EMEA	0.00
37	Medical Liaison_Japan	1,417.71
38	Medical Liaison_China	0.00
39	Medical Liaison_Asia	14.00
40	Medical Liaison_Australia	0.00
41	Medical Liaison_India	0.00
42		6,536.52	A6
43	Project Manager_Western EMEA	3,800.00
44	Medical Liaison_North America	0.00
45	Medical Liaison_South America	0.00
46	Medical Liaison_Western EMEA	0.00
47	Medical Liaison_Eastern EMEA	0.00
48	Medical Liaison_Japan	2,725.52
49	Medical Liaison_China	0.00
50	Medical Liaison_Asia	11.00
51	Medical Liaison_Australia	0.00
52	Medical Liaison_India	0.00
53		0.00	A7
54	Project Manager_Western EMEA	0.00
55	Medical Liaison_North America	0.00
56	Medical Liaison_South America	0.00
57	Medical Liaison_Western EMEA	0.00
58	Medical Liaison_Eastern EMEA	0.00
59	Medical Liaison_Japan	0.00
60	Medical Liaison_China	0.00
61	Medical Liaison_Asia	0.00
62	Medical Liaison_Australia	0.00
63	Medical Liaison_India	0.00
64		581.48	A8
65	Project Manager_Western EMEA	380.00
66	Medical Liaison_North America	0.00
67	Medical Liaison_South America	0.00
68	Medical Liaison_Western EMEA	0.00
69	Medical Liaison_Eastern EMEA	0.00
70	Medical Liaison_Japan	201.48
71	Medical Liaison_China	0.00
72	Medical Liaison_Asia	0.00
73	Medical Liaison_Australia	0.00
74	Medical Liaison_India	0.00
75		4,509.25	A9
76	Project Assistant_South America	18.20
77	Project Assistant_Western EMEA	64.80
78	Project Assistant_Eastern EMEA	51.00
79	Project Assistant_Japan	4,190.85
80	Project Assistant_Asia	92.50
81	Project Assistant_Australia	31.90
82	Project Assistant_India	60.00
83		14.10	A10
84	Project Assistant_South America	0.00
85	Project Assistant_Western EMEA	0.00
86	Project Assistant_Eastern EMEA	0.00
87	Project Assistant_Japan	0.00
88	Project Assistant_Asia	0.00
89	Project Assistant_Australia	14.10
90	Project Assistant_India	0.00
91		0.00	A11
92	Project Director_Western EMEA	0.00
93		250.00	A12
94	Project Director_North America	40.00
95	Project Director_Western EMEA	210.00
96
97		1,319.00	B1
98	Project Director_North America	201.00
99	Project Director_Western EMEA	1,118.00
100		1,982.50	B2
101	Project Director_North America	303.00
102	Project Director_Western EMEA	1,679.50
103		0.00	B3
104	Project Manager_Western EMEA	0.00
105		2.00	B4
106	Medical Liaison_South America	0.00
107	Medical Liaison_Western EMEA	0.00
108	Medical Liaison_Eastern EMEA	0.00
109	Medical Liaison_Japan	0.00
110	Medical Liaison_Asia	2.00
111	Medical Liaison_Australia	0.00
112	Medical Liaison_India	0.00
113	Medical Liaison_North America	0.00
114		3,402.00	B5
115	Programmer_India	3,402.00
116		587.00	B6
117	Programmer_India	587.00
118		0.00	B7
119	Logistics Coordinator_India	0.00
120		3,943.00	B8
121	Logistics Coordinator_India	3,943.00
122		20.00	B9
123	Logistics Coordinator_India	20.00
124
125		0.00	C1
126	Data Analyst_India	0.00
127		0.00	C2
128	Data Analyst_India	0.00
129		36.00	C3
130	Data Analyst_South America	3.85
131	Data Analyst_Western EMEA	7.19
132	Data Analyst_India	12.95
133	Data Analyst_North America	12.00
134		8.00	C4
135	Data Analyst_India	8.00
136		456.00	C5
137	Data Analyst_India	456.00
138		377.00	C6
139	Data Analyst_South America	60.50
140	Data Analyst_Western EMEA	113.00
141	Data Analyst_India	203.50
142		0.00	C7
143	Data Analyst_South America	0.00
144	Data Analyst_Western EMEA	0.00
145	Data Analyst_India	0.00
146		251.33	C8
147	Data Analyst_South America	40.33
148	Data Analyst_Western EMEA	75.33
149	Data Analyst_India	135.67
150		0.00	C9
151	Data Analyst_South America	0.00
152	Data Analyst_Western EMEA	0.00
153	Data Analyst_India	0.00
154	Data Analyst_North America	0.00
155		60.98	C10
156	Data Analyst_South America	0.00
157	Data Analyst_Western EMEA	0.00
158	Data Analyst_India	0.00
159	Data Analyst_North America	60.98
160		0.00	C11
161	Data Analyst_India	0.00
162		125.67	C12
163	Data Analyst_South America	6.05
164	Data Analyst_Western EMEA	11.30
165	Data Analyst_India	20.35
166	Data Analyst_North America	87.97
167		0.00	C13
168	Data Analyst_South America	0.00
169	Data Analyst_Western EMEA	0.00
170	Data Analyst_India	0.00
171	Data Analyst_North America	0.00
172		0.00	C14
173	Data Analyst_South America	0.00
174	Data Analyst_Western EMEA	0.00
175	Data Analyst_India	0.00
176	Data Analyst_North America	0.00
177		0.00	C15
178	Data Analyst_South America	0.00
179	Data Analyst_Western EMEA	0.00
180	Data Analyst_India	0.00
181	Data Analyst_North America	0.00
182		150.83	C16
183	Data Analyst_South America	24.21
184	Data Analyst_Western EMEA	45.21
185	Data Analyst_India	81.42
186
187		0.00	D1
188	Data Analyst_South America	0.00
189	Data Analyst_Western EMEA	0.00
190	Data Analyst_India	0.00
191	Data Analyst_North America	0.00
192		0.00	D2
193	Data Analyst_Western EMEA	0.00
194		0.00	D3
195	Data Analyst_South America	0.00
196	Data Analyst_Western EMEA	0.00
197	Data Analyst_India	0.00
198		0.00	D4
199	Data Analyst_South America	0.00
200	Data Analyst_Western EMEA	0.00
201	Data Analyst_India	0.00
202		0.00	D5
203	Data Analyst_South America	0.00
204	Data Analyst_Western EMEA	0.00
205	Data Analyst_India	0.00
206		44.00	D6
207	Senior Project Assistant_India	20.00
208	Senior Project Assistant_Japan	20.00
209	Senior Project Assistant_South America	2.00
210	Senior Project Assistant_North America	2.00
211	Senior Project Assistant_Western EMEA	0.00
212		717.00	D7
213	Senior Project Assistant_India	215.00
214	Senior Project Assistant_Japan	215.00
215	Senior Project Assistant_South America	123.00
216	Senior Project Assistant_North America	164.00
217		27.00	D8
218	Senior Project Assistant_India	25.00
219	Senior Project Assistant_North America	2.00
220		13.00	D9
221	Senior Project Assistant_Western EMEA	8.00
222	Senior Project Assistant_North America	5.00
223		175.00	D10
224	Senior Project Assistant_Western EMEA	175.00
225		789.00	D11
226	Senior Project Assistant_India	170.25
227	Senior Project Assistant_Japan	170.25
228	Senior Project Assistant_Western EMEA	103.50
229	Senior Project Assistant_North America	345.00
230		170.25	D12
231	Senior Project Assistant_India	170.25
232		493.35	D13
233	Senior Project Assistant_India	4.50
234	Senior Project Assistant_Western EMEA	48.45
235	Senior Project Assistant_North America	147.90
236	Senior Project Assistant_Japan	292.50
237		0.00	D14
238	Senior Project Assistant_India	0.00
239	Senior Project Assistant_Western EMEA	0.00
240	Senior Project Assistant_North America	0.00
241
242		0.00	E1
243	Medical Liaison_Japan	0.00
244		0.00	E2
245	Medical Liaison_Japan	0.00
246	Medical Liaison_Western EMEA	0.00
247		0.00	E3
248	Medical Liaison_Japan	0.00
249		0.00	E4
250	Medical Liaison_Japan	0.00
251		9.00	E5
252	Medical Liaison_Japan	9.00
253
254		8.00	F1
255	Medical Liaison_Japan	0.00
256	Medical Liaison_India	0.00
257	Medical Liaison_Western EMEA	8.00
258	Medical Liaison_North America	0.00
259		2.00	F2
260	Medical Liaison_Japan	0.00
261	Medical Liaison_India	2.00
262		0.00	F3
263	Medical Liaison_Japan	0.00
264		60.00	F4
265	Medical Liaison_Japan	16.00
266	Medical Liaison_India	0.00
267	Medical Liaison_Western EMEA	0.00
268	Medical Liaison_North America	44.00
269		41.00	F5
270	Medical Liaison_Japan	41.00
271		0.00	F6
272	Medical Liaison_India	0.00
273		0.00	F7
274	Medical Liaison_Japan	0.00
Data

Cell Formulas
Range	Formula
B4,B206	B4	=SUM(B5:B9)
B10,B83,B75	B10	=SUM(B11:B17)
B18,B120,B91	B18	=SUM(B19:B19)
B20,B64,B53,B42,B31	B20	=SUM(B21:B30)
B93,B259,B244,B220,B217,B100,B97	B93	=SUM(B94:B95)
B103,B273,B271,B269,B262,B251,B249,B247,B242,B230,B223,B192,B160,B136,B134,B127,B125,B122,B118,B116,B114	B103	=SUM(B104)
B105	B105	=SUM(B106:B113)
B129,B264,B254,B232,B225,B212,B187,B177,B172,B167,B162,B155,B150	B129	=SUM(B130:B133)
B138,B237,B202,B198,B194,B182,B146,B142	B138	=SUM(B139:B141)

Example Workbook.xlsb
	A	B	C	D	E
1	Concatenate	Total Hours Per Role		First Row	Last Row
2	Data Analyst_Asia	0		1	1
3	Data Analyst_Australia	0		1	1
4	Data Analyst_China	0		1	1
5	Data Analyst_Eastern EMEA	0		1	1
6	Data Analyst_India	918		126	205
7	Data Analyst_Japan	0		1	1
8	Data Analyst_North America	161		133	191
9	Data Analyst_South America	135		130	203
10	Data Analyst_Western EMEA	252		131	204
11	Logistics Coordinator_Asia	0		1	1
12	Logistics Coordinator_Australia	0		1	1
13	Logistics Coordinator_China	0		1	1
14	Logistics Coordinator_Eastern EMEA	0		1	1
15	Logistics Coordinator_India	3963		119	123
16	Logistics Coordinator_Japan	0		1	1
17	Logistics Coordinator_North America	0		1	1
18	Logistics Coordinator_South America	0		1	1
19	Logistics Coordinator_Western EMEA	0		1	1
20	Medical Liaison_Asia	27		28	110
21	Medical Liaison_Australia	0		8	111
22	Medical Liaison_China	0		27	71
23	Medical Liaison_Eastern EMEA	0		7	108
24	Medical Liaison_India	2		9	272
25	Medical Liaison_Japan	5965		26	274
26	Medical Liaison_North America	44		22	268
27	Medical Liaison_South America	0		5	106
28	Medical Liaison_Western EMEA	8		6	267
29	Programmer_Asia	0		1	1
30	Programmer_Australia	0		1	1
31	Programmer_China	0		1	1
32	Programmer_Eastern EMEA	0		1	1
33	Programmer_India	3989		115	117
34	Programmer_Japan	0		1	1
35	Programmer_North America	0		1	1
36	Programmer_South America	0		1	1
37	Programmer_Western EMEA	0		1	1
38	Project Assistant_Asia	101		15	88
39	Project Assistant_Australia	52		16	89
40	Project Assistant_China	0		1	1
41	Project Assistant_Eastern EMEA	51		13	86
42	Project Assistant_India	60		17	90
43	Project Assistant_Japan	4191		14	87
44	Project Assistant_North America	0		1	1
45	Project Assistant_South America	18		11	84
46	Project Assistant_Western EMEA	97		12	85
47	Project Director_Asia	0		1	1
48	Project Director_Australia	0		1	1
49	Project Director_China	0		1	1
50	Project Director_Eastern EMEA	0		1	1
51	Project Director_India	0		1	1
52	Project Director_Japan	0		1	1
53	Project Director_North America	544		94	101
54	Project Director_South America	0		1	1
55	Project Director_Western EMEA	3008		92	102
56	Project Manager_Asia	0		1	1
57	Project Manager_Australia	0		1	1
58	Project Manager_China	0		1	1
59	Project Manager_Eastern EMEA	0		1	1
60	Project Manager_India	0		1	1
61	Project Manager_Japan	0		1	1
62	Project Manager_North America	0		1	1
63	Project Manager_South America	0		1	1
64	Project Manager_Western EMEA	7553		19	104
65	Senior Project Assistant_Asia	0		1	1
66	Senior Project Assistant_Australia	0		1	1
67	Senior Project Assistant_China	0		1	1
68	Senior Project Assistant_Eastern EMEA	0		1	1
69	Senior Project Assistant_India	605		207	238
70	Senior Project Assistant_Japan	698		208	236
71	Senior Project Assistant_North America	666		210	240
72	Senior Project Assistant_South America	125		209	215
73	Senior Project Assistant_Western EMEA	335		211	239
Workings

Cell Formulas
Range	Formula
D2	D2	=IFERROR(MATCH($A2,CriteriaRange,0),1)
E2:E73	E2	=IF($D2=1,$D2,SUMPRODUCT(MAX(ROW(CriteriaRange)*($A2=CriteriaRange))))
D3:D73	D3	=IFERROR(MATCH(A3,CriteriaRange,0),1)
B2:B73	B2	=SUMIF(Data!$A$3:$A$274,Workings!$A2,Data!$B$3:$B$274)

Named Ranges
Name	Refers To	Cells
Data!_FilterDatabase	=Data!$A$1:$C$613	D2:E73
CriteriaRange	=Data!$A$1:$A$274	D2:E73
FirstInstanceRange	=Workings!$D$2:$D$73	E2

What I'd want the macro to do would to assign the first and last rows to the SUMIF formula, so for row 6 of the 'Workings' tab for example, the first and last instances of that particular concatenate in the 'Data' tab are on rows 126 and 205 respectively, so the SUMIF formula should be

=SUMIF(Data!$A$126:$A$205,Workings!$A6,Data!$B$126:$B$205)

For row 7, this concatenate is not found in the 'Data' tab, and so the formulas are returning 1, and so the SUMIF formula would be

=SUMIF(Data!$A$1:$A$1,Workings!$A6,Data!$B$1$B$1)

The only part of the macro code I'm failing to figure out is how to insert the values for XXXX (First instance row number) and YYYY (Last instance row number) into the SUMIF formulas in an automated way

Range("FormulaRange").FormulaR1C1 = "=SUMIF('Data'!R" & XXXX & "C1:R" & YYYY & "C1,RC1,'Data'!R" & XXXX & "C2:R" & YYYY & "C2)"

Obviously the benefit in the example is negligible, but spread over 1000s of rows it should be significant. Hopefully this is a bit clearer now, let me know if you have any questions!

Thanks

Moosles

 

[JackDanIce]

Hi Moosles,

I think this is your set up:

You have two sheets, Data and Working and you're returning total values from Data into Working using SUMIF formula.
You need the SUMIF formula in Working to update as soon as any change is made in Data.

However, due to the large number of rows in Data, SUMIFs are too slow, so want to augment using a macro which in turn uses additional formula in Working to determine start/end row numbers in Data, then have the macro update/replace the formula in column B of Working with restricted ranges to reduce the calculation time of those SUMIF formula in Working?

It seems over engineered at first read and generally speaking you want to keep things as simple as possible in Excel.

Reading from sheet and back to sheet is also slow which I'm not sure will be improved drastically using VBA; the formula will still need to calculate after the macro ends, even if the ranges within those formula are now reduced.

A redesign of the Data table (sheet), without the subtotals into a flat table of just Concatenate, Total Hours and Task as column headers, would help improve calculation time as well as reduce total row count but you say this can't be done.

You're also on Office 2019 which I don't think has the FILTER function, or you could try:

Other.xlsm
	A	B	C	D	E	F
1	Data				Working
2	Concatenate	Total Hours	Task		Concatenate	Total Hours Per Role
3					A	0
4					B	0
5		0	A1		C	0
6	A				D	1
7	B				E	2
8	C				F	3
9		6	A2
10	D	1
11	E	2
12	F	3
Data

Cell Formulas
Range	Formula
B5,B9	B5	=SUM(B6:B8)
F3:F8	F3	=SUM(FILTER($B$6:$B$12,$A$6:$A$12=$E3))

Using arrays and dictionaries within VBA may be one route to create specific formula before looping over the range in Working and updating cells, or given the data size on both sheets, a Power Query approach may be better than VBA, but I don't think your users will want that either.

Otherwise, based on the restrictions, I'm afraid I can't think of an elegant solution... hopefully anyone else reading can spot something missed or offer suggestion