# Perform Sumif and AverageIf on Dictionary with multiple keys

#### rkaczano

##### Board Regular
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

End Sub

Thanks

### 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
Maybe you could use something like this:

VBA Code:
``````Sub Test()

Dim NewDict As Object

Set NewDict = CreateObject("Scripting.Dictionary")

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
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
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!

Thanks again.