I had no problem when I copy-and-pasted the fragment of your macro, with the
following corrections.


Dim mQuestion1Range, mTimeRange, mPositionRange As Range

That works as-is. But I believe the following is better style, and there
will be situations where it can make a difference.

Dim mQuestion1Range as Range, mTimeRange as Range, mPositionRange As Range


Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")J

Remove the "J", an obvious typo.


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

Replace with:

MsgBox Evaluate("=SUMPRODUCT( --(" & mTimeRange.Address & "= """ & _
mTimeCriteria & """), " & mQuestion1Range.Address & ")")

The primary problems were the placements of quotes, the failure to use
..Address where appropriate, and the lack of quotes (in the Evaluate string)
around mTimeCriteria.

I replaced effectively "*(mQuestion1Range.Address)" with
",mQuestion1Range.Address". That is optional an style issue, a personal
choice if the mQuestion1Range.Address range has only numbers and truly
empty cells (i.e. no formula and no constant).


----- original message -----

"DogLover" wrote in message
...
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) )")