How to Summarize Hours by Person Across Excel Columns
Question details
The user needs to calculate the total hours worked by each person across multiple task columns in an Excel worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a summary table to track and calculate total hours for different individuals spread out across multiple data columns.
- Observed behavior
- The user aims to match each person's name from a list and dynamically sum their corresponding numerical hour values from multiple target columns into a consolidated summary table.
Ensure that your dataset has consistent formatting, with names correctly spelled in their respective columns and hours entered as standard numerical values rather than text.
Use the SUMPRODUCT Function to Calculate Total Hours
The SUMPRODUCT function is the most reliable way to multiply and sum arrays, allowing you to match a person's name and sum their corresponding hours across multiple columns.
This formula works by comparing a target name against a column of names (creating a true/false array) and multiplying it by the corresponding numerical values in the adjacent columns.
Place the unique names of the individuals you want to summarize in a new, separate column (for example, Column J).
In the adjacent cell (e.g., K3), enter the formula: =SUMPRODUCT(($B$2:$B$16=J3)*($C$2:$E$16)). Adjust the range $B$2:$B$16 to where the raw names are located, and $C$2:$E$16 to the columns containing the hours.
Click and drag the fill handle from the bottom-right corner of cell K3 down the column to apply the calculation to the rest of the names in your summary table.
Generate a Dynamic Name List Using UNIQUE and FILTER
If you are using Microsoft 365 or Excel 2021, you can fully automate the creation of the unique name list before summarizing the hours.
Summarize Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT, UNIQUE, and FILTER, allowing you to manage timesheets and summarize hours across columns effortlessly without compatibility issues.
- 1. Open your timesheet: Launch WPS Spreadsheet and open the .xlsx document containing your hours and personnel data.
- 2. Set up your summary table: Create a dedicated area or a new sheet to list out the unique names of the individuals you need to track.
- 3. Insert the summary formula: Select the target cell, input the SUMPRODUCT formula referencing your data ranges, and press Enter to calculate the totals across columns.
- 4. Fill down the data: Double-click the fill handle on the calculated cell to instantly copy the formula for all other individuals on your list.
Frequently Asked Questions
Why is my SUMPRODUCT formula returning a #VALUE! error?
This error generally occurs when the array sizes do not perfectly match. Ensure that your criteria range (e.g., $B$2:$B$16) has the exact same number of rows as your sum range (e.g., $C$2:$E$16).
Can I use the AutoSum feature to total hours across columns by name?
AutoSum is highly effective for totaling contiguous rows or columns directly, but it cannot match specific criteria (like a person's name) across a mixed dataset. For criteria-based summarization across multiple columns, an array formula like SUMPRODUCT is required.
Are UNIQUE and FILTER functions available in older versions of Excel?
No, dynamic array functions such as UNIQUE and FILTER are only available in Microsoft 365, Excel 2021, and newer versions. For older software versions, you must manually remove duplicates to extract unique names or use combined INDEX and MATCH formulas.
Does WPS Spreadsheet support the SUMPRODUCT function?
Yes, WPS Spreadsheet fully supports the SUMPRODUCT function and other advanced array formulas, ensuring high compatibility with your existing Excel timesheets and complex data summaries.
