Calculate Total Hours by Subcontractor
I want to sum the total billable hours worked by the 'Plumbing' subcontractor across all project days.
=SUMIF(B2:B9,"Plumbing",D2:D9) How it works: The SUMIF function evaluates each cell in the specified 'range' (B2:B9, the Subcontractor column). If a cell's content matches the 'criteria' ("Plumbing"), the function then adds the corresponding value from the 'sum_range' (D2:D9, the Hours Worked column) to a running total. This effectively filters and sums hours only for the 'Plumbing' subcontractor.
Data Setup
| Date | Subcontractor | Task | Hours Worked |
|---|---|---|---|
| 2023-10-01 | Plumbing | Pipe Installation | 8 |
| 2023-10-01 | Electrical | Wiring | 6 |
| 2023-10-02 | Plumbing | Fixture Setup | 7 |
| 2023-10-02 | HVAC | Ductwork | 9 |
| 2023-10-03 | Plumbing | Inspection Prep | 5 |
| 2023-10-03 | Electrical | Panel Upgrade | 7 |
| 2023-10-04 | Plumbing | Final Connections | 6 |
| 2023-10-04 | HVAC | Unit Installation | 8 |
Step-by-Step Guide
Open your project's daily log or timesheet in Excel.
Identify the column that lists the 'Subcontractor' names (e.g., Column B). This will be your 'range'.
Identify the specific subcontractor you want to sum hours for, which is 'Plumbing'. This will be your 'criteria'.
Identify the column that contains the 'Hours Worked' (e.g., Column D). This will be your 'sum_range'.
In an empty cell where you want the total to appear, type the formula: `=SUMIF(B2:B9,"Plumbing",D2:D9)` and press Enter.
Explore More
Flag Budget Overruns for Materials
I want a formula that displays 'Over Budget' if the actual material cost exceeds the estimated cost, otherwise 'Within Budget'.
Get Material Cost per Unit
I need to pull the unit cost for 'Concrete' and 'Lumber' from a vendor price list into my estimation sheet.
Count Safety Incidents by Site
I need to count the number of safety reports logged for 'Site A' to monitor compliance.
Find Supplier Contact by Material
I need to find the phone number of the supplier who provides 'Drywall', searching through a messy vendor list.