Function Problems

Use Excel SUMIFS to Total Approved Time Off by Employee and Pay Code

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Calculate approved time off totals using both employee name and pay code as criteria.

Use Excel SUMIFS to Total Approved Time Off by Employee and Pay Code
Product
Microsoft Excel
Device & OS
not provided
Scenario
A destination table needs to summarize approved time off from a source table by employee and pay code.
Observed behavior
Each destination cell should show the total approved time for the matching employee and pay code.
Before you start

Check the source table and column names, and confirm that Time Approved contains numeric values. Identify the destination employee column and the header or cell that contains the pay code.

Solution 1Recommended

Sum approved time using structured table references

Use this formula when the source and destination data are in Excel tables and the pay code is a destination table header.

1
Select the destination cell

In the destination table, select the cell for the employee and the Personal Time pay code.

2
Enter the SUMIFS formula

In the formula bar, enter =SUMIFS(Table1[Time Approved],Table1[Employee Name],[@Employee],Table1[Pay Code],Table4[[#Headers],[Personal Time]]) and press Enter.

3
Adjust the references

Replace Table1, Table4, and the column names with the names used in your workbook. If the pay code is in a worksheet cell instead of the table header, replace Table4[[#Headers],[Personal Time]] with that cell reference.

4
Check the total

Compare the result with matching rows in Table1. The source table references expand when you add rows to the table.

Sum approved time using structured table references
How the criteria work: SUMIFS adds Time Approved only for rows where Employee Name matches the current destination row and Pay Code matches the specified header or cell.
Calculate time-off totals in WPS

Sum approved time off with WPS Spreadsheets

Use SUMIFS in WPS Spreadsheets to total approved time by employee and pay code using worksheet cell ranges.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your workbook.
  2. 2. Locate the data and criteria: Find the source columns for employee, pay code, and approved time, plus the destination cells containing the employee and pay code.
  3. 3. Enter SUMIFS: Select a destination cell and enter =SUMIFS($C$2:$C$100,$A$2:$A$100,$E2,$B$2:$B$100,F$1), adjusting the ranges and criteria cells to match your worksheet.
  4. 4. Fill and check: Press Enter, compare the result with matching source rows, and drag the fill handle to calculate other totals.
Calculate totals using both employee and pay code criteriaOpen and edit Microsoft Excel XLSX and XLS filesCopy formulas across a summary grid using the fill handleWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

What does each part of the SUMIFS formula do?

The first argument is the Time Approved range to sum. Each subsequent pair specifies a criteria range and its matching value: Employee Name with the employee, then Pay Code with the pay code.

Can I use a cell instead of a pay code header?

Yes. In the formula bar, replace the pay code header reference with the cell containing the pay code, such as F$1.

Why does my SUMIFS formula return zero?

Check that employee names and pay codes match the source values and that Time Approved contains numbers rather than text. Also verify that the sum and criteria ranges cover the corresponding rows.

Will the formula include newly added rows?

Structured references include rows added to the source Excel table. If you use fixed cell ranges instead, extend those ranges when new data falls outside them.