If you were writing the formula in a cell, it would look something like:
=SUMPRODUCT((HoursCodes="A")*(HourStat="x")*Period Hours)

Since both the classcode and clistat values are strings, they need to be
surrounded by double quotes.

=chr(34) is a double quote in VBA land.



Option Explicit
Sub testme()

Dim TotalHrs As Double 'don't use Single
Dim ClassCode As String
Dim CLIStat As String
Dim myFormula As String

ClassCode = "A"
CLIStat = "x"

'=SUMPRODUCT((HourCodes="a")*(HourStat="x")*Period Hours)
With Worksheets("Hours")

myFormula = "SUMPRODUCT(" _
& "(HoursCodes=" & Chr(34) & ClassCode & Chr(34) & ")" _
& "*(HourStat=" & Chr(34) & CLIStat & Chr(34) & ")" _
& "*PeriodHours)"

TotalHrs = .Evaluate(myFormula)

End With

MsgBox TotalHrs

End Sub

I find this more difficult to read (most the time!), but you could use it if you
like it better:

myFormula = "SUMPRODUCT(" _
& "(HoursCodes=""" & ClassCode & """)" _
& "*(HourStat=""" & CLIStat & """)" _
& "*PeriodHours)"

======
ps. The reason I used a separate string variable for the formula is so that I
could step through the code, debug.print the formula to the immediate window and
copy|Paste into a cell in excel proper.

Lots of times, I like to get excel's help with my formula typing errors.

pps. There's no reason ever to use Single or Integer. Always use Double and
Long. Modern computers will have less work to do and you don't have to worry
about overflow errors.

Luke wrote:

I've searched through similar topics and tried several variations of code,
but I keep getting a type-mismatch error when trying to use Sumproduct. I am
wanting to use this in several parts of my code. The first instance (which
is also where the error keeps occurring) is below.

With Worksheets("Hours")
TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
(HourStat=CliStat)*PeriodHours)")
End With

TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
long), CliStat as Integer (0, 1, or 99), and the rest as Ranges. I've tried
adjusting the syntax in every way I can think of and based on other examples
I could find, but each time I get the type-mismatch error. Do I just have
something dim'd incorrectly?

Thanks for time and help.

--

Dave Peterson