CODEWITHSUNDEEP
excelvbaperformance
Ramon
Ramon

Reputation: 35

Faster way of adding sumifs formulas into large range

I am wondering if there is a faster way of adding (and calculating) a number of formulas into a larger range.

So the range counts 22 columns with 14235 rows each. Every column has its own formula most of them are sumifs with 5 criterias.

My approach is actually very simple, but probably not the fastest. It's just selecting the range of each column and insert the formula. 

Sheet1.Range("G4:G14238").FormulaR1C1 = "=SUMIFS(sumRangeA,criteria_range1,RC[-6],criteria_range2,RC[-5],criteria_range3,RC[-4],criteria_range4,RC[-3],criteria_range5,RC[-2])"
Sheet1.Range("H4:H14238").FormulaR1C1 = "=SUMIFS(sumRangeB,criteria_range1,RC[-7],criteria_range2,RC[-6],criteria_range3,RC[-5],criteria_range4,RC[-4],criteria_range5,RC[-3])"
...and so on

This actually takes a while, I believe because excel is inserting and calculating everything after having inserted.

So I tried deactivating the auto calculation before inserting the formulas and let excel calculate everything once every formulas were filled, but that even took longer (nearly twice as long). Not sure if I used the code correctly, though

Application.Calculation = xlCalculateManual

Sheet1.Range("G4:G14238").FormulaR1C1 = "=SUMIFS(sumRangeA,criteria_range1,RC[-6],criteria_range2,RC[-5],criteria_range3,RC[-4],criteria_range4,RC[-3],criteria_range5,RC[-2])"
Sheet1.Range("H4:H14238").FormulaR1C1 = "=SUMIFS(sumRangeB,criteria_range1,RC[-7],criteria_range2,RC[-6],criteria_range3,RC[-5],criteria_range4,RC[-4],criteria_range5,RC[-3])"

Application.Calculation = xlAutomatic

To give you a better understanding what I try to achieve: Until now I have these formulas in a regular excel table (no vba at all). It is working, but very slow. If I filter for one condition it takes up to two minutes to show me the results. Since I use this table daily it's very time consuming and rather inefficient.

So I thought creating something with vba might help to make it faster. My idea was to insert the formulas, let it calculate and then paste everything as values. That should allow me to work faster with the file. But as my approach takes very much time too, it's not the perfect solution until now.

Are you guys able to help me with that? I am happy with any other approach that might help fasten up the work flow!

Thanks in advance!

Best,

Ramon

Upvotes: 1

Views: 1254

Answers (1)

Jeremy
Jeremy

Reputation: 1337

A nice simple loop would do it:

Sub SumIf()
Dim ASCII As Long
Dim iRef As Long
Application.Calculation = xlCalculationManual

iRef = 6
For ASCII = 71 To 93
    Sheet1.Range(Chr(ASCII) & "4:" & Chr(ASCII) & "14238").FormulaR1C1 = "=SUMIFS(sumRange,criteria_range1,RC[-" & iRef & "],criteria_range2,RC[-" & (iRef - 1) & "],criteria_range3,RC[-" & (iRef - 2) & "],criteria_range4,RC[-" & (iRef - 3) & "],criteria_range5,RC[-" & (iRef - 4) & "])"
    iRef = iRef  + 1
Loop

Application.Calculation = xlAutomatic

End Sub

Upvotes: 0

Related Questions