I figured out the answer. Here is an example:

Private Sub CommandButton1_Click()

Dim OutReach As Range
Dim WS As Worksheet
Dim AgeRange As String
Dim Red As Range

Set WS = Worksheets("Sheet1")
Set OutReach = WS.Range("A15")
Set Red = WS.Range("I1")
AgeRange = "B1:B10"


OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange &
"1)*(D1:F10=""" & Red.Value & """))")

WS.Range("A16").Value = Red.Value

End Sub


--
Thanks
Shawn


"Shawn" wrote:

I have been posting similar questions the last few days and thought I had all
my answers. Turns out, I don't. Here is my code:

Private Sub CommandButton1_Click()

Dim OutReach As Range
Dim WS As Worksheet
Dim AgeRange As String

Set WS = Worksheets("Sheet1")
Set OutReach = WS.Range("A15")
AgeRange = "B1:B10"


OutReach.Value = WS.Evaluate("=SUMPRODUCT((" & AgeRange & "
1)*(D1:F10=I1))")

End Sub


This code works great and does exactly what that I want it to. However, in
the formula line, instead of D1:F10 = I1, I want D1:F10 =
Worksheets("Sheet2").Range("A1").Value

This gives and error.

I basically want this formlua to look to Sheet2???


--
Thanks
Shawn