SUMIF with variable range

Question

I want to make this simple code but I didn't find any solutions to this.

I have a loop to iterate all rows of my sheet, and for every iteration I want to make a sumif, but the range is variable.

so I want something like this:

Dim row,MaxColumn as Integer
MaxColumn = 5
For row= 1 to 5
ActiveSheet.Cells(row, MaxColumn + 1).Select
ActiveCell.Formula = "=SUMIF("B2:D(here I want a variable D1 to 5 range with row variable),""=1"")"

next row

I don't know how to finish this.

I know I can use this to select a range with a variable, but I cannot use it on SUMIF.

ActiveSheet.Range("B" & row & ":D" & row)

what is the point of changing formula of _same_ cell 5 times? If you change active cell somewhere else, show us more code.. — Dmitry Pavliv, May 08 '14 at 12:25
**1)** [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) **2)** in line `Dim row,MaxColumn as Integer` variable `row` has type `Variant`. Change it to `Dim row as Integer,MaxColumn as Integer` **3)** you can apply formula without loop for entire range: `Range(A1:A5).Formula="=SUMIF(B1:D1,1)"` and excel would automatically adjust it for each cell/row — Dmitry Pavliv, May 08 '14 at 12:51

Ripster · Accepted Answer · 2014-05-08T13:06:56.360

You can get the address of a range like this Range("B2:D2").Address
This will produce an absolute range $B$2:$D$2

You can give pass it True/False to get relative addresses Range("B2:D2").Address(False, False)
Which produces the range B2:D2

Here is an example:

Dim TotalRows As Long
Dim i As Long

' Get the total number of rows in the current sheet
TotalRows = ActiveSheet.UsedRange.Rows.Count

' Iterate through all rows
For i = 1 To TotalRows
    ' Set the current cells formula range to Bi:Di where i = the current row
    Range("A" & i).Formula = "=SUMIF(" & Range("B" & i & ":D" & i).Address(False, False) & ", 1)"
Next

Alternatively you can set the formula of a range like this:
Range("A1:A4").Formula = "=SUMIF(B1:D1,1)"

Excel will automatically increment the range based on the row for you.

SUMIF Example

SUMIF with variable range

1 Answers1