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
First, we will briefly explain the SUM function. This function sums cells’ values from a given range. The syntax is:
The range is a range of cells which we want to sum. For example, if we write the formula like this:
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:
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.