Sum unique values with multiple criteria

Question

Hello,
I need an equivalent formula to sum unique values based on some criteria. The results I want is in column E. There are 2 criteria(first one on column D and the second one, column B must be ">10".

Now I use this formula: {=SUM(IF(FREQUENCY(IF($A$2:$A$10=D2,IF($B$2:$B$10>10,MATCH($B$2:$B$10,$B$2:$B$10,0))),ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10))}

The problem with this formula, is that it can't be set with FormulaArray inVBA.I understand that I can use evaluate to extract the result but all I want is to have a formula on every cell from column E, that can calculate correct.

Thank you !

Answer 1

I had to try a couple methods before I found one that works, but I got 'er figured out.

Paste these functions into a module:

Public Function SumIfIf(rgeData As Range, matchCriteria As String, numCompCriteria As String) As Double

    Dim c As Range, arr_Distinct() As String, x As Long, totalOut As Double, str_ConcatRgeRow As String
    ReDim arr_Distinct(0)
    
    totalOut = 0
    If InStr("<>=", Left(numCompCriteria, 1)) = 0 Then numCompCriteria = "=" & numCompCriteria

    For Each c In rgeData.Columns(1).Cells
        str_ConcatRgeRow = c.Value & c.Offset(0, rgeData.Columns.Count - 1).Value
        If Not IsInArray(arr_Distinct, str_ConcatRgeRow) Then
            ReDim Preserve arr_Distinct(UBound(arr_Distinct) + 1)
            arr_Distinct(UBound(arr_Distinct)) = str_ConcatRgeRow
            If Evaluate(c.Value = matchCriteria) And Evaluate("=(" & c.Offset(0, rgeData.Columns.Count - 1).Value & numCompCriteria & ")") Then
                totalOut = totalOut + c.Offset(0, rgeData.Columns.Count - 1).Value
            End If
        End If
    Next c
    
    SumIfIf = totalOut

End Function

Function IsInArray(arrToCheck As Variant, valToFind As Variant) As Boolean
    Dim x As Long
    IsInArray = False
    For x = 1 To UBound(arrToCheck)
        If arrToCheck(x) = valToFind Then IsInArray = True
    Next x
End Function

In the case of your example, you'd use it like this:

Usage:

SumIfIf(rgeData As Range, matchCriteria As String, numCompCriteria As String)

rgeData = a range of any number of rows and at least 2 columns

The function matches:

• the left-most column for exact matches to 'matchCriteria', and

• the right-most column for matches to 'numCompCriteria'

...and then:

• returns the sum of numCompCriteria matches

• where [matchCriteria] + [numCompCriteria] is unique.

matchCriteria = a text or numeric identifier, to be matched exactly

numCompCriteria = a numeric identifier specific as a string starting with > or < or = like you would specify simple criteria for the SumIf worksheet function.

• Examples of numCompCriteria: "=10", "<=10", "10"

I'm at my screen-time limit for the day; Let me know if you want further explanation. In the meantime hopefully this solves your issue. :)

+1 to the Question for the challenge, which had the bonus of being just what I needed for something I'm working on too!

---

Update, further to @BOB's question:

The line of code that does the comparison is this one:

If Evaluate(c.Value = matchCriteria) And Evaluate("=(" & c.Offset(0, rgeData.Columns.Count - 1).Value & numCompCriteria & ")") Then

so depending on your needs there are a few ways you could change it.

Quick and dirty, if your new criteria is permanent, replace the above line with:

If Evaluate(c.Value = matchCriteria) _
    And Evaluate("=(" & c.Offset(0, rgeData.Columns.Count - 1).Value & ">10" & ")") _
    And Evaluate("=(" & c.Offset(0, rgeData.Columns.Count - 1).Value & "<=35" & ")") _
    Then

Note that this won't be using the value of numCompCriteria anymore but you'd still have to specify something, or else remove the argument from the function declaration. Or, adjust it add more parameters for your new criteria.

Following that pattern you can add all the criteria you like. The way it's used here, "Evaluate" is returning True or False. You can demonstrate with:

debug.print Evaluate ("=(10>35)")

and in the code above

c.Offset(0, rgeData.Columns.Count - 1).Value

returns the value of the right-most column.