Are you struggling to group and sum time durations in Google Sheets using the Query function? Look no further! In this article, I will share two different methods that will help you accomplish this task effortlessly. Let’s dive right in!
Simple Method with Manual Formatting
The first method I’m going to share is relatively simple, but it does require some manual formatting. If you don’t mind formatting the duration column (column E) to a number, then this method is for you.
Here’s how you can do it:
- Select the time duration range (e.g., E2:E8).
- Go to the Format menu and choose Number > Number.
- Now, the Query aggregation function SUM will work correctly, albeit the output will be in a number format.
To convert the result back to a duration format, just select the cell with the Query formula (e.g., G3) and apply Format > Number > Duration.
That’s all you need to do to group and sum time durations using the Query function in Google Sheets!
Sum Time Duration Without Manual Formatting
If you prefer to avoid manual formatting, don’t worry! I have a solution for you as well. Follow these steps:
Step 1: Convert the time durations in column E to time values or numbers using the following formula:
=ArrayFormula(hour(E2:E8)/24+minute(E2:E8)/1440+second(E2:E8)/86400)Step 2: Instead of using column E directly, use the virtual column (the converted time durations) in your Query formula. Here’s an example:
=Query({A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Sum(Col5)",1)Note: When using virtual data in Query, remember to use column numbers instead of column letters as identifiers. In the example above, I replaced Sum(E) with Sum(Col5).
To format the result column back to a duration format, simply click Format > Number > Duration.
I also explained this method in one of my advanced Query tutorials – “Query to Calculate Hours Worked in Week Wise in Google Sheets.”
Grouping and Summing Time Durations Using Query
Now that you know how to sum time durations using Query, let’s talk about how to group and sum them. You can use either of the two formulas I mentioned earlier (#1 or #2) and add a group by clause. Here’s an example for each:
Query Formula with Manual Formatting:
=query(A1:E,"Select B, Sum(E) where A is not null Group By B",1)
Query Formula Without Manual Formatting:
=Query({A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Col2,Sum(Col5) where Col1 is not null group by Col2",1)
Remember to format the appropriate columns to duration using Format > Number > Duration.
And there you have it! You now know how to group and sum time durations using the Query function in Google Sheets. It’s as simple as that!
For more tips and tricks on using Google Sheets, be sure to check out Crawlan.com. Happy time duration grouping and summing!
