Avoiding combining INDIRECT and SUMIFS using FormulaR1C1

Moosles

New Member
Joined
Apr 1, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
I am trying to clean up some old slow code which is causing a bottleneck but I can't seem to figure this part out. The existing code applies a SUMIF formula to data on a tab called 'Lists', with criteria columns in columns A and B of the 'Lists' tab. The sum column is variable, with the column number of the sum range in the 'Lists' tab shown by the calculated value in column I of my current worksheet. The existing approach uses INDIRECT to specify the sum range in each formula, however this (along with the following steps of copy/paste/autofill/calculate) is slowing the code down a lot:

VBA Code:
Range("K5").Formula = "=SUMIFS(INDIRECT(""Lists!C""&$I5,0),Lists!$A:$A,$G5,Lists!$B:$B,K$1)"
Range("K5").Copy
Range("L5:R5").PasteSpecial xlPasteAll
Range("G5:R5").AutoFill Range("G5:R2000")
Cells.Calculate

I think I can do this all in one step using FormulaR1C1 but I can't work out how to reference the value in column I to specify the column number needed for the sum range - Any idea hows to obtain the value XXXXXX in the below?

VBA Code:
Range("K5:R2000").FormulaR1C1 = "=SUMIFS(Lists!C" & XXXXXX & ":C" & XXXXXX & ", Lists!C1:C1,RC7,Lists!C2:C2,R1C)"
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
This should work for what you are trying, although reducing the number of rows in the formula ranges would probably make more of a difference.
An entire column in the formula with only 10k rows of data means 99% of the effort is being wasted.
VBA Code:
Range("K5:R2000").FormulaR1C1 = "=SUMIFS(Lists!C" & Range("I5").Value & ":C" & Range("I5").Value & ", Lists!C1:C1,RC7,Lists!C2:C2,R1C)"

Noting that you are calculating in the last line, I would assume that you have set calculation to manual. Disabling screen updating should also help to reduce the run time of the code.
 

Moosles

New Member
Joined
Apr 1, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
This should work for what you are trying, although reducing the number of rows in the formula ranges would probably make more of a difference.
An entire column in the formula with only 10k rows of data means 99% of the effort is being wasted.
VBA Code:
Range("K5:R2000").FormulaR1C1 = "=SUMIFS(Lists!C" & Range("I5").Value & ":C" & Range("I5").Value & ", Lists!C1:C1,RC7,Lists!C2:C2,R1C)"

Noting that you are calculating in the last line, I would assume that you have set calculation to manual. Disabling screen updating should also help to reduce the run time of the code.
Thanks Jason.

You are correct, I have calculation set to manual, and screen updating was off already. Good point about the use of the whole column too, as the number of rows in the 'Lists' tab varies as part of the macro I'm going to have to find the last row in the 'Lists' tab and use something like this:

VBA Code:
LastRow = Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Row

Range("K5:R2000").FormulaR1C1 = "=SUMIFS(Lists!R1C" & XXXXXX & ":R" & LastRow & "C" & XXXXXX & ", Lists!R1C1:R" & LastRow & "C1,RC7,Lists!R1C2:R" & LastRow & "C2,R1C)"

The issue is that the value in column I is variable for each row, so the proposed solution of referencing Range("I5").value would only return the column number required for the first row. The formula for row 6 needs to refer to I6, the formula for row 7 needs to refer to I7 and so on, the solution above returns I5 for all rows which is incorrect. Ideally I'd like to avoid using INDIRECT but I can't think of another way around it...
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
Sorry, that was an oversight on my part. To do it without indirect would need a loop for the way that you want to do it. The only other option may be to use INDEX instead.
Note that it will need some slight variation depending on the range of columns to be covered, the example below is for columns C:J (R1C3:R?C10).
There is an adjustment factor, RC9-COLUMN(RC3)+1 used in the formula as well. With this method, column C in the example is column 1 in the index range rather than column 3 in the sheet, so this is used to correct the column numbers in I5, etc for that purpose.
VBA Code:
Range("K5:R2000").FormulaR1C1 = "=SUMIFS(INDEX(Lists!R1C3:R" & lastrow & "C10,0,RC9-COLUMN(RC3)+1), Lists!R1C1:R" & LastRow & "C1,RC7,Lists!R1C2:R" & LastRow & "C2,R1C)"

The index and indirect methods would perform equally when the code enters them into the sheet, but subsequent calculations triggered by data changes, etc. should be more efficient.
 
Solution

Moosles

New Member
Joined
Apr 1, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
That's perfect, thank you! I updated it slightly as the number of columns in the 'Lists' tab can vary as well, so I added some terms to find the last row and column number of the 'Lists' tab and incorporated them into the code below. I also replaced the RC9-COLUMN(RC3)+1 with RC9-2 as this performs the same function.

VBA Code:
LastRow = Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Row
LastColumn = Sheets("Lists").Range("XFD1").End(xlToLeft).Column

Range("K5:R2000").FormulaR1C1 = "=SUMIFS(INDEX(Lists!R1C3:R" & LastRow & "C" & LastColumn & ",0,RC9-2), Lists!R1C1:R" & LastRow & "C1,RC7,Lists!R1C2:R" & LastRow & "C2,R1C)"

Thanks for your help, much appreciated!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
I also replaced the RC9-COLUMN(RC3)+1 with RC9-2 as this performs the same function.
That is correct, it is common practice to subtract the first column then add 1 in order to prevent problems if columns are inserted or deleted to the left of the range, but that is not necessary if you can be certain that this will not happen.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,893
Messages
5,638,869
Members
417,057
Latest member
Jimbo Jones

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