Function Problems

Excel Formula to Track Employee Approval Progress and Unapproved Files

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs an Excel formula to track assignment and approval dates, calculate the days required for approval, and count the number of unapproved files per employee.

Product: Excel
Device & OS: not provided
Scenario: Managing and tracking employee file approvals, monitoring progress dates, and calculating outstanding workloads.
Observed behavior: Establishing a tracking system using formulas that successfully calculates approval duration and filters unapproved status accurately for multiple employees.

Before you start

Ensure your dataset is organized with clear columns for Employee Names, Assignment Dates, Approval Dates, and Approval Status before applying the tracking formulas.

Solution 1Recommended

Use COUNTIFS with Absolute References to Count Unapproved Files

Use the COUNTIFS function to count exactly how many files remain "Not Approved" for a specific employee while using absolute references to maintain the source range.

When counting multiple criteria across a dataset, COUNTIFS is the most effective formula. Adding absolute references ($) ensures that when you drag the formula down to apply it to other employees, the referenced data range does not shift.

Select the target cell

Click on the cell where you want the unapproved file count to appear (for example, next to the employee's name in cell H2).

Enter the COUNTIFS formula

Type the formula =COUNTIFS($A$2:$A$12,H2,$E$2:$E$12,"Not Approved") into the formula bar. In this example, $A$2:$A$12 is the employee name column, H2 is the specific employee you are evaluating, and $E$2:$E$12 is the approval status column.

Copy the formula down

Press Enter to apply the formula. Then, click the small square at the bottom-right corner of the cell (fill handle) and drag it down to copy the formula for all other employees in your summary list.

Formula accuracy: Using the dollar signs ($) locks the data range, preventing reference errors as you copy the formula down your spreadsheet.

Solution 2

Calculate Days Required for Approval

Determine the duration an employee took to approve a file by utilizing a straightforward date subtraction formula.

Advanced Spreadsheet Functions

Track Approval Workflows Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced logical functions like COUNTIFS and automatic date calculations, making it simple to build comprehensive tracking workbooks for your team.

1. Open your workbook: Launch WPS Spreadsheet and open your employee tracking data file.
2. Apply tracking formulas: Use the COUNTIFS function with absolute references just as you would in Excel to monitor unapproved files.
3. Enhance with formatting: Highlight the formula results and apply conditional formatting to color-code overdue approvals instantly.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsBuilt-in templates for project and task trackingAdvanced data validation and conditional formatting for status trackingFree, lightweight, and fast alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return a #VALUE! error or zero?

This usually happens if the range sizes do not match (e.g., referencing A2:A12 but E2:E13) or if there are hidden trailing spaces in the text "Not Approved". Ensure your ranges are identical in size and text criteria match perfectly.

What is the purpose of the dollar signs ($) in the Excel formula?

The dollar signs create absolute references. This means that when you copy the formula down to other cells, the referenced data range stays perfectly fixed and doesn't shift row by row, ensuring accurate calculations.

How can I automatically highlight files that have been pending for more than 5 days?

You can use Conditional Formatting. Select your data range, click on 'Conditional Formatting' > 'New Rule', choose to use a formula, and enter something like =TODAY()-B2>5 (assuming B is the Assignment Date). Set a red fill color to highlight these overdue files.

Excel & Data Problems