Summarize Total Project Hours in Excel with a PivotTable or Formula
Question details
Create a summary that totals all timesheet hours for each project when project names appear in multiple rows.
- Product
- Microsoft Excel or WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a timesheet list with repeated project names and a ManHours or hours column and needs one total for each project.
- Observed behavior
- A project summary should list every distinct project name and the sum of its numeric hours.
Make sure the source range has headers, such as Project and ManHours, and that every hours entry is stored as a number. Standardize project spelling and remove unintended spaces so matching project entries are grouped together.
Create a PivotTable to total ManHours by project
Use this method for a fast, refreshable summary of repeated timesheet entries. It is usually the simplest option for larger lists.
A PivotTable groups identical project names and calculates the total of the associated hours. Refresh the PivotTable after changing or adding source timesheet records.
Click any cell in the timesheet list, including the Project and ManHours column headers. In WPS Spreadsheet or Excel, choose Insert > PivotTable.
In the Create PivotTable dialog, verify that the selected table or cell range contains all timesheet rows, then choose where to place the PivotTable and select OK.
In the PivotTable Fields pane, drag Project to the Rows area. Each distinct project name will appear once in the report.
Drag ManHours to the Values area. If it shows Count of ManHours rather than Sum of ManHours, open the value field settings and choose Summarize Values By > Sum.
After editing the original timesheet range, click inside the PivotTable and use PivotTable Tools > Refresh, or right-click the PivotTable and choose Refresh.
Use a dynamic-array formula for an automatic project summary
Use this option when you want a formula-based summary that spills into adjacent cells and updates as the referenced data changes.
Build a Refreshable Project Hours Summary in WPS Spreadsheet
WPS Spreadsheet lets you summarize repeated timesheet entries with a PivotTable or calculate a live project-by-project total with formulas. Import an existing Excel workbook, organize the source data, and refresh the report whenever new hours are added.
- 1. Open the timesheet workbook: Open the Excel .xlsx or .xls file in WPS Office and select the worksheet containing Project and ManHours columns.
- 2. Insert a PivotTable: Click inside the source list, then select Insert > PivotTable and confirm the data range.
- 3. Configure project totals: Drag Project to Rows and ManHours to Values in the field list, then set the value calculation to Sum if necessary.
- 4. Refresh the report: After adding new timesheet rows, click the PivotTable and choose PivotTable Tools > Refresh to update the project totals.
Frequently Asked Questions
Why does my PivotTable show Count of ManHours instead of Sum?
The ManHours values are likely stored as text or the value field is set to Count. Convert the source entries to numbers, then open the value field settings in the PivotTable and select Summarize Values By > Sum.
How do I include new timesheet rows in a project-hours PivotTable?
If the new rows are inside the original source range, click the PivotTable and choose PivotTable Tools > Refresh. If they are outside the source range, use PivotTable Tools > Change Data Source to expand the range, then refresh.
Can a project-hours formula ignore blank project names?
Yes. The FILTER(A2:A1000,A2:A1000<>"") portion of the dynamic-array formula excludes blank project cells before UNIQUE and SORT create the project list.
What happens when two project names are spelled differently?
A PivotTable and SUMIF treat differently spelled names as separate projects. Correct inconsistent spelling, capitalization, or extra spaces in the Project column before creating the summary.
Does the dynamic-array formula work in every Excel version?
No. LET, FILTER, UNIQUE, SORT, and HSTACK require a version with dynamic-array support, such as Microsoft 365. Use a PivotTable when these functions are unavailable.
