Forgot to say why it doesn't work normally: When you use an array function in Excel (or with the Evaluate procedure) then Excel understands you are using array functions and so the sub results of the various parameters are arrays themselves. But if you have 
        ' Solution option 3
        s.Cells(3, 3) = .SumProduct((NameValue = s.Cells(3, 1)) * (DateValuea = s.Cells(3, 2)) * ReturnRange)
then then individual parameters are being calculated as a normal value before being passed to the SumProduct function, so the SumProduct function never sees arrays.

You could do it differently by building the arrays and passing these to SumProduct (or Match etc) as described by Andrew Poulson in mrexcel.com/forum/excel-questions/47370-sumproduct-visual-basic-applications.html

Function xxx(x, y)
    Dim WF As WorksheetFunction
    Dim a As Range, b As Range, c As Range
    Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant
    Dim i As Integer
    Set WF = Application.WorksheetFunction
    Set a = Range("A4:A14")
    Set b = Range("B4:B14")
    Set c = Range("C4:C14")
    Arr1 = WF.Transpose(a)
    For i = 1 To UBound(Arr1)
        If Arr1(i) = x Then
            Arr1(i) = 1
        Else
            Arr1(i) = 0
        End If
    Next i
    Arr2 = WF.Transpose(b)
    For i = 1 To UBound(Arr2)
        If Arr2(i) = y Then
            Arr2(i) = 1
        Else
            Arr2(i) = 0
        End If
    Next i
    Arr3 = WF.Transpose(c)
    xxx = WF.SumProduct(Arr1, Arr2, Arr3)
End Function