I had this working and I have no idea what I did or what happened but now it will not work. When I drop the formula back into the Spreadsheet it works fine... So, it has to be some way in which I am calling the function improperly. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValueRng As Range
Dim ValueRng2 As Range

Set ValueRng = Range("C2")
Set ValueRng2 = Range("K2")


If ValueRng > 0 Then
With Range("B7:F8")
.Formula = "=IF(B7="""","""",SUMPRODUCT((Schedule!$J$2:$M$1300=B7)*(Schedule!$H$2:$H$1300=Sheet1!$C$2))&"" - Visits"")"
.Value = .Value
End With
End If

If ValueRng > 0 Then
With Range("G8:H8")
.Formula = "=IF(G7="""","""",IF(SUMPRODUCT((Schedule!$J$2:$M$1300=Sheet1!G7)*(Schedule!$H$2:$H$1300=Sheet1!$C$2))=0,"""",SUMPRODUCT((Schedule!$J$2:$M$1300=Sheet1!G7)*(Schedule!$H$2:$H$1300=Sheet1!$C$2))&"" - Visits""))"
.Value = .Value
End With
End If

If ValueRng2 > 0 Then
With Range("J8:N8")
.Formula = "=IF(J7="""","""",SUMPRODUCT((Schedule!$J$2:$M$1300=J7)*(Schedule!$I$2:$I$1300=Sheet1!$K$2))&"" - Visits"")"
.Value = .Value
End With
End If

If ValueRng2 > 0 Then
With Range("O8:P8")
.Formula = "=IF(O7="""","""",IF(SUMPRODUCT((Schedule!$J$2:$M$1300=Sheet1!O7)*(Schedule!$I$2:$I$1300=Sheet1!$K$2))=0,"""",SUMPRODUCT((Schedule!$J$2:$M$1300=Sheet1!O7)*(Schedule!$I$2:$I$1300=Sheet1!$K$2))&"" - Visits""))"
.Value = .Value
End With
End If



End Sub