Can someone help me with this sumif formula

[QuietMan]

MXT is the range that contains the sunif criteria. it dosent like the offset
I commented out the R1C1 formula below so it might be easier for you to follow

Thanks

Sub Sumif_Test()
MXT = Range("MXT")
For X = 20 To 30
For Y = 1 To 3
Cells(X, Y).Value = Application.SumIf(MXT, Cells(18, Y), Offset(MXT,
Cells(X, 4), MXT.Rows.Count, 1))
Next Y
Next X

' This is thr R1C! equivalent of the formula I'm trying to create.
'ActiveCell.FormulaR1C1 =
"=SUMIF(MXT,R[-13]C4,OFFSET(MXT,0,R[-15]C,ROWS(MXT),1))"
End Sub

 

[Bernie Deitrick]

When you have a working formula, just use the formula:

Sub Sumif_Test2()
Dim MXT As Range
Set MXT = Range("MXT")

With Range(Cells(20, 1), Cells(30, 3))
.FormulaR1C1 = "=SUMIF(MXT,R[-13]C4,OFFSET(MXT,0,R[-15]C,ROWS(MXT),1))"
.Value = .Value
End With

End Sub

HTH,
Bernie
MS Excel MVP

 

[QuietMan]

Thanks
--
Helping Is always a good thing

When you have a working formula, just use the formula:

Sub Sumif_Test2()
Dim MXT As Range
Set MXT = Range("MXT")

With Range(Cells(20, 1), Cells(30, 3))
.FormulaR1C1 = "=SUMIF(MXT,R[-13]C4,OFFSET(MXT,0,R[-15]C,ROWS(MXT),1))"
.Value = .Value
End With

End Sub

HTH,
Bernie
MS Excel MVP

MXT is the range that contains the sunif criteria. it dosent like the offset
I commented out the R1C1 formula below so it might be easier for you to follow

Thanks

Sub Sumif_Test()
MXT = Range("MXT")
For X = 20 To 30
For Y = 1 To 3
Cells(X, Y).Value = Application.SumIf(MXT, Cells(18, Y), Offset(MXT,
Cells(X, 4), MXT.Rows.Count, 1))
Next Y
Next X

' This is thr R1C! equivalent of the formula I'm trying to create.
'ActiveCell.FormulaR1C1 =
"=SUMIF(MXT,R[-13]C4,OFFSET(MXT,0,R[-15]C,ROWS(MXT),1))"
End Sub