Perform Sumif and AverageIf on Dictionary with multiple keys

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top