This works, but I cannot see where you set mMonth

Public Sub DoStats()
Dim mStyle As String
Dim mQtr As String
Dim mFormula As String
Dim mCount As Long
Dim mMonth As Long
Dim planDataSheet As Worksheet
Dim filtersSheet As Worksheet
Set filtersSheet = Sheets("Filters")
Set planDataSheet = Sheets("PlanData")

mStyle = "SFD"
mQtr = "2009/4"
mFormula = "SUMPRODUCT(('" & planDataSheet.Name & "'!K2:K18646=""" &
mStyle & """)*" & _
"('" & planDataSheet.Name & "'!AT2:AT18646=" & mMonth & "))"
mCount = Application.Evaluate(mFormula)
MsgBox mCount
End Sub

HTH

Bob

"RussellT" wrote in message
...
Bob, found your discussion on sumproduct at xldyamic.com and created the
following code for my formula. I substituted actual ranges as opposed to
calcing endrows, but I still get aType Mismatch error. thanks

Public Sub DoStats()
Dim mStyle As String
Dim mQtr As String
Dim mFormula As String
Dim mCount As Long
Dim planDataSheet As Worksheet
Dim filtersSheet As Worksheet
Set filtersSheet = Sheets("Filters")
Set planDataSheet = Sheets("PlanData")

mStyle = "SFD"
mQtr = "2009/4"
mFormula = "SUMPRODUCT(plandataSheet.Range(K2:K18646=""" & mStyle &
""")*(plandataSheet(AT2:AT18646=""" & mMonth & """))"
mCount = Application.Evaluate(mFormula)
MsgBox mCount
End Sub

"Bob Phillips" wrote:

Try

StatsForm.TextBox9 = Application.Evaluate("SUMPRODUCT(" & _
"(" & planDataSheet.Range("K2:K" & Endrow).Address(,
, ,
True) & "=""""SFD"")*" & _
"(" & planDataSheet.Range("AT2:AT" &
Endrow).Address(, ,
, True) & "=Quarter)*" & _
"(" & planDataSheet.Range("V2:V" & Endrow).Address(,
, ,
True) & ")")

HTH

Bob


"RussellT" wrote in message
...
I want to sum number in Column V if two conditions are met.
Condition 1: Data in Column K = SFD
Condition 2: Data in Column AT = value in cell H2 on another Sheet.

Dim Quarter As String
Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4

I've tried the formula but get error message "Invalid number of
arguments"
Thanks for the assistance.

StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" &
Endrow),
"SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter,
planDataSheet.Range("V2:V" & Endrow))

I've also tried replacing the And joiner with * to no avail.


.