Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Offset(0, 7) <> "" Then
        MsgBox "В этой строке есть наименование"
        Exit Sub
    End If
    With Sheets(2)
        Set r = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
        Set cl = r.Find(what:=Target.Value).Offset(1, 1)
            If Not cl Is Nothing Then
                vr = .Range(cl, cl.End(xlDown)).Value
                Target.Offset(1, 7).Resize(UBound(vr), 1) = vr
                Target.Offset(0, 4).FormulaR1C1 = "=SUM(R[1]C[6]:R[" & UBound(vr) & "]C[6])"
            End If
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Offset(0, 7) <> "" Then
        MsgBox "В этой строке есть наименование"
        Exit Sub
    End If
        If Target.Value <> "" Then
            With Sheets(2)
                Set r = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
                Set cl = r.Find(what:=Target.Value).Offset(1, 1)
                    If Not cl Is Nothing Then
                        vr = .Range(cl, cl.End(xlDown)).Value
                        Target.Offset(1, 7).Resize(UBound(vr), 1) = vr
                        Target.Offset(0, 4).FormulaR1C1 = "=SUM(R[1]C[6]:R[" & UBound(vr) & "]C[6])"
                    End If
            End With
        Else
            If Cells(Rows.Count, 2).End(xlUp).Row <= Target.Row Then
                Set ec = Cells(Rows.Count, 9).End(xlUp)
                Else: Set ec = Target.End(xlDown).Offset(0, 7)
            End If
            Target.Offset(0, 4) = ""
            Range(Target.Offset(0, 7), ec).ClearContents
        End If
End Sub