Can someone help me with this sumif formula

Q

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
 
B

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
 
Q

QuietMan

Thanks
--
Helping Is always a good thing


Bernie Deitrick said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top