Snasui Excel Forum

Code: Select all

Sub text()
    Dim WF As WorksheetFunction
    Dim s1 As String, dateMain As Single, i As Integer
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set WF = Application.WorksheetFunction
    Set Ws1 = Sheets(1)
    Set Ws2 = Sheets(2)
    s1 = Ws2.Range("a2")
    dateMain = Ws2.Range("b2")
    i = WF.SumProduct((Ws1.Range("a:a") = s1), (Ws1.Range("b:b") > dateMain))
    MsgBox i
End Sub

Code: Select all

i = Evaluate("=Sumproduct(--(" & Ws1.Range("a:a").Address _
        & "=""" & s1 & """),--(" & Ws1.Range("b:b").Address & "=" & dateMain & "))")

Code: Select all

i = Evaluate("=Sumproduct(--(Sheet1!" & Ws1.Range("a:a").Address _
        & "=""" & s1 & """),--(Sheet1!" & Ws1.Range("b:b").Address & "=" & dateMain & "))")