It's not really the =sumproduct() formula that's wrong--it's your VBA syntax:

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address _
& "=" & Chr(34) & mTimeCriteria & Chr(34) & ")," _
& "--(" & mPositionRange.Address _
& "=" & Chr(34) & mPositionCriteria & Chr(34) & ")," _
& mQuestion1Range.Address & ")"

DogLover wrote:

This is so close to what I need, but I need to have multiple criteria and
cannot seem to find the right syntax. I feel fairly certain once I can
expand for 1 more, I can do the others.

Here is what I'm trying to do, but it suggest I am missing a parenthesis,
and just don't see it. What would the formula be to add an additional
criteria like this?

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address _
& "=" & Chr(34) & mTimeCriteria & Chr(34) & ")," _
--(" & mPositionRange.Address _
& "=" & Chr(34) & mPositionCriteria & Chr(34) &
")," _
& mQuestion1Range.Address & ")"

"DogLover" wrote:

This works! You are a lifesaver. It looks like the key was to add this
With Worksheets ("data") so it would recognize the actual data range.

"Dave Peterson" wrote:

Option Explicit
Sub testme02()
Dim mTimeCriteria As String '????
Dim mPositionCriteria As String
Dim mQuestion1Range As Range
Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mFormula As String
Dim mCount As Long
Dim Res As Variant 'could be an error

mTimeCriteria = "First day of employment (Time 1)"
mPositionCriteria = "Registered Nurse"

With Worksheets("data")
Set mPositionRange = .Range("DataPosition")
Set mTimeRange = .Range("DataTime")
Set mQuestion1Range = .Range("DataQuestion1")

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address _
& "=" & Chr(34) & mTimeCriteria & Chr(34) _
& ")," & mQuestion1Range.Address & ")"

Res = .Evaluate(mFormula)
End With

If IsError(Res) Then
MsgBox "Error in evaluating"
Else
MsgBox Res
End If

End Sub

Notice that I used the with/end with structure -- even with the .evaluate
method. That means that the unqualified addresses (no workbook/worksheet names
included) will refer to that worksheet in the With statement.

If I had used Application.evaluate() or just Evaluate(), then the addresses
would have referred to the activesheet (if the code was in a General module).

I could have used:

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address(external:=true) _
& "=" & Chr(34) & mTimeCriteria & Chr(34) _
& ")," & mQuestion1Range.Address(external:=true & ")"

res = application.evaluate(mFormula)

ps. Notice that those strings have to be surrounded by double quotes--just like
in a formula in a cell:

=SUMPRODUCT(--(a1:a10="First day of employment (Time 1)"),b1:b10)




DogLover wrote:

Just wanting to count multiple criteria and plan to expand this function once
I know it is error free. I think I have some format off or something, please
help I get the message "type mismatch".

mquestion1range has numerical data which should sum if the mTimeCriteria is
found to match.

Dim mTimeCriteria, mPositionCriteria As String
Dim mQuestion1Range, mTimeRange, mPositionRange As Range
Dim mFormula As String
Dim mCount As Long

mTimeCriteria = "First day of employment (Time 1)"
mPositionCriteria = "Registered Nurse"
Set mPositionRange = Worksheets("Data").Range("DataPosition")
Set mTimeRange = Worksheets("Data").Range("DataTime")
Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")J

MsgBox Evaluate("=SUMPRODUCT( --(mTimeRange= " & mTimeCriteria &
")*(mQuestion1Range) )")

--

Dave Peterson
.


--

Dave Peterson