Most simply,
Replace yoursheetname with the actual name of your WorkSheet.Rich (BB code):mySum = WorksheetFunction.SumIf(Sheets("yoursheetname").Range("B5:B20"), "<8000", Sheets("yoursheetname").Range("F5:F20"))
Sub sumiftest3()
Cells(1, 1).Value = "8900"
Dim TopEnd As Variant
TopEnd = Application.Match(Cells(1, 1).Value, Worksheets("ABCDaily").Columns(2), 0)
Cells(Topend+2,5).value = Application.sumif(.Range(Cells(2,5),cells(Topend,5)),"8900",.Range(cells(5,5),cells(Topend,5))
End Sub
Sub sumiftest3()
Dim TopEnd As Variant
With Worksheets("ABCDaily")
.Cells(1, 1).Value = "8900"
TopEnd = Application.Match(.Cells(1, 1).Value, .Columns(2), 0)
.Cells(TopEnd + 2, 5).Value = Application.SumIf(.Range(.Cells(2, 5), .Cells(TopEnd, 5)), "8900", .Range(.Cells(5, 5), .Cells(TopEnd, 5)))
End With
End Sub
ActiveSheet is the default if you don't specify any specific sheet, but I rarely trust that because sometimes the activesheet is not what you think unless the sub is being called from a button on that sheet.
I cannot test the following code, however, I believe this should work.
Code:Sub sumiftest3() Dim TopEnd As Variant With Worksheets("ABCDaily") .Cells(1, 1).Value = "8900" TopEnd = Application.Match(.Cells(1, 1).Value, .Columns(2), 0) .Cells(TopEnd + 2, 5).Value = Application.SumIf(.Range(.Cells(2, 5), .Cells(TopEnd, 5)), "8900", .Range(.Cells(5, 5), .Cells(TopEnd, 5))) End With End Sub
If you are still getting 0 value after these changes, the next step would be to verify all of your cell ranges are evaluating to the expected lookup/return ranges you want.
Also, can you help me to change the abover criteria to be >=8000? I think this will make it easier for me to see what is going on.
This actually I think explains the problem you are having... currently you are filtering the sumif to only sum where values are 8900 exactly
you should change the "8900" to "<=8900" or whatever range you are looking for.
Sub sumiftest4()
Dim TopEnd As Variant
With Worksheets("ABCDaily")
.Cells(1, 1).Value = "8000"
TopEnd = Application.Match(.Cells(1, 1).Value, .Columns(2), 0)
.Cells(TopEnd + 2, 5).Value = Application.SumIf(.Range(.Cells(2, 5), .Cells(TopEnd, 5)), ">=8000", .Range(.Cells(5, 5), .Cells(TopEnd, 5)))
End With
End Sub