Finding the highest total value

[HomeTek]

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

 

[PCL]

Do you know that a Pivot Table could make the deal if you accept to refresh it

 

[PCL]

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

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

 

[PCL]

Another one smarter

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]

Another one smarter

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.

 

[PCL]

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
like in your display
The returned value will be "C" with your data

 

[HomeTek]

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
like in your display
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.