Function Problems

How to Total Employee Hours Across Sheets Using SUMIF in Excel

Tauseeq Magsi Oct 22, 2026 868 views

Question details

The user needs to total weekly hours for employees by matching their employee numbers across different worksheets.

How to Total Employee Hours Across Sheets Using SUMIF

Product: Excel
Device & OS: not provided
Scenario: A user is managing employee timesheets and needs to consolidate weekly hours from a data sheet into a master summary sheet using unique employee numbers as the identifier.
Observed behavior: Data needs to be aggregated accurately across multiple sheets where rows may not be in the exact same order, requiring a conditional formula to match the employee numbers.

Before you start

Ensure that the employee numbers on both sheets are formatted identically (e.g., both formatted as numbers or both formatted as text) so the formula can successfully match the criteria.

Solution 1Recommended

Use the SUMIF Function to Match and Total Hours

The SUMIF function is the most efficient way to add up values based on a single condition, such as matching an employee ID, across different worksheets.

The SUMIF formula works by evaluating a specific range of cells against a given criteria, and then summing up the corresponding values from another range. When dealing with multiple sheets, you simply need to include the sheet names in your cell references.

Select the target cell

Open Sheet2 and click on the cell where you want the total weekly hours for the first employee to appear.

Enter the SUMIF formula

Type the formula: =SUMIF(Sheet1!A:A, Sheet2!A2, Sheet1!E:E) directly into the cell or the formula bar.

Apply the formula

Press Enter to calculate the total hours for the employee listed in cell A2.

Fill the formula down

Click the small square at the bottom-right corner of the formula cell and drag it down the column to automatically apply the calculation to the rest of your employee list.

Use the SUMIF Function to Match and Total Hours

Formula Breakdown: Sheet1!A:A represents the column where employee numbers are located on the first sheet. Sheet2!A2 is the specific employee number you want to match. Sheet1!E:E is the column containing the hours you want to total.

Data Analysis with WPS Spreadsheet

Calculate and Manage Data Across Sheets Easily with WPS Office

WPS Spreadsheet provides powerful functions, including SUMIF, VLOOKUP, and PivotTables, allowing you to seamlessly calculate and manage complex employee data across multiple worksheets.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office, open WPS Spreadsheet, and load the workbook containing your employee data.
2. Access the Insert Function tool: Navigate to the Formulas tab on the top ribbon and click on 'Insert Function' (fx).
3. Configure the SUMIF parameters: Search for 'SUMIF' in the function box. Use your mouse to effortlessly select the Range on your data sheet, the Criteria on your master sheet, and the Sum_range.
4. Generate totals: Click OK to apply the formula, then double-click the fill handle to populate the totals for all employees instantly.

100% compatible with Microsoft Excel formulas, formatting, and file typesCross-sheet calculations made easy with an intuitive formula builderFree, lightweight, and fast performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIF formula returning zero even though the employee numbers match?

This usually happens when there is a data type mismatch. For instance, employee numbers in Sheet1 might be formatted as text while those in Sheet2 are formatted as numbers. Ensure both columns share the same formatting.

Can I use multiple criteria to sum hours across sheets?

Yes. If you need to match multiple conditions, such as an employee number and a specific project code, you should use the SUMIFS function instead of SUMIF.

What happens if an employee number listed on Sheet2 doesn't exist in Sheet1?

If the SUMIF function cannot find a matching criteria in the specified range, it will safely return a 0 for that cell without causing a formula error.

How do I adjust the formula if my data doesn't start in row 1?

Instead of selecting entire columns (like A:A), you can select specific ranges (like Sheet1!A2:A100). If you do this, make sure to make the range absolute (Sheet1!$A$2:$A$100) so the references don't shift when you fill the formula down.

Excel & Data Problems