count responses across a row

[JoeyArr]

I have a simple survey form which I am trying to count 'Yes' responses to a series of 15 questions. I am not able to count across the row of data in the Responses table accurately. My formula looks as follows:

Totalyes:[question1]*-1+[question2]*-1+[question3]*-1 :[question4]*-1+[question5]*-1+[question6]*-1 +[question7]*-1+[question8]*-1+[question9]*-1 +[question10]*-1+[question11]*-1+[question12]*-1 +[question13]*-1+[question14]*-1+[question15]*-1

I'm not getting an accurate count upon submittal of the record.

Many Thanks,
Joe

 

[PHV]

Have a look at the Abs and DSum functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

Public Function basCountOf(varCountof As Variant, ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/12/2006
    'To return the count or a series of values which are equal to an argument (varCountOf)

    'Sample Usage" _
    ? basCountOf("Green", "REd", "Blue", "green", "Green", "yellow", , "GrEeN", "Green") _
      4

    Dim Idx As Integer
    Dim MyVal As Variant

    For Idx = 0 To UBound(varMyVals())

        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If

        If (varMyVals(Idx) = varCountof) Then
            MyVal = MyVal + 1
        End If

NextVal:
    Next Idx

    basCountOf = MyVal

End Function

MichaelRed