Possibly a more helpful suggestion ...

1. In ...

LCase(Trim(TheYear)) = "ALL"
... since you lower case TheYear, it will NEVER, EVER, EVER = "ALL"


2. In ...

TheYear = Range ("A1")
... it should be E1


3. Brute force, and not very elegant, but easiest to understand

Option Explicit


Private Sub GetTotal()
    Dim iRow As Long
    Dim Total As Double
    Dim TheYear As Variant, TheMonth As Variant
    Dim Theitem As String
   
    
    With ActiveSheet
         ' get your values from the linked cells.
         TheYear = .Range("E1").Value
         TheMonth = .Range("F1").Value
         Theitem = .Range("G1").Value
        
         For iRow = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
            If .Cells(iRow, 1).Value = TheYear Or LCase(Trim(TheYear)) = "all" Then
                If .Cells(iRow, 2).Value = TheMonth Or LCase(Trim(TheMonth)) = "all" Then
                    If .Cells(iRow, 3).Value = Theitem Or LCase(Trim(Theitem)) = "all" Then
                        Total = Total + .Cells(iRow, 4).Value
                    End If
                End If
            End If
        Next iRow
    End With
    
    MsgBox Total
End Sub