Using VBA to limit the range of SUMIF formulas

Moosles

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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:
Excel Formula:
=SUMIF('Data'!A2040:A2825,'Workings'!A1,'Data'!B2040:B2825)
Rather than the altogether more inefficient:
Excel Formula:
=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:

VBA Code:
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 :)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
ABC
1ConcatenateTotal HoursTask
2
3
40.00A1
5Medical Liaison_South America0.00
6Medical Liaison_Western EMEA0.00
7Medical Liaison_Eastern EMEA0.00
8Medical Liaison_Australia0.00
9Medical Liaison_India0.00
1046.00A2
11Project Assistant_South America0.00
12Project Assistant_Western EMEA32.00
13Project Assistant_Eastern EMEA0.00
14Project Assistant_Japan0.00
15Project Assistant_Asia8.00
16Project Assistant_Australia6.00
17Project Assistant_India0.00
1829.00A3
19Project Manager_Western EMEA29.00
202,770.30A4
21Project Manager_Western EMEA1,216.00
22Medical Liaison_North America0.00
23Medical Liaison_South America0.00
24Medical Liaison_Western EMEA0.00
25Medical Liaison_Eastern EMEA0.00
26Medical Liaison_Japan1,554.30
27Medical Liaison_China0.00
28Medical Liaison_Asia0.00
29Medical Liaison_Australia0.00
30Medical Liaison_India0.00
313,559.71A5
32Project Manager_Western EMEA2,128.00
33Medical Liaison_North America0.00
34Medical Liaison_South America0.00
35Medical Liaison_Western EMEA0.00
36Medical Liaison_Eastern EMEA0.00
37Medical Liaison_Japan1,417.71
38Medical Liaison_China0.00
39Medical Liaison_Asia14.00
40Medical Liaison_Australia0.00
41Medical Liaison_India0.00
426,536.52A6
43Project Manager_Western EMEA3,800.00
44Medical Liaison_North America0.00
45Medical Liaison_South America0.00
46Medical Liaison_Western EMEA0.00
47Medical Liaison_Eastern EMEA0.00
48Medical Liaison_Japan2,725.52
49Medical Liaison_China0.00
50Medical Liaison_Asia11.00
51Medical Liaison_Australia0.00
52Medical Liaison_India0.00
530.00A7
54Project Manager_Western EMEA0.00
55Medical Liaison_North America0.00
56Medical Liaison_South America0.00
57Medical Liaison_Western EMEA0.00
58Medical Liaison_Eastern EMEA0.00
59Medical Liaison_Japan0.00
60Medical Liaison_China0.00
61Medical Liaison_Asia0.00
62Medical Liaison_Australia0.00
63Medical Liaison_India0.00
64581.48A8
65Project Manager_Western EMEA380.00
66Medical Liaison_North America0.00
67Medical Liaison_South America0.00
68Medical Liaison_Western EMEA0.00
69Medical Liaison_Eastern EMEA0.00
70Medical Liaison_Japan201.48
71Medical Liaison_China0.00
72Medical Liaison_Asia0.00
73Medical Liaison_Australia0.00
74Medical Liaison_India0.00
754,509.25A9
76Project Assistant_South America18.20
77Project Assistant_Western EMEA64.80
78Project Assistant_Eastern EMEA51.00
79Project Assistant_Japan4,190.85
80Project Assistant_Asia92.50
81Project Assistant_Australia31.90
82Project Assistant_India60.00
8314.10A10
84Project Assistant_South America0.00
85Project Assistant_Western EMEA0.00
86Project Assistant_Eastern EMEA0.00
87Project Assistant_Japan0.00
88Project Assistant_Asia0.00
89Project Assistant_Australia14.10
90Project Assistant_India0.00
910.00A11
92Project Director_Western EMEA0.00
93250.00A12
94Project Director_North America40.00
95Project Director_Western EMEA210.00
96
971,319.00B1
98Project Director_North America201.00
99Project Director_Western EMEA1,118.00
1001,982.50B2
101Project Director_North America303.00
102Project Director_Western EMEA1,679.50
1030.00B3
104Project Manager_Western EMEA0.00
1052.00B4
106Medical Liaison_South America0.00
107Medical Liaison_Western EMEA0.00
108Medical Liaison_Eastern EMEA0.00
109Medical Liaison_Japan0.00
110Medical Liaison_Asia2.00
111Medical Liaison_Australia0.00
112Medical Liaison_India0.00
113Medical Liaison_North America0.00
1143,402.00B5
115Programmer_India3,402.00
116587.00B6
117Programmer_India587.00
1180.00B7
119Logistics Coordinator_India0.00
1203,943.00B8
121Logistics Coordinator_India3,943.00
12220.00B9
123Logistics Coordinator_India20.00
124
1250.00C1
126Data Analyst_India0.00
1270.00C2
128Data Analyst_India0.00
12936.00C3
130Data Analyst_South America3.85
131Data Analyst_Western EMEA7.19
132Data Analyst_India12.95
133Data Analyst_North America12.00
1348.00C4
135Data Analyst_India8.00
136456.00C5
137Data Analyst_India456.00
138377.00C6
139Data Analyst_South America60.50
140Data Analyst_Western EMEA113.00
141Data Analyst_India203.50
1420.00C7
143Data Analyst_South America0.00
144Data Analyst_Western EMEA0.00
145Data Analyst_India0.00
146251.33C8
147Data Analyst_South America40.33
148Data Analyst_Western EMEA75.33
149Data Analyst_India135.67
1500.00C9
151Data Analyst_South America0.00
152Data Analyst_Western EMEA0.00
153Data Analyst_India0.00
154Data Analyst_North America0.00
15560.98C10
156Data Analyst_South America0.00
157Data Analyst_Western EMEA0.00
158Data Analyst_India0.00
159Data Analyst_North America60.98
1600.00C11
161Data Analyst_India0.00
162125.67C12
163Data Analyst_South America6.05
164Data Analyst_Western EMEA11.30
165Data Analyst_India20.35
166Data Analyst_North America87.97
1670.00C13
168Data Analyst_South America0.00
169Data Analyst_Western EMEA0.00
170Data Analyst_India0.00
171Data Analyst_North America0.00
1720.00C14
173Data Analyst_South America0.00
174Data Analyst_Western EMEA0.00
175Data Analyst_India0.00
176Data Analyst_North America0.00
1770.00C15
178Data Analyst_South America0.00
179Data Analyst_Western EMEA0.00
180Data Analyst_India0.00
181Data Analyst_North America0.00
182150.83C16
183Data Analyst_South America24.21
184Data Analyst_Western EMEA45.21
185Data Analyst_India81.42
186
1870.00D1
188Data Analyst_South America0.00
189Data Analyst_Western EMEA0.00
190Data Analyst_India0.00
191Data Analyst_North America0.00
1920.00D2
193Data Analyst_Western EMEA0.00
1940.00D3
195Data Analyst_South America0.00
196Data Analyst_Western EMEA0.00
197Data Analyst_India0.00
1980.00D4
199Data Analyst_South America0.00
200Data Analyst_Western EMEA0.00
201Data Analyst_India0.00
2020.00D5
203Data Analyst_South America0.00
204Data Analyst_Western EMEA0.00
205Data Analyst_India0.00
20644.00D6
207Senior Project Assistant_India20.00
208Senior Project Assistant_Japan20.00
209Senior Project Assistant_South America2.00
210Senior Project Assistant_North America2.00
211Senior Project Assistant_Western EMEA0.00
212717.00D7
213Senior Project Assistant_India215.00
214Senior Project Assistant_Japan215.00
215Senior Project Assistant_South America123.00
216Senior Project Assistant_North America164.00
21727.00D8
218Senior Project Assistant_India25.00
219Senior Project Assistant_North America2.00
22013.00D9
221Senior Project Assistant_Western EMEA8.00
222Senior Project Assistant_North America5.00
223175.00D10
224Senior Project Assistant_Western EMEA175.00
225789.00D11
226Senior Project Assistant_India170.25
227Senior Project Assistant_Japan170.25
228Senior Project Assistant_Western EMEA103.50
229Senior Project Assistant_North America345.00
230170.25D12
231Senior Project Assistant_India170.25
232493.35D13
233Senior Project Assistant_India4.50
234Senior Project Assistant_Western EMEA48.45
235Senior Project Assistant_North America147.90
236Senior Project Assistant_Japan292.50
2370.00D14
238Senior Project Assistant_India0.00
239Senior Project Assistant_Western EMEA0.00
240Senior Project Assistant_North America0.00
241
2420.00E1
243Medical Liaison_Japan0.00
2440.00E2
245Medical Liaison_Japan0.00
246Medical Liaison_Western EMEA0.00
2470.00E3
248Medical Liaison_Japan0.00
2490.00E4
250Medical Liaison_Japan0.00
2519.00E5
252Medical Liaison_Japan9.00
253
2548.00F1
255Medical Liaison_Japan0.00
256Medical Liaison_India0.00
257Medical Liaison_Western EMEA8.00
258Medical Liaison_North America0.00
2592.00F2
260Medical Liaison_Japan0.00
261Medical Liaison_India2.00
2620.00F3
263Medical Liaison_Japan0.00
26460.00F4
265Medical Liaison_Japan16.00
266Medical Liaison_India0.00
267Medical Liaison_Western EMEA0.00
268Medical Liaison_North America44.00
26941.00F5
270Medical Liaison_Japan41.00
2710.00F6
272Medical Liaison_India0.00
2730.00F7
274Medical Liaison_Japan0.00
Data
Cell Formulas
RangeFormula
B4,B206B4=SUM(B5:B9)
B10,B83,B75B10=SUM(B11:B17)
B18,B120,B91B18=SUM(B19:B19)
B20,B64,B53,B42,B31B20=SUM(B21:B30)
B93,B259,B244,B220,B217,B100,B97B93=SUM(B94:B95)
B103,B273,B271,B269,B262,B251,B249,B247,B242,B230,B223,B192,B160,B136,B134,B127,B125,B122,B118,B116,B114B103=SUM(B104)
B105B105=SUM(B106:B113)
B129,B264,B254,B232,B225,B212,B187,B177,B172,B167,B162,B155,B150B129=SUM(B130:B133)
B138,B237,B202,B198,B194,B182,B146,B142B138=SUM(B139:B141)


Example Workbook.xlsb
ABCDE
1ConcatenateTotal Hours Per RoleFirst RowLast Row
2Data Analyst_Asia011
3Data Analyst_Australia011
4Data Analyst_China011
5Data Analyst_Eastern EMEA011
6Data Analyst_India918126205
7Data Analyst_Japan011
8Data Analyst_North America161133191
9Data Analyst_South America135130203
10Data Analyst_Western EMEA252131204
11Logistics Coordinator_Asia011
12Logistics Coordinator_Australia011
13Logistics Coordinator_China011
14Logistics Coordinator_Eastern EMEA011
15Logistics Coordinator_India3963119123
16Logistics Coordinator_Japan011
17Logistics Coordinator_North America011
18Logistics Coordinator_South America011
19Logistics Coordinator_Western EMEA011
20Medical Liaison_Asia2728110
21Medical Liaison_Australia08111
22Medical Liaison_China02771
23Medical Liaison_Eastern EMEA07108
24Medical Liaison_India29272
25Medical Liaison_Japan596526274
26Medical Liaison_North America4422268
27Medical Liaison_South America05106
28Medical Liaison_Western EMEA86267
29Programmer_Asia011
30Programmer_Australia011
31Programmer_China011
32Programmer_Eastern EMEA011
33Programmer_India3989115117
34Programmer_Japan011
35Programmer_North America011
36Programmer_South America011
37Programmer_Western EMEA011
38Project Assistant_Asia1011588
39Project Assistant_Australia521689
40Project Assistant_China011
41Project Assistant_Eastern EMEA511386
42Project Assistant_India601790
43Project Assistant_Japan41911487
44Project Assistant_North America011
45Project Assistant_South America181184
46Project Assistant_Western EMEA971285
47Project Director_Asia011
48Project Director_Australia011
49Project Director_China011
50Project Director_Eastern EMEA011
51Project Director_India011
52Project Director_Japan011
53Project Director_North America54494101
54Project Director_South America011
55Project Director_Western EMEA300892102
56Project Manager_Asia011
57Project Manager_Australia011
58Project Manager_China011
59Project Manager_Eastern EMEA011
60Project Manager_India011
61Project Manager_Japan011
62Project Manager_North America011
63Project Manager_South America011
64Project Manager_Western EMEA755319104
65Senior Project Assistant_Asia011
66Senior Project Assistant_Australia011
67Senior Project Assistant_China011
68Senior Project Assistant_Eastern EMEA011
69Senior Project Assistant_India605207238
70Senior Project Assistant_Japan698208236
71Senior Project Assistant_North America666210240
72Senior Project Assistant_South America125209215
73Senior Project Assistant_Western EMEA335211239
Workings
Cell Formulas
RangeFormula
D2D2=IFERROR(MATCH($A2,CriteriaRange,0),1)
E2:E73E2=IF($D2=1,$D2,SUMPRODUCT(MAX(ROW(CriteriaRange)*($A2=CriteriaRange))))
D3:D73D3=IFERROR(MATCH(A3,CriteriaRange,0),1)
B2:B73B2=SUMIF(Data!$A$3:$A$274,Workings!$A2,Data!$B$3:$B$274)
Named Ranges
NameRefers ToCells
Data!_FilterDatabase=Data!$A$1:$C$613D2:E73
CriteriaRange=Data!$A$1:$A$274D2:E73
FirstInstanceRange=Workings!$D$2:$D$73E2


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
Excel Formula:
=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
Excel Formula:
=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

VBA Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1DataWorking
2ConcatenateTotal HoursTaskConcatenateTotal Hours Per Role
3A0
4B0
50A1C0
6AD1
7BE2
8CF3
96A2
10D1
11E2
12F3
Data
Cell Formulas
RangeFormula
B5,B9B5=SUM(B6:B8)
F3:F8F3=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 :)
 

Forum statistics

Threads
1,143,839
Messages
5,721,096
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top