How to Fix Excel PivotTable Time Totals Not Summing Correctly
Question details
The user needs to correct an issue where a PivotTable displays the count of time entries instead of summing up their total duration.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Aggregating time durations (HH:MM:SS) in a PivotTable report.
- Observed behavior
- The PivotTable defaults to counting the time entries rather than calculating their sum, and totals exceeding 24 hours display incorrectly due to standard time formatting.
Ensure your source data column containing time values is formatted correctly as Time or Custom (hh:mm:ss) and does not contain any hidden text characters or blank cells.
Change Value Field Settings to Sum and Apply Custom Time Format
Modify the calculation type of the PivotTable field from Count to Sum, and apply an elapsed time format to handle durations over 24 hours.
When time fields are added to a PivotTable, the spreadsheet application often defaults to counting the number of records instead of adding the times together. Additionally, standard time formats reset back to zero after 24 hours, meaning cumulative time totals require a specific custom format.
Click on the time field in the 'Values' area of your PivotTable Fields pane. From the drop-down menu that appears, select 'Value Field Settings'.
In the 'Summarize value field by' tab within the dialog box, select 'Sum' instead of 'Count'.
Click the 'Number Format' button located at the bottom left of the dialog box. Choose the 'Custom' category.
In the 'Type' input box, enter '[h]:mm:ss' and click 'OK'. This specific bracketed format ensures time totals exceeding 24 hours are displayed accurately as elapsed time rather than clock time.
Click 'OK' one more time to apply the new calculation and formatting rules to your PivotTable.
Easily Sum Time in PivotTables with WPS Spreadsheet
WPS Spreadsheet provides a highly compatible, intuitive interface for managing PivotTables, allowing you to easily sum time data, customize calculations, and format large time aggregates effortlessly.
- 1. Create a PivotTable: Highlight your data in WPS Spreadsheet, navigate to the Insert tab on the top ribbon, and click PivotTable.
- 2. Adjust Value Settings: Drag your time column into the Values area. Click on it and select Value Field Settings.
- 3. Select Sum and Format: Choose 'Sum', then click 'Number Format'. Select 'Custom' and input '[h]:mm:ss' to accurately aggregate and display total time durations.
Frequently Asked Questions
Why does my PivotTable default to 'Count' instead of 'Sum'?
PivotTables will automatically default to 'Count' if there are any blank cells or text values in the source data column. Ensure your entire column contains only valid numeric time entries with no blanks to make it default to 'Sum'.
Why does my summed time show as 0:00:00?
This usually occurs because the source data is formatted as text rather than an actual time value. You can resolve this by converting the text to time using the VALUE function, or by copying a blank cell and using Paste Special > Add over your time data.
Can I sum minutes and seconds without displaying hours?
Yes. If you only want to see accumulated minutes and seconds, you can adjust the custom number format to [m]:ss instead of [h]:mm:ss. This will accumulate all elapsed hours directly into the total minute count.
