Track Concrete Costs by Supplier
I need to sum the total cost of 'Concrete' purchased specifically from 'BuildCo' to audit our spending.
=SUMIFS(D2:D8, B2:B8, "Concrete", C2:C8, "BuildCo") How it works: The SUMIFS function allows you to sum values in a range that meet multiple criteria. In this scenario, it sums all values in the 'Cost' column (D2:D8) only if the corresponding 'Item' in column B is 'Concrete' AND the 'Supplier' in column C is 'BuildCo'. This precisely targets the specific spending you want to audit.
Data Setup
| Date | Item | Supplier | Cost |
|---|---|---|---|
| 2023-01-15 | Concrete | BuildCo | 1500 |
| 2023-01-20 | Steel | MetalWorks | 2000 |
| 2023-01-22 | Wood | TimberSupply | 800 |
| 2023-02-01 | Concrete | BuildCo | 1200 |
| 2023-02-05 | Gravel | RoadMaterials | 500 |
| 2023-02-10 | Concrete | CementCorp | 1800 |
| 2023-02-12 | Concrete | BuildCo | 900 |
Step-by-Step Guide
Open your Excel spreadsheet containing your procurement data.
Identify the column that contains the 'Cost' (e.g., Column D) – this will be your 'sum_range'.
Identify the column that contains the 'Item' (e.g., Column B) – this will be your first 'criteria_range'.
Identify the column that contains the 'Supplier' (e.g., Column C) – this will be your second 'criteria_range'.
In an empty cell, type the SUMIFS formula: `=SUMIFS(D2:D8, B2:B8, "Concrete", C2:C8, "BuildCo")`
Press Enter to get the total cost.
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.
Calculate Total Hours by Subcontractor
I want to sum the total billable hours worked by the 'Plumbing' subcontractor across all project days.