Function Problems

How to Align Dates by Employee Using SUMIFS in Excel

Huda Qurayshi Oct 21, 2026 868 views

Question details

The user needs to align and compare dates between two columns grouped by employee, and calculate the related values using the SUMIFS function.

How to Align Excel Dates Between Columns by Employee with SUMIFS

Product: Excel
Device & OS: not provided
Scenario: Calculating values based on date matching within specific employee groupings where some employee name cells in the dataset are initially blank.
Observed behavior: Employee names must be successfully filled down into blank cells before a SUMIFS formula can accurately calculate the aligned date values.

Before you start

Ensure your dataset is organized with employee names in one column, the dates to compare in two other columns, and the numerical values you want to sum in a separate column.

Solution 1Recommended

Fill Blank Employee Names and Apply SUMIFS Formula

Use the Go To Special feature to quickly fill missing employee names, then use SUMIFS to calculate values based on matching dates.

Before using the SUMIFS formula, data containing grouped employee records often has blank cells under each employee name to avoid repetition. These blank cells must be filled with the corresponding employee's name so the formula can properly identify the criteria for every row.

Select the Employee Column Range

Highlight the cells in the employee name column that contain both the names and the blank cells below them (for example, A3:A23).

Select Blank Cells

Press Ctrl+G on your keyboard to open the Go To dialog box, click on the 'Special' button, select 'Blanks', and click OK to highlight only the empty cells.

Fill Employee Names Down

Without clicking anywhere else on the sheet, type '=' and press the Up Arrow key to reference the cell immediately above (e.g., =A2). Press Ctrl+Enter to apply this formula to all selected blank cells simultaneously.

Apply the SUMIFS Formula

In your result cell (e.g., F3), enter the formula: =SUMIFS($E$3:$E$23,$A$3:$A$23,A3,$D$3:$D$23,B3). Adjust the column ranges ($E for the sum range, $A for the employee criteria range, and $D for the date criteria range) to match your specific workbook structure.

Copy the Formula Down

Press Enter to get the calculated result, then drag the fill handle from the bottom-right corner of the cell downwards to apply this formula to the remaining rows.

Fill Blank Employee Names and Apply SUMIFS Formula

Use Absolute References: Make sure to use absolute references (indicated by the $ signs, like $E$3:$E$23) for your data ranges so they don't shift when you copy the formula down.

Advanced Spreadsheet Features

Easily Manage Data and Formulas with WPS Spreadsheet

You can effortlessly perform advanced data alignments, fill blank cells using 'Go To Special', and write SUMIFS formulas in WPS Spreadsheet, a highly compatible and lightweight office suite.

1. Open Data in WPS Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook containing the employee and date records.
2. Fill Blank Cells: Highlight your employee column, press Ctrl+G, click 'Blanks', type '=' followed by the Up Arrow, and press Ctrl+Enter.
3. Calculate with SUMIFS: Type your SUMIFS formula in the target cell to accurately align and calculate the values based on dates, then drag the formula down to apply it to all rows.

Fully compatible with Microsoft Excel formulas like SUMIFS.Includes the 'Go To Special' feature for quick data formatting and blank cell filling.Lightweight, fast, and completely free to use.Familiar user interface makes it easy to transition from other software.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning a VALUE! error or 0?

This usually happens if your criteria ranges and sum ranges are not exactly the same size. Ensure ranges like $E$3:$E$23 and $A$3:$A$23 have the exact same number of rows. Returning 0 can also mean the dates in column B and D are formatted as text instead of valid dates.

How does Ctrl+Enter work when filling blank cells?

Pressing Ctrl+Enter instead of just Enter applies the formula you just typed to all currently selected cells at once. In this scenario, it allows you to dynamically fill multiple disconnected blank cells with the value from the cell immediately above each of them.

Can I use multiple criteria in SUMIFS?

Yes, the SUMIFS function is designed specifically for multiple criteria. In this task, it uses one criterion for the employee name and a second criterion for the specific date to ensure precise alignment and calculation.

Excel & Data Problems