I have a SUMProduct formula that I wrote in VBA. It works fine until the
combination in the criteria is such where there are 0 records, rather than it
returning a 0, it says #Value, but I'm not sure how to deal with it.

Does anyone have an idea??

Here is my code.
Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long

Dim mPositionCriteria As String
Dim mBeginDateCriteria As Variant
Dim mEndDateCriteria As Variant
Dim mTimeCriteria As String
Dim mEntityCriteria As String
Dim mQuestion1Criteria As String

Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mOrientMoYrRange As Range
Dim mEntityRange As Range
Dim mQuestion1Range As Range

Dim mFormula As String
Dim mBegMo As Integer, mBegYr As Integer
Dim mEndMo As Integer, mEndYr As Integer

mPositionCriteria = mPositionC ' This line of Code allows automatic
RECALCULATION
mBeginDateCriteria = mBeginDateC
mEndDateCriteria = mEndDateC
mEntityCriteria = mEntityC

' Needed if Subroutine vs Functio, change to passing variable later
'mPositionCriteria = Worksheets("RFJ").Range("N6")
'mBeginDateCriteria = Worksheets("RFJ").Range("N8")
'mEndDateCriteria = Worksheets("RFJ").Range("N9")

mBegMo = Month(mBeginDateCriteria)
mBegYr = Year(mBeginDateCriteria)


If Month(mEndDateCriteria) = 12 Then
mEndMo = 1
mEndYr = Year(mBeginDateCriteria) + 1
Else
mEndMo = Month(mEndDateCriteria) + 1
mEndYr = Year(mBeginDateCriteria)
End If

' Set Criterias
mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)


If mPositionCriteria = "<" Then
mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
End If

If mEntityCriteria = "<" Then
mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records
Else
mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
End If

mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34)


With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mOrientMoYrRange = .Range("DataOrientMoYr")
Set mEntityRange = .Range("DataEntity")
Set mQuestion1Range = .Range("DataQuestion1")

mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria
& "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mBeginDateCriteria & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mEndDateCriteria & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria &
"),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address &
mQuestion1Criteria & ") )"

'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums
correctly

Kountifs = .Evaluate(mFormula)
If IsNull(Kountifs) Then MsgBox "Zero"

End With

If IsError(Kountifs) Then
MsgBox "Error in evaluating"
Else
MsgBox Kountifs

End If
End Function