Sumproduct gives #Value! when answer=0

D

DogLover

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
 
D

Dave Peterson

=sumproduct() won't work that way.

I'd bet that you have an error somewhere in one of your fields in the worksheet.
 
D

DogLover

On the worksheet, the =sumproduct evaluates to 0 which is correct in the case
where certain criterias are selected.

I have the function built with my VBA code and it evaluates the exact same
criteria, but returns #Value!.

Not sure where I would have an error that would cause something like this.
Can you tell me will the VBA code return a 0 if you choose a combination of
multiple criteria where the count =0?
 
D

Dave Peterson

If the data is ok (no errors), then evaluating the =sumproduct() formula will
give you a number.

Maybe you should add a line after you finish creating the mFormula string.

Debug.print mFormula

This will cause your formula to be printed in the immediate window of the VBE.
Hit ctrl-g if you don't see it.

Then look at the ranges on that data worksheet that are used in the formula. I
still bet that there's something wrong there.

If that doesn't help, post the formula you see in your immediate window. Maybe
it'll help...
 
D

DogLover

I think there is more than one problem. I found one where field was numeric
so I had to change the code to represent the correct syntax. Now, I have my
hads full.
I have 2 lines of code, each for string data.

Specially, when I add either of these fields into my long mFormula, I get
an error 13, Data Type error. One thing I've noted, is both strings have -
hyphens in it. Are there any rules on what can be in string data for passing.

' mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & "),"
' mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & "),"


Also, it seems that when I pull these out of the long formula and evaluate
each with this shorter code, I am not seeing the error.
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & "),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria
& ") )"



This is the entire formula:
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria
& "),"
mFormula = mFormula & "--(" & mDeptNoRange.Address & mDeptNoCriteria &
"), "
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mBeginDateCriteria & "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address &
mEndDateCriteria & "),"
mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "),
"
mFormula = mFormula & "-- (" & mQuestion1Range.Address &
mQuestion1Criteria & ") )"
 
D

Dave Peterson

I would create a simpler formula. Then I would share what that formula string
looks like.

I don't have a guess right now.
 
D

DogLover

The problem appears to be the LEN of the mFormula. Have you ever had the
string being evaluated longer than 255 characters. This appears to be the
issue. I am not sure how to change my formula if I have 7/8 criteria to
evaluate as T/F to count.
 
D

Dave Peterson

If you're close to the 255 limit, you may be able to use:

mTimeRange.Address(0,0)
instead of
mTimeRange.Address
(for all your variables)

the .address(0,0) will not include those $ signs in the address.

If that doesn't help, take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

It's a technique to workaround that .formulaarray length limit when populating a
cell in a worksheet. I haven't tested using .evaluate(), but you can.

The last suggestion would be to loop through the range and do the count
yourself.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help. Sumproduct Data Type Mismatch Erro 3
Sumproduct formula works in spreadsheet, NOT VBA. Help please 3

Top