Snasui Excel Forum

Code: Select all

Dim rAll As Range, r As Range
Dim rmn As String, rmx As String
With Sheets("Value")
    Set rAll = .Range("b2", .Range("b" & .Rows.Count).End(xlUp))
    For Each r In rAll
        r.Offset(0, 7).Value = VBA.Left(r.Value, InStr(r.Value, ",") - 1)
    Next r
    For Each r In rAll
        rmn = Split(r.Offset(0, 3).Value, "-")(0)
        rmx = Split(r.Offset(0, 3).Value, "-")(1)
        r.Offset(0, 4).Value = Application.SumIfs(rAll.Offset(0, 1), _
            rAll.Offset(0, 7), ">=" & rmn, rAll.Offset(0, 7), "<=" & rmx)
        r.Offset(0, 5).Value = Application.AverageIfs(rAll.Offset(0, -1), _
            rAll.Offset(0, 7), ">=" & rmn, rAll.Offset(0, 7), "<=" & rmx)
        If IsError(r.Offset(0, 5).Value) Then r.Offset(0, 5).Value = 0
        r.Offset(0, 6).Value = Application.CountIfs( _
            rAll.Offset(0, 7), ">=" & rmn, rAll.Offset(0, 7), "<=" & rmx)
    Next r
    rAll.Offset(0, 7).Resize(, 2).ClearContents
End With