Avoiding combining INDIRECT and SUMIFS using FormulaR1C1

[Moosles]

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:

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?

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

 

[jasonb75]

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.

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]

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.

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:

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]

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.

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.

 

[Moosles]

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.

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]

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.