Sum Substitute Teacher Pay
I need to sum 'Daily Rate' where Role is 'Substitute' and Month is 'November'. This helps me quickly calculate the total pay for all substitute teachers for a specific month.
=SUMIFS(D2:D10, B2:B10, "Substitute", C2:C10, "November") How it works: The SUMIFS function allows you to sum values in a range that meet multiple criteria. In this scenario, it sums the 'Daily Rate' (D2:D10) only for rows where the 'Role' (B2:B10) is exactly 'Substitute' AND the 'Month' (C2:C10) is exactly 'November'. This provides a precise total for substitute teacher pay for the specified month.
Data Setup
| Employee Name | Role | Month | Daily Rate |
|---|---|---|---|
| John Doe | Teacher | November | 150 |
| Jane Smith | Substitute | November | 120 |
| Peter Jones | Substitute | October | 120 |
| Alice Brown | Teacher | November | 150 |
| Bob White | Substitute | November | 130 |
| Charlie Green | Substitute | December | 125 |
| Diana Prince | Teacher | November | 150 |
| Eve Adams | Substitute | November | 120 |
| Frank Black | Substitute | October | 130 |
Step-by-Step Guide
Identify the column containing the values you want to sum (Daily Rate, which is column D).
Identify the first criteria range (Role, which is column B) and the corresponding criteria ("Substitute").
Identify the second criteria range (Month, which is column C) and the corresponding criteria ("November").
Enter the SUMIFS formula into an empty cell, ensuring the ranges match your data (e.g., D2:D10 for sum_range, B2:B10 for first criteria_range, C2:C10 for second criteria_range).