I am sorry for being unclear. In the line:

Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" &
_ var1 & """)*(B1:B10=k1))")

The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable
should reference the cell address Worksheets("Sheet1").Range("J1").

I want to be able to name the cell address as variables in my sumproduct
formula. I then want to use the variable name and the forumla function as if
I had entered the cell address.

--
Thanks
Shawn


"Bob Phillips" wrote:

I put J1 in A1, A in B1 and A in K1, and I got 1.

Am I mis-reading what you are trying to do with those values?

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
Private Sub CommandButton1_Click()

Dim Ans1 As Range
Dim var1 As String
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
Set Ans1 = WS.Range("h2")
var1 = "J1"

Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" &
_
var1 & """)*(B1:B10=k1))")

End Sub


--
HTH

Bob Phillips

"Shawn" wrote in message
...
I apologize in advance for re-posting this question. However, I haven't
got
an answer to it yet and thought a fresh string might catch some "new"
eyes.
I can't get the formula to calculate correctly. If I just put J1 in the
formula, it does fine. However, I don't want to put J1, I want to put
var1.
This is vital to some significant programming I plan on doing, but have
to
make it work on the small scale first. Please help!

Private Sub CommandButton1_Click()

Dim Ans1 As Range
Dim var1 As String
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
Set Ans1 = WS.Range("h2")
var1 = "J1"

Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" &
var1 & "")*(B1:B10=k1))")

End Sub


--
Thanks
Shawn