Sum time over 30 minutes

To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. In the example shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0))) where "times" is the named range C5:C14.

April 14, 2025 · 1 min · 54 words · David Francis

Table of Contents

Explanation

This formula uses the SUMPRODUCT function to sum the result of two expressions that yield arrays.

Notice the result in D15 is the same as the result in G5.

The TIME function is useful when you want to assemble a proper time inside another formula.

Excel formula: Sum race time splits

COUNTIF can be used to count cells that…

Excel SUMPRODUCT function

Excel TIME function

Excel SUMIFS function

Excel COUNTIFS function