Search code examples
exceloptimizationsumifs

Adding Unique values and excluding a constant value - Optimizing formulas

I need to add unique values and make sure that I am excluding a constant value (eg 10)

Acct #        Value
9xxx123        50
9xxx123        50
9xxx123        10
9xxx123        15
9xxx234        10
9xxx234        25
9xxx234        25
9xxx234        30

The answer should be: 9xxx123 = 65 and for 9xxx234 = 55

The following formula gives me the correct answer:

=SUMPRODUCT((($A$2:$A$9=E2)*$B$2:$B$9)/(COUNTIFS($A$2:$A$9,E2,$B$2:$B$9,$B$2:$B$9)+($A$2:$A$9<>E2)))

However, due to the large data set that I am analyzing, it takes my computer close to one hour to perform all the calculations. Any suggestions on how to optimize this formula?

Thanks!

Leo

Solution

  • How about a user-defined function?

    Dim cell As Range
Dim mysum As Long, i As Long, j As Long
Dim checkarr As Variant
Function SUMOMITTINGVALUE(accountrange As Range, accountnumber As Range, omitvalue As Long) As Long

    mysum = 0
    j = 0
    ReDim checkarr(0 To 0)

    For Each cell In accountrange
        If cell.Value = accountnumber And _
           cell.Offset(, 1).Value <> omitvalue Then
            For i = 0 To UBound(checkarr)
                If checkarr(i) = cell.Offset(, 1).Value Then
                    Exit For
                ElseIf i = UBound(checkarr) Then
                    mysum = mysum + cell.Offset(, 1).Value
                    checkarr(j) = cell.Offset(, 1).Value
                    j = j + 1
                    ReDim Preserve checkarr(0 To j)
                End If
            Next i
        End If
    Next cell

    SUMOMITTINGVALUE = mysum
End Function

    img1