Go Back

Sum race time splits

Read time: 20 minutes

In this tutorial, you will find out how to sum race time splits for multiple competitors using the SUM function with time values. Within our table, we will have several competitors with their time results from a few races. In the last column, we will calculate the sum of all times for every competitor. Prerequisites for this formula is familiarity with SUM function as well as the correct formatting of cells data types.

Sum race time splits

Formula

First, we will briefly explain the SUM function. This function sums cells’ values from a given range. The syntax is:

=SUM(range)

Example

The range is a range of cells which we want to sum. For example, if we write the formula like this:

=SUM(A3:B5)

It will sum all numeric values from cells range A3:B5.

Now we can look at our example. The following table has different competitors in column B, and several races in columns C to H. In column I, we want to sum total race times per competitors. The formula for the first row of the table looks like this:

=SUM(C3:H3)

 

Figure 1. Sum race time splits

To be able to sum time values, you need to set a correct format of all fields in this table. We formatted all cells as time values (HH:MM:SS). Doing like this, we enable the SUM function to summarize all the values from the range.

Excel internally converts all time values into decimal, so the values can be summed. For example, if we have time 2:57:46 in cell I3, Excel will convert it to 0.123449074 while calculating. Hours are calculated as 2/24, minutes as 57/(24*60) and seconds as 46/(24*60*60). The final decimal number will be the product of these three: 0.12344907, which is similar to 2 hours, 57 minutes and 46 seconds (2:57:46).

If we look once more at our example, in cell I3 we got the sum of all times from cells C3 to H3, which is 2:57:46.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

When I try to use this formula to write to cells it has a run time error. I tried using Formula instead of Array and it doesn't work either. Sub Fill_Formulas() Application.ScreenUpdating = False Application.Calculation = xlCalculateManual Range("Table7['# to make the draw]").FormulaArray = "=IF([@[Draw '#]]>0, [@[Draw '#]],IF(OR([@[Race Entered]]=EventtoDraw, EventtoDraw=""), MAX(IF([Race Entered]=[@[Race Entered]],[Draw '#]))+SUMPRODUCT(([Race Entered]=[@[Race Entered]])*(['# for Drawing]<[@['# for Drawing]]))+COUNTIFS(H$11:H11,H11, C$11:C11,C11), ""))" Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub
Solved by D. J. in 12 mins
When I try to use this formula to write to cells it executes the first 2 but has a run time error on the 3rd. I tried using Formula instead of Array and it doesn't work either. Sub Fill_Formulas() Application.ScreenUpdating = False Application.Calculation = xlCalculateManual Range("Table7[Multi Horse]").Formula = "=COUNTIFS([Race Entered],[@[Race Entered]], [Rider],[@Rider])" Range("Table7[Count for Pref]").Formula = "=MAX([Multi Horse])/[@[Multi Horse]]*(COUNTIFS(H$11:H11,H11,J$11:J11,J11))-1" Range("Table7['# to make the draw]").FormulaArray = "=IF([@[Draw '#]]>0, [@[Draw '#]],IF(OR([@[Race Entered]]=EventtoDraw, EventtoDraw=""), MAX(IF([Race Entered]=[@[Race Entered]],[Draw '#]))+SUMPRODUCT(([Race Entered]=[@[Race Entered]])*(['# for Drawing]<[@['# for Drawing]]))+COUNTIFS(H$11:H11,H11, C$11:C11,C11), ""))" Range("Table7['# for Drawing]").Formula = "=IF([@[Draw '#]]>0, "",IF([@[Draw Pref (0-10)]]>0, [@[Draw Pref (0-10)]],IF([@[Multi Horse]]>1,[@[Count for Pref]],RANDBETWEEN(0,1.5+MAX([Multi Horse])))))" Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub
Solved by K. J. in 13 mins
i want a macro that separates that splits the bold text and regular text.
Solved by E. H. in 25 mins

Leave a Comment

avatar