Track Budget vs Actual by Room
I need to sum 'Actual Cost' where Room is 'Kitchen' and Expense Type is 'Materials'. This helps me monitor specific expenditures for different parts of a renovation project.
=SUMIFS(C2:C9, A2:A9, "Kitchen", B2:B9, "Materials") How it works: The SUMIFS function is designed to sum values in a specified range that meet multiple criteria. In this scenario, it efficiently sums all 'Actual Cost' entries (from C2:C9) only for those rows where the 'Room' column (A2:A9) exactly matches "Kitchen" AND the 'Expense Type' column (B2:B9) exactly matches "Materials". This provides a precise total for a very specific component of your renovation project, allowing for granular budget tracking.
Data Setup
| Room | Expense Type | Actual Cost |
|---|---|---|
| Kitchen | Materials | 500 |
| Kitchen | Labor | 1200 |
| Bathroom | Materials | 300 |
| Kitchen | Materials | 750 |
| Living Room | Materials | 400 |
| Kitchen | Permits | 100 |
| Bathroom | Labor | 800 |
| Kitchen | Materials | 200 |
Step-by-Step Guide
Identify the range containing the values you want to sum, which is your 'Actual Cost' column (e.g., C2:C9).
Specify the first criteria range, which is your 'Room' column (e.g., A2:A9).
Define the first criteria, which is the specific room you want to filter by, in this case, "Kitchen". Remember to enclose text in double quotes.
Specify the second criteria range, which is your 'Expense Type' column (e.g., B2:B9).
Define the second criteria, which is the specific expense type, "Materials". Again, use double quotes for text.
Combine these into the SUMIFS function: `=SUMIFS(C2:C9, A2:A9, "Kitchen", B2:B9, "Materials")`.