Finding the highest total value

HomeTek

New Member
Joined
Jan 27, 2017
Messages
32
Office Version
365
Platform
Windows
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
 

Some videos you may like

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
Joined
Jul 15, 2008
Messages
1,348
Do you know that a Pivot Table could make the deal if you accept to refresh it
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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
Joined
Jul 15, 2008
Messages
1,348
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
Joined
Jan 27, 2017
Messages
32
Office Version
365
Platform
Windows
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
Joined
Jul 15, 2008
Messages
1,348
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

New Member
Joined
Jan 27, 2017
Messages
32
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,407
Messages
5,414,223
Members
403,520
Latest member
Pineappleman

This Week's Hot Topics

Top