Monday, October 10, 2011

Excel VBA: Sum Values IF

Below we have made a function which sums the values of one column/range given that the value of another column is equal to a fixed value. This function ignores the N/A values, which is not the case for the built-in function called SumIf in Excel. If the built-in function finds a N/A value the function returns N/A.

' SUM Value If
' Ignores N/A
Public Function SumValueIf(ByVal objRange As Range, ByVal objCriteria As Range, ByVal objSumRange As Range) As Currency
    Dim intRow As Integer
    Dim objRangeValue As Object
    Dim objCriteriaValue As Object
    Dim objValue As Object
    Dim dblValue As Currency
    Dim dblSum As Currency

    ' Get the criteria value
    objCriteriaValue = objCriteria(1, 1)

    ' Walk through the rows
    For intRow = 1 To objRange.Rows.Count Step 1
        ' Get the current value
        objRangeValue = objRange(intRow, 1)

        ' Compare values
        If (objRangeValue = objCriteriaValue) Then
            ' Get the value
            objValue = objSumRange(intRow, 1)

            ' Is valid number?
            ' Ignore any strings, #N/A, #Error, etc.
            If (IsNumeric(objValue)) Then
                ' Get the value
                dblValue = CCur(objValue)

                ' Sum
                dblSum = dblSum + dblValue
            End If
        End If
    Next

    ' Get the value
    SumValueIf = dblSum
End Function

The function can be used in the following way:

= SumValueIf(A1:A10; "abc"; B1:B10)

where A1:A10 is the compare column. Only sum the value in column B1:B10 if the value in column A is equal to "abc".
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

1 kommentarer:

Anonymous said... at May 6, 2012 at 5:31 AM

The statement to make this function to work returns error messages

= SumValueIf(A1:A10; "abc"; B1:B10)

Post a Comment