Finding the highest total value

HomeTek

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

bWi8gu.jpg



Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Do you know that a Pivot Table could make the deal if you accept to refresh it
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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