Calculation Issues

How to Exclude Zero Values from a Subtotal Average in Excel

John Wilson Oct 7, 2026 872 views

Question details

The user needs to calculate the average of a dataset using the subtotal function to respect filtered rows, but wants to ensure that any cells containing zero are excluded from the average calculation.

How to Exclude Zero Values from Subtotal Averages in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating an accurate average of filtered data where zero values are present and would otherwise negatively skew the final result.
Observed behavior: Standard subtotal functions (like SUBTOTAL(101, ...)) include zero values in the average calculation, and filtering does not automatically ignore zeros unless they are explicitly filtered out or handled by a custom formula.

Before you start

Ensure your dataset is organized in a clear tabular format, and note the exact range of cells containing the values you intend to calculate and filter.

Solution 1Recommended

Use the LET and BYROW Functions to Exclude Zeros

This method combines modern dynamic array functions to check for visible rows and non-zero values simultaneously, ensuring an accurate subtotal average.

This formula uses LAMBDA and BYROW to iterate through the filtered range, checking which rows are currently visible, and then multiplies that by a condition checking for non-zero values.

Select the destination cell

Click on the cell where you want the subtotal average result to be displayed (for example, cell B12).

Input the array formula

Type the following formula into the formula bar: =LET(lgc,BYROW(A2:A9,LAMBDA(a,SUBTOTAL(103,a)))*(B2:B9<>0),SUM(lgc*(B2:B9))/SUM(lgc))

Adjust cell ranges

Modify the ranges A2:A9 (the column used to determine visible rows) and B2:B9 (the actual values to average) to match your specific dataset.

Calculate the result

Press Enter to apply the formula. Filter your table, and the average will dynamically update while ignoring any zeros in the visible data.

Use the LET and BYROW Functions to Exclude Zeros

Function Compatibility: The LET, BYROW, and LAMBDA functions are available in newer spreadsheet software versions. If you are using an older version, this formula will return an error.

Solution 2

Use SUBTOTAL with FILTER and INDIRECT

An alternative approach that identifies visible rows, replaces zeros with errors, and filters them out before calculating the average.

Calculate Subtotal Averages Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like LET, BYROW, and LAMBDA, allowing you to easily calculate subtotal averages that exclude zero values on filtered data. It offers a seamless experience for complex data analysis.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the data you want to average.
2. Apply data filters: Navigate to the 'Data' tab on the top ribbon and click 'Filter' to add dropdown arrows to your headers.
3. Enter the formula: In a blank cell below your data, input the dynamic array formula: =LET(lgc,BYROW(A2:A9,LAMBDA(a,SUBTOTAL(103,a)))*(B2:B9<>0),SUM(lgc*(B2:B9))/SUM(lgc))
4. View the accurate average: Press Enter. The cell will now display the average of the visible values, automatically excluding any zeros.

Fully compatible with Microsoft Excel formulas and functionsSupports dynamic arrays and modern functions seamlesslyLightweight, fast, and completely free for everyday data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard AVERAGE function include hidden rows?

The standard AVERAGE function calculates all cells within the specified range, regardless of whether they are visible or hidden by data filters. To exclude hidden rows, you must use the SUBTOTAL or AGGREGATE functions.

Can I use the AGGREGATE function to ignore zero values in a subtotal?

While the AGGREGATE function can ignore hidden rows and error values, it does not have a built-in option to specifically ignore zero values. You must combine it with other functions or use an array formula to achieve this.

What does the number 103 represent in the SUBTOTAL function?

In the SUBTOTAL function, the argument 103 calls the COUNTA function while explicitly instructing Excel to ignore manually hidden rows and filtered rows. This allows formulas to evaluate only the currently visible data.

Are the LET and BYROW functions supported in older Excel versions?

No, LET, BYROW, and LAMBDA are dynamic array functions introduced in newer versions like Microsoft 365 and Excel 2021. If you use an older version, you will need to rely on alternative helper columns or SUMPRODUCT formulas.

Excel & Data Problems