Total Overtime Hours by Department
I want to sum the total overtime hours claimed by the 'Engineering' department for payroll processing.
=SUMIF(A2:A7,"Engineering",C2:C7) How it works: The SUMIF function checks each cell in the specified 'range' (A2:A7) for a match with the 'criteria' ("Engineering"). For every cell that matches, it adds the corresponding value from the 'sum_range' (C2:C7) to the total. This allows the HR Manager to quickly aggregate overtime hours for a specific department without manually filtering or summing.
Data Setup
| Department | Employee Name | Overtime Hours |
|---|---|---|
| Engineering | Alice Smith | 15 |
| Marketing | Bob Johnson | 10 |
| Engineering | Charlie Brown | 20 |
| Sales | Diana Prince | 5 |
| Engineering | Eve Adams | 12 |
| Marketing | Frank White | 8 |
Step-by-Step Guide
Identify the range containing the department names (e.g., column A, from A2 to A7). This will be your 'range' argument.
Specify the criteria for summing, which is 'Engineering'. This will be your 'criteria' argument.
Identify the range containing the overtime hours (e.g., column C, from C2 to C7). This will be your 'sum_range' argument.
Combine these into the SUMIF formula: =SUMIF(A2:A7,"Engineering",C2:C7).
Enter the formula into a cell where you want to see the total.
Explore More
Dynamically Finding Employee Salary by ID
An HR Manager frequently needs to look up an employee's salary based on their unique Employee ID. Manually searching through a large employee roster is inefficient and prone to errors, especially when dealing with hundreds or thousands of employees.
Quickly Locating Employee Email Addresses
An HR manager needs to quickly find an employee's email address to send important announcements, respond to inquiries, or update records, using only their Employee ID. Manually sifting through a large employee roster is inefficient and time-consuming.
Calculate Employee Probation End Date
I need to automatically calculate the date 90 days after an employee's start date to schedule their review.
Track Number of Sick Days Taken
I need to count how many times 'Sick Leave' appears in an employee's annual attendance record.