Is there a better way to write this Sumifs piece of code?

Question

I have 13 columns in which i use a sumif column from another data sheet. It is pretty simple and straight forward but not quite sure how I can write it more properly.

I have 13 months in my data sheet in columns ranging from N, Q, T (...) AX. Thus, 2 columns in between that I do not need. From row 4 to 7000 I have different customers in no specific order, so I want to make a simple sumIf code.

My code is already working but seems odd and an overkill. Do you guys know how I can write this in a smaller and smarter piece of code using notation like For j = 1 to 13 (...)?

Here is my code:

Range("C4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("N3:N50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("D4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("Q3:Q50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("E4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("T3:T50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("F4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("W3:W50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("G4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("Z3:Z50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("H4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AC3:AC50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("I4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AF3:AF50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("J4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AI3:AI50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("K4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AL3:AL50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("L4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AO3:AO50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("M4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AR3:AR50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("N4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AU3:AU50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
    Range("O4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(data.Range("AX3:AX50000"), data.Range("B3:B50000"), Range("B4").Cells(i, 1), data.Range("J3:J50000"), "Behold", data.Range("D3:D50000"), Range("AB4").Cells(i, 1))

Thank you in advance guys! :)

Matthias Fuglsang-Damgaard · Answer 1 · 2020-07-02T09:28:41.167

What you could do is to isolate the functionality into its own Sub and then parse the data that makes each line different

Sub YourSub()
   DoTheSumIf i, "C", "N"
   DoTheSumIf i, "D", "Q"
   DoTheSumIf i, "E", "T"
   DoTheSumIf i, "F", "W"
   DoTheSumIf i, "G", "Z"
   DoTheSumIf i, "H", "AC"
   DoTheSumIf i, "I", "AF"
   DoTheSumIf i, "J", "AI"
   DoTheSumIf i, "K", "AL"
   DoTheSumIf i, "L", "AO"
   DoTheSumIf i, "M", "AR"
   DoTheSumIf i, "N", "AU"
   DoTheSumIf i, "O", "AX"
End Sub
Sub DoTheSumIf(i As Integer, targetRange As String, dataColumn As String)
    Range(targetRange + "4").Cells(i, 1) = Application.WorksheetFunction.SumIfs(Data.Range(dataColumn + "3:" + dataColumn + "50000"), Data.Range("B3:B50000"), Range("B4").Cells(i, 1), Data.Range("J3:J50000"), "Behold", Data.Range("D3:D50000"), Range("AB4").Cells(i, 1))
End Sub

Why would you call Range and then Cell? "Range("D4").Cells(i, 1)". You could just call "Cell" in the first place?

Hi there, Didn't know this was even possible. Happy to admit that I am new to VBA and coding, but learning as I go along. Thanks for your contribution! — Lelung, Jul 02 '20 at 09:25

Chronocidal · Answer 2 · 2020-07-02T09:23:31.897

1

Let's start from the top: First, Range("C4").Cells(i, 1) is basically just ActiveSheet.Cells(i+3, 3) (Note that if you do not explicitly define the Worksheet, it is implicitly ActiveSheet. You should try to avoid this where possible, just like using Select)

Similarly, code in the form data.Range("N3:N50000") can be written as data.Range(data.Cells(3, 14), data.cells(50000, 14)). This might look longer now, but it will let us cut a lot of code out later.

The last step for iteration 1 is to note your columns are all scaled linearly - they are all consistently 0, 1 or 3 columns apart.

For j=1 To 13
    ActiveSheet.Range(i+3, j+2).Value = Application.WorksheetFunction.SumIfs( _
        data.Range(data.Cells(3, 3*j+11), data.cells(50000, 3*j+11)), _
        data.Range("B3:B50000"), ActiveSheet.Cells(i+3, 2), _
        data.Range("J3:J50000"), "Behold", _
        data.Range("D3:D50000"), ActiveSheet.Cells(i+3, 28))
Next j

However, you can also use a normal Excel formula, and code in the following format, to do this without any loops:

With Range(..)
    .Formula = "..."
    .Calculate
    .Value = .Value
End With

(You may want to use Application.Calculate to disable Automatic Calculation while you do this)

This will require that we know the Minimum and Maximum values of i (I have used min_i and max_i), and the name of the data sheet (I have assumed it is data_sheet)

Application.Calculation = xlCalculationManual
With ActiveSheet.Range(ActiveSheet.Cells(min_i+3, 3), ActiveSheet.Cells(max_i+3, 15))
    .Formula = "=SUMIFS(Index(data_sheet!$3:$3, 3*Column()+11):Index(data_sheet!$50000:$50000, 3*Column()+11), " & _
      "data_sheet!$B$3:$B$50000, $B" & cStr(min_i+3) & ", " & _
      "data_sheet!$J$3:$J$50000, ""Behold"", " & _
      "data_sheet!$D$3:$D$50000, $AB" & cStr(min_i+3) & ")"
    .Calculate
    .Value = .Value
End With
Application.Calculation = xlCalculationAutomatic

edited Jul 02 '20 at 09:23

answered Jul 02 '20 at 08:41

Chronocidal

6,827
1
12
26

Hi there. Thanks a bunch for the guide. Very interesting indeed. I do however still struggle. Tried to copy paste your line of code in your first propososal, but I get a compile error saying "Expected: List separator or ). But it sure looks like it all adds up. Hmm. – Lelung Jul 02 '20 at 09:22
@Lelung Sorry, it was missing a `, _` from the SumIf. Added now – Chronocidal Jul 02 '20 at 09:24
Thanks a bunch! This was really helpful and feel like I've gained a larger understanding of it! :) May I be so bold to ask another question in another part of my code? `For j = 1 To 4 Range("AC4").Cells(i, j) = Range("Z4").Cells(i, 1) / 4 * j Next j` This piece of code only gives me the value in the output as a result of when I've run the code, however, I want it to also show the output but preferably also the formula so the user can see how the value has been created? Any idea of how I implement this? :) – Lelung Jul 02 '20 at 09:47
@Lelung Start with fully qualifying your code: `Range("AC4").Cells(i, j).Value = Range("Z4").Cells(i, 1) / 4 * j`, then you can change the `.Value` (implicit/default in your version) to `.Formula = "=$Z4/4 * " & j` (Also, check whether your code needs brackets - it currently results in `(Z4/4)*j`, not `Z4/(4*j)`) – Chronocidal Jul 02 '20 at 09:55
Aha. Thanks a bunch. Again it is part of a bigger loop running through rows as well. So it is only first iteration that is Z4. Next will be Z5. How do I change the code to shift downward? I tried `$Z & i` without luck. – Lelung Jul 02 '20 at 10:42
@Lelung Either `.Formula"=$Z" & i & "/4 * " & j`, or use FillDown to put everything in at once (e.g. `Range("AC4:AC100").Formula = "=$Z4/4 * " & j`) – Chronocidal Jul 02 '20 at 10:51
Many thanks, however, it still does not work. I cannot use the 2nd option you wrote as this is part of an if formula and other rows between 4 and 100 shouldn't put in this formula. I get an error using `Range("AC4").Cells(i, j).Formula "=$Z" & i & "/4 * " & j` "Object doesnt support this property or method". Run time error 438 – Lelung Jul 02 '20 at 12:45
@Lelung Sorry, there should be another equals there, between `Formula" and `"=$Z"` (so, `.Formula = "=$Z"`) – Chronocidal Jul 02 '20 at 12:48
You're a true legend. Thanks a bunch. Really grateful. Is there any specific guide/place you'd start if you want to learn all this " by heart" ? – Lelung Jul 02 '20 at 12:55
Sorry to be so bold and disturb you one last time. Another part of my If code is this formula: `Range("AC4").Cells(i, j) = -Range("Z4").Cells(i, 1) * (4 - j) / 4`. Ive tried rewrite this as. `Range("AC4").Cells(i, j).Formula = "=-$Z" & i + 3 & "*(4 - j) / 4"`. This gives me an output of #NAME? as direct formula is: =-$Z10*(4 - **j**) / 4. Once again. Thanks a bunch for helping – Lelung Jul 02 '20 at 13:24
@Lelung You need to take the `j` out of the quotes, so that VBA adds the *value* to the String which you use as a formula: `Range("AC4").Cells(i, j).Formula = "=-$Z" & i + 3 & "*(4 - " & j & ") / 4"` – Chronocidal Jul 02 '20 at 13:26
Hello Chonocidal. May I be so bold to ask one last question? I am interested in adding another condition to my sum if formula. I want to add that it should only sum the cells if the value in column I is above 0. Can you please enlighten me? I've tried this without luck: `For j = 1 To 13 ActiveSheet.Cells(i + 3, j + 2).Value = Application.WorksheetFunction.SumIfs( _ data.Range(data.Cells(3, 3 * j + 11), data.Cells(50000, 3 * j + 11)), _ data.Range("B3:B50000"), ActiveSheet.Cells(i + 3, 2), _ data.Range("I3:I50000"), > 0) Next j ` – Lelung Jul 06 '20 at 07:38
@Lelung The `> 0` needs to be in quotes (`, ">0")`), just like if you were using `SUMIFS` in the Worksheet – Chronocidal Jul 06 '20 at 07:59
Thanks a bunch again! Very much appreciated! :) – Lelung Jul 06 '20 at 08:56

Is there a better way to write this Sumifs piece of code?

2 Answers2