Perform Sumif and AverageIf on Dictionary with multiple keys

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
62
I have a dictionary where I am creating keys that represent multiple keys. I create them with a hashtag. They keys are time based data. Here is an example with 2 years of data over 2 months and three days.

Is it possible to do sumif’s and averageifs on the dictionary for the years or months? I assume I would have to parsing the exciting key/hashtag while looping? But I also assume I would have replace the existing keys with a temporary key (i.e. replace “2020|January|01” with “2000“ or “January”) and then perform the sumif/averageif. But it is not clear to me how you would do this as parsing the key for sumifs and averageifs would create duplicate keys (i.e. I would have multiple keys for January in a given year).

Or should I be simply using a Collection?

Any idea on how to do this?

Sub Test()

Dim NewDict as New Scripting Dictionary

Set NewDict to New Scripting Dictionary

NewDict.Add “2020|January|01”, 350
NewDict.Add “2020|January|02”, 165
NewDict.Add “2020|January|03”, 134

NewDict.Add “2020|February|01”, 350
NewDict.Add “2020|February|02”, 165
NewDict.Add “2020|February|03”, 134

End Sub


Thanks
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,570
Maybe you could use something like this:

VBA Code:
Sub Test()

Dim NewDict As Object

    Set NewDict = CreateObject("Scripting.Dictionary")

    NewDict.Add "2020|January|01", 350
    NewDict.Add "2020|January|02", 165
    NewDict.Add "2020|January|03", 134

    NewDict.Add "2020|February|01", 350
    NewDict.Add "2020|February|02", 165
    NewDict.Add "2020|February|03", 134

    Debug.Print SumIfDict(NewDict, "2020|January|")
    Debug.Print AverageIfDict(NewDict, "2020|February|")
End Sub

Function SumIfDict(d, k)
Dim x As Variant

    For Each x In d
        If x Like "*" & k & "*" Then SumIfDict = SumIfDict + d(x)
    Next x
End Function

Function AverageIfDict(d, k)
Dim x As Variant, c As Long

    For Each x In d
        If x Like "*" & k & "*" Then
            AverageIfDict = AverageIfDict + d(x)
            c = c + 1
        End If
    Next x
    AverageIfDict = AverageIfDict / c
End Function
Your keys are set up so that you can use a partial key to pass to the functions. You could pass "2020|" if you want to sum/average the entire year.
 

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
62
Great. Works well.

Question 1: Is this a recursive function? I notice AverageIf Dict = AverageIfDict + d(x)

Question 2: I adjusted this to include a filter on the dictionary as follows by passing a variable called "u". This allows me to reduce the size of the dictionary to a filtered list within the function. I did this because the keys I have may look more like this:

Asset#1|2020|January|
Asset#2|2020|January|

Thus I still pass "Asset#1|2020|January|" in variable 'k". But to filter on the function I also pass "Asset1" as the variable "u" to filter on the dictionary so that I do not have to loop through the entire results. It seems to filter but I want to make sure I understand how the filter works if this is recursive.


VBA Code:
Function AverageIfDict(d, k, u)
Dim x As Variant, c As Long

    For Each x In Filter(d.Keys, u) 'Was For Each x In d
        If x Like "*" & k & "*" Then
            If IsNull(d(x)) = False Then
                AverageIfDict = AverageIfDict + d(x)
                Debug.Print x & " @" & Format(d(x), "$0.00") & " and " & k & "AverageIf =" & AverageIfDict
                c = c + 1
            Else
                'do nothing
            End If
        Else
          'Do nothing
        End If
    Next x
    
    AverageIfDict = AverageIfDict / c
    Debug.Print AverageIfDict
    
End Function
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,570
Nope, it's not recursive. A recursive routine calls itself, and neither of these do that. It just iterates through the elements in the dictionary. I understand why you ask though, since I'm using the function name within the function. But in VBA you put the value you want to return in the function name. It's typical to do that right before you exit the routine, but you can use that name as a variable within the function if you want, which I did, just to avoid defining another work variable. Although I really should have defined it like this:

Function AverageIfDict(d, k, u) As Double

Without the "As Double" on the end, it defaulted to Variant. Also note that VBA initialized that to 0, so I didn't have to. Even so, some people like to manually initialize their variables before use, partly as a matter of self-documentation, or style, or consistency with other languages.

As far as adding the filter, I think what you did is fine. It's a nice extension of the function. Your debug code should persuade you that it works as you expect.

Good luck!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,731
Messages
5,446,181
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top