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


--
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


"QuietMan" <(E-Mail Removed)> wrote in message
news:0A6C4204-4059-4CF3-A142-(E-Mail Removed)...
> 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
>
>
> --
> Helping Is always a good thing

Thanks
--
Helping Is always a good thing


"Bernie Deitrick" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0A6C4204-4059-4CF3-A142-(E-Mail Removed)...
> > 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
> >
> >
> > --
> > Helping Is always a good thing
>
>
>