SUMIF returning zero

Question

Here are snippets of two worksheets

This is the code that I have on the first sheet meant for change in selection of the dropdown. There will be more Cases, but my issue is with the SUMIF. Anytime column B on Sheet2 matches the corresponding column A item on Sheet2, sum column C on Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D1")) Is Nothing Then
    Select Case Range("D1")
        Case "2014-2015": Cells(2, "B") = WorksheetFunction.SumIf(Worksheets("2014-2015").Range("B2:B22"), A2, Worksheets("2014-2015").Range("C2:C22"))
        Case Else: Cells(2, "B") = 8
  End Select
End If
End Sub

The problem is that it is always returning 0. Hoping for help as to an edit to the code to make it display the proper total.

Thanks,

Gilligan Gilligan · Accepted Answer · 2015-07-30T17:02:21

It looks like the problem is with the value it is trying to match, specified only as A2 in your Sumif formula. It isn't recognizing that you want to match the value in cell A2. It would need to be referenced with something similar to Worksheets("SummarySheet").Range("A2").Value like you did for the other parameters of that function.

SUMIF returning zero

1 Answers