Reputation: 700
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 !
Upvotes: 1
Views: 2833
Reputation: 21639
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 columnsThe 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
matcheswhere
[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 theSumIf
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.
Upvotes: 3