Multi-condition weighted average with SUMPRODUCT
AdvancedA weighted average is a handy inventory tool. When several warehouses stock the same product at different costs, the cost you report should lean toward where most of the units sit, not just how many locations match.
The filter inputs are already set in B1 and D1. The inventory table runs from A3:E16, and the summary sits in A18:B20.
Fill in B18:B20 so every result recalculates when B1 or D1 changes. Use SUMPRODUCT for all three formulas.
Count a warehouse only when it matches both filters: the region in B1 and the product line in D1. This is an AND filter, not an OR filter.
Here is what each line needs:
- B18 (Total matching units): add up the units on hand across the matching warehouses.
- B19 (Simple average cost): the plain average unit cost across the matching warehouses. Every warehouse counts the same, no matter how many units it holds.
- B20 (Weighted average cost): the average unit cost, weighted by units on hand. Larger warehouses pull the result toward their cost.
Need some help?
Hint 1
Treat the summary as one filtered subset viewed three different ways: quantity, cost where each matching warehouse has equal influence, and cost influenced by inventory concentration.
Hint 2
SUMPRODUCT works best here when every array covers the same table rows. Build a reusable row-level filter from the two parameter cells, then choose the numeric arrays that match the business meaning of each summary line.
Hint 3
You don't need SUMPRODUCT's comma-separated arguments here. Pass one expression and multiply the arrays inside it. Each `(range=cell)` test returns a column of TRUE/FALSE that Excel reads as 1 or 0, so multiplying them keeps only the rows where both conditions hold. That single-argument trick is what makes the AND filter work.
Related function(s)
Multi-condition weighted average with SUMPRODUCT
AdvancedA weighted average is a handy inventory tool. When several warehouses stock the same product at different costs, the cost you report should lean toward where most of the units sit, not just how many locations match.
The filter inputs are already set in B1 and D1. The inventory table runs from A3:E16, and the summary sits in A18:B20.
Fill in B18:B20 so every result recalculates when B1 or D1 changes. Use SUMPRODUCT for all three formulas.
Count a warehouse only when it matches both filters: the region in B1 and the product line in D1. This is an AND filter, not an OR filter.
Here is what each line needs:
- B18 (Total matching units): add up the units on hand across the matching warehouses.
- B19 (Simple average cost): the plain average unit cost across the matching warehouses. Every warehouse counts the same, no matter how many units it holds.
- B20 (Weighted average cost): the average unit cost, weighted by units on hand. Larger warehouses pull the result toward their cost.
Need some help?
Hint 1
Treat the summary as one filtered subset viewed three different ways: quantity, cost where each matching warehouse has equal influence, and cost influenced by inventory concentration.
Hint 2
SUMPRODUCT works best here when every array covers the same table rows. Build a reusable row-level filter from the two parameter cells, then choose the numeric arrays that match the business meaning of each summary line.
Hint 3
You don't need SUMPRODUCT's comma-separated arguments here. Pass one expression and multiply the arrays inside it. Each `(range=cell)` test returns a column of TRUE/FALSE that Excel reads as 1 or 0, so multiplying them keeps only the rows where both conditions hold. That single-argument trick is what makes the AND filter work.