Solved: How to calculate the weighted average of a visible range after applying filters using SUMPRODUCT and SUBTOTAL in Excel

📌 The Problem Explained

Imagine you are managing a product inventory. You have a list of items with their Unit Price and Quantity in Stock. To find the average price across your entire inventory, a simple average isn’t enough; you need a weighted average (where quantity determines the weight of the price).

The challenge arises when you apply a filter (e.g., filtering by “Category” or “Supplier”). A standard SUMPRODUCT formula continues to calculate hidden rows, leading to incorrect totals for your filtered view. Excel’s SUBTOTAL function can ignore hidden rows, but it doesn’t natively support weighted averages. To solve this, you must combine SUMPRODUCT with a specific SUBTOTAL and OFFSET array trick.

💡 The Quick Solution

Use the following formula structure (assuming Prices are in B2:B10 and Weights/Quantities are in C2:C10):

=SUMPRODUCT(B2:B10, C2:C10, SUBTOTAL(103, OFFSET(B2, ROW(B2:B10)-MIN(ROW(B2:B10)), 0))) / SUMPRODUCT(C2:C10, SUBTOTAL(103, OFFSET(C2, ROW(C2:C10)-MIN(ROW(C2:C10)), 0)))

🛠️ Step-by-Step Breakdown

To implement this, ensure your data is organized in columns and you have applied a filter via Data > Filter.

Component	Function / Logic	Purpose
SUMPRODUCT	SUMPRODUCT(Values, Weights, Visibility_Array)	Multiplies the values by their weights, but only if the visibility array returns 1.
SUBTOTAL	SUBTOTAL(103, ...)	The function code 103 counts non-empty cells while ignoring hidden rows.
OFFSET	OFFSET(First_Cell, ROW(Range)-MIN(ROW(Range)), 0)	This forces Excel to look at each row individually rather than the whole range at once.
Visibility Array	The result of the SUBTOTAL/OFFSET combo	Generates an array of 1s (visible) and 0s (hidden) for SUMPRODUCT to use as a multiplier.
The Divisor	/ SUMPRODUCT(Weights, Visibility_Array)	Divides the total weighted sum by the sum of the weights of only the visible rows.

Detailed Execution:

1. Identify your ranges: Let B2:B10 be your values (e.g., Price) and C2:C10 be your weights (e.g., Quantity).
2. Generate the Visibility Mask: The expression SUBTOTAL(103, OFFSET(B2, ROW(B2:B10)-MIN(ROW(B2:B10)), 0)) creates an array like {1; 0; 1...} where 1 represents a visible row.
3. Calculate the Numerator: SUMPRODUCT multiplies Price * Quantity * Visibility. Hidden rows become 0.
4. Calculate the Denominator: You must divide by the sum of visible weights only. Use SUMPRODUCT(Weights, Visibility_Array).

⚠️ Common Mistakes to Avoid

• Using the wrong SUBTOTAL code: Do not use 3 if you want to ignore rows hidden by manual “Hide Row” commands; use 103. If you only care about rows hidden by filters, both work, but 103 is more robust for general productivity.
• Forgetting the denominator: A common error is dividing by a simple SUM(C2:C10). This will include hidden weights in your division, resulting in a much lower (and incorrect) average.
• Range Mismatch: Ensure all ranges (Values, Weights, and the Range inside the OFFSET function) have the exact same start and end rows (e.g., 2 to 10). If they don’t align, Excel will return a #VALUE! error.