Sub Test()
    Dim LR As Long
    Dim Total As Variant

    With Worksheets("Sheet1")
         LR = .Cells(.Rows.Count, "A").End(xlUp).Row
         Total = Application.WorksheetFunction.SumIf(.Range("A2:A" & LR), "<>Test", .Range("B2:B" & LR))
    End With
End Sub

vntArr = objRng

'*******************************************************************************
'Purpose:   Sums up a range of values excluding the values of cells where
'           another cell in the same row contains a specified value.
'*******************************************************************************
Sub SumifArray()

  Const cstrName As String = "Sheet1" 'Name of the worksheet to be processed
  Const cLngFirstRow As Long = 2 'First row of data (excluding headers)
  Const cStrSumColumn As String = "B" 'The column to sum up
  Const cStrCheckColumn As String = "A" 'The column where to check against
  Const cStrCheckString As String = "Test" 'The value to be checked against

  Dim objRng As Range 'The range of data (both columns)
  Dim vntArr As Variant 'The array where the range is to be pasted into
  Dim lngLastRowCheck As Long 'Calculated last row of data in the "check" column
  Dim lngLastRowSum As Long 'Calculated last row of data in the "sum" column
  Dim lngArrCounter As Long 'Array row counter
  Dim lngSum As Long 'Value accumulator

  With Worksheets(cstrName)
    ' Last used row in column cStrCheckColumn
    lngLastRowCheck = .Columns(cStrCheckColumn).Find(What:="*", _
        After:=.Cells(1, cStrCheckColumn), LookIn:=xlFormulas, _
        Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ' Last used row in column cStrSumColumn
    lngLastRowSum = .Columns(cStrSumColumn).Find(What:="*", _
        After:=.Cells(1, cStrSumColumn), LookIn:=xlFormulas, _
        Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  End With

  ' Calculate the range of data
  Set objRng = Range(Cells(2, cStrCheckColumn), _
      Cells(lngLastRowCheck, cStrSumColumn))
  ' Paste the range of data into an array (One-based, two-dimensional)
  vntArr = objRng
  ' Release object variable: the data is in the array
  Set objRng = Nothing

  ' Loop through the array
  For lngArrCounter = LBound(vntArr) To UBound(vntArr)
    ' Check if the value in the "check" column isn't equal to cStrCheckString
    If vntArr(lngArrCounter, 1) <> cStrCheckString Then _
        lngSum = lngSum + vntArr(lngArrCounter, 2)
  Next

  ' Write the result into the first empty row after the last row of data in
  ' the "sum" column
  Worksheets(cstrName).Cells(lngLastRowSum + 1, cStrSumColumn) = lngSum

End Sub

Sub test()
'i suppose the name are on the A coloumn and the value are in b coloumn
Dim rows As Integer
Dim sum As Double

sum = 0

'count how many rows
rows = ActiveSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count

For i = 2 To rows

    'sum the value
    sum = sum + Cells(i, 2) 'column B value

Next i

'subtract the item focused
sum = sum - Cells(ActiveCell.Row, 2)

'write sum in the last row (column B)
Cells(rows + 1, 2) = sum

End Sub