List Departments with Overtime
I need a unique list of all departments that have reported overtime hours this week.
=UNIQUE(FILTER(B2:B10, D2:D10 > 0)) How it works: The `FILTER` function first evaluates the range `D2:D10` to find all rows where the 'Overtime Hours' are greater than 0. For those rows, it extracts the corresponding 'Department' names from the `B2:B10` range. The `UNIQUE` function then takes this filtered list of department names and returns only the distinct values, providing a clean list of all departments with reported overtime without any duplicates.
Data Setup
| Employee ID | Department | Hours Worked | Overtime Hours |
|---|---|---|---|
| E001 | Emergency | 40 | 5 |
| E002 | ICU | 40 | 0 |
| E003 | Emergency | 38 | 2 |
| E004 | Pediatrics | 40 | 0 |
| E005 | Surgery | 45 | 8 |
| E006 | ICU | 42 | 3 |
| E007 | Surgery | 40 | 0 |
| E008 | Emergency | 40 | 0 |
| E009 | Pediatrics | 45 | 5 |
Step-by-Step Guide
Ensure your data is organized with columns for 'Department' and 'Overtime Hours'. For this example, 'Department' is in column B and 'Overtime Hours' is in column D, starting from row 2.
Select an empty cell where you want the unique list of departments to appear (e.g., F2).
Enter the formula: `=UNIQUE(FILTER(B2:B10, D2:D10 > 0))`
Press Enter. Excel will spill the unique list of departments that have reported overtime hours into the selected cell and the cells below it.