# Finding the highest total value

#### HomeTek

##### New Member
Hi,

I have a spreadsheet in which I want to find out which sport has accumulated the most playing time (example below).

The answer is of course cricket, but I am having trouble with what formula I need to enter into cell J2 to provide this answer, if anyone can help at all please?

I have played around with sumifs etc but to no avail.

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.

#### PCL

##### Well-known Member
Do you know that a Pivot Table could make the deal if you accept to refresh it

#### PCL

##### Well-known Member
Here an UDF to do the job
Somewhere put
=HightestTotalValue(B2:B9,C2:C9)
where
Sport = B2:B9
Time = C2:C9

Code:
``````Option Explicit

Function HightestTotalValue(SportRg As Range, TimeRg As Range) As String
Dim MaxVal As Single
Dim SportDic   As Object
Set SportDic = CreateObject("Scripting.Dictionary")
Dim I As Integer
Dim IVal As Integer
If (SportRg.Rows.Count <> TimeRg.Rows.Count) Then Exit Function
For I = 1 To SportRg.Rows.Count
If (SportDic.exists(SportRg.Cells(I, 1).Value)) Then
SportDic.Item(SportRg.Cells(I, 1).Value) = SportDic.Item(SportRg.Cells(I, 1).Value) + TimeRg.Cells(I, 1).Value
Else
SportDic.Item(SportRg.Cells(I, 1).Value) = TimeRg.Cells(I, 1).Value
End If
Next I
MaxVal = Application.WorksheetFunction.Max(SportDic.Items)
For I = 0 To SportDic.Count
If (Application.WorksheetFunction.Index(SportDic.Items, I + 1) = MaxVal) Then Exit For
Next I
HightestTotalValue = Application.WorksheetFunction.Index(SportDic.keys, I + 1)

End Function``````

Last edited:

#### PCL

##### Well-known Member
Another one smarter

Code:
``````Option Explicit

Function HightestTotalValue(SportRg As Range, TimeRg As Range) As String
Dim MaxVal As Single
Dim SportDic   As Object
Set SportDic = CreateObject("Scripting.Dictionary")
Dim I As Integer
Dim IVal As Integer
If (SportRg.Rows.Count <> TimeRg.Rows.Count) Then Exit Function
For I = 1 To SportRg.Rows.Count
If (SportDic.exists(SportRg.Cells(I, 1).Value)) Then
SportDic.Item(SportRg.Cells(I, 1).Value) = SportDic.Item(SportRg.Cells(I, 1).Value) + TimeRg.Cells(I, 1).Value
Else
SportDic.Item(SportRg.Cells(I, 1).Value) = TimeRg.Cells(I, 1).Value
End If
Next I

MaxVal = Application.WorksheetFunction.Max(SportDic.items)
For I = 0 To SportDic.Count
If (SportDic.items()(I) = MaxVal) Then Exit For
Next I
HightestTotalValue = SportDic.keys()(I)

End Function``````

#### HomeTek

##### New Member
Another one smarter

Code:
``````Option Explicit

Function HightestTotalValue(SportRg As Range, TimeRg As Range) As String
Dim MaxVal As Single
Dim SportDic   As Object
Set SportDic = CreateObject("Scripting.Dictionary")
Dim I As Integer
Dim IVal As Integer
If (SportRg.Rows.Count <> TimeRg.Rows.Count) Then Exit Function
For I = 1 To SportRg.Rows.Count
If (SportDic.exists(SportRg.Cells(I, 1).Value)) Then
SportDic.Item(SportRg.Cells(I, 1).Value) = SportDic.Item(SportRg.Cells(I, 1).Value) + TimeRg.Cells(I, 1).Value
Else
SportDic.Item(SportRg.Cells(I, 1).Value) = TimeRg.Cells(I, 1).Value
End If
Next I

MaxVal = Application.WorksheetFunction.Max(SportDic.items)
For I = 0 To SportDic.Count
If (SportDic.items()(I) = MaxVal) Then Exit For
Next I
HightestTotalValue = SportDic.keys()(I)

End Function``````
Thanks ever so much for this.

I never even thought of using a Pivot table for some reason, but I've created one and it works perfectly for what I need.

To be honest I have no idea what's going on in the UDF you mentioned. Out of curiosity I have tried to do it this way but I don't really think I understand what I need to do with it.

I have placed the code into a VBA module, and I understand it creates a function that I can use, but I'm not too sure what to do with the =HightestTotalValue in the cells itself.

It's not a big deal because you have already helped by pointing me in the direction of using a pivot table, but I do like to learn.

Last edited:

#### PCL

##### Well-known Member
The UDF is used as a function in EXCEL like SUM, COUNTA ....
Put the code in a module
in D3 (for example ) put =HightestTotalValue(B2:B9,C2:C9)
where B2:B9, is the range for Sport
C2:C9) is the range for Time
The returned value will be "C" with your data

#### HomeTek

##### New Member
The UDF is used as a function in EXCEL like SUM, COUNTA ....
Put the code in a module
in D3 (for example ) put =HightestTotalValue(B2:B9,C2:C9)
where B2:B9, is the range for Sport
C2:C9) is the range for Time
The returned value will be "C" with your data
Thank you again, much appreciated. That works perfectly.

I'll spend some time breaking it down and learning how it all works. I've never created custom functions before.