Summarizing Filtered Data

Analyzing the most vital data is important, but there are limitations to summarizing filtered data using functions like SUM and AVERAGE. One limitation is that these functions don't adjust their calculations if some rows are hidden by a filter.

Excel offers two ways to summarize only the visible cells in a filtered list. The first method is AutoCalculate. To use AutoCalculate, select the cells you want to summarize. Excel displays the average, sum, and count of the visible cells in the selection on the status bar at the lower edge of the window.

AutoCalculate isn't limited to sum, average, and count. You can add or remove calculations as needed; a check mark appears next to a function's name if its result is shown on the status bar.

However, AutoCalculate doesn't make the result available in the worksheet. Formulas like =SUM(C1:C9) always consider every cell in the range, regardless of hidden rows. To summarize only visible values, use the SUBTOTAL or AGGREGATE function.

The SUBTOTAL function allows you to choose whether to summarize all values in a range or only those in visible rows. Its syntax is =SUBTOTAL(function_num, ref1, ref2, ...). The function_num argument specifies the operation to summarize your data. The ref1, ref2, etc., arguments represent up to 29 ranges to include in the calculation.

For example, if you manually hide rows 10-16, the formula =SUBTOTAL(9, C3:C16, E3:126, G3:G16) sums all values in the ranges, including hidden rows. The formula =SUBTOTAL(109, C3:C16, E3:E16, G3:G16) sums only the visible values, ignoring manually hidden rows.

Place your SUBTOTAL formula in a row even with or above the headers in the filtered range to avoid hiding the formula's result.

The following table lists the summary operations available for the SUBTOTAL formula. Excel displays the available summary operations as part of the Formula AutoComplete functionality, so you don’t need to remember the operation make_numbers_easy_to_read.mdx

As the preceding table shows, the SUBTOTAL function has two sets of operations. The first set (operations 1–11) includes hidden values in their summary, while the second set (operations 101–111) summarizes only visible values. Operations 1–11 summarize all cells in a range, regardless of hidden rows. In contrast, operations 101–111 ignore values in manually hidden rows. However, the SUBTOTAL function does not adjust its result for rows hidden by a filter.

Excel treats the first cell in the data range as a header cell, so it doesn’t consider it when building the list of unique values. Be sure to include the header cell in your data range!

The AGGREGATE function extends the capabilities of the SUBTOTAL function. It allows you to select from a broader range of functions and use another argument to determine which values to ignore in the calculation. The AGGREGATE function has two possible syntaxes, depending on the summary operation you select. The first syntax is =AGGREGATE(function_num, options, ref1…), similar to the SUBTOTAL function. The second syntax, =AGGREGATE(function_num, options, array, [k]), is used for LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, and QUARTILE.EXC operations.

The following table summarizes the summary operations available for use in the AGGREGATE function.

You use the second argument, options, to select which items the AGGREGATE function should ignore. These items can include hidden rows, errors, and SUBTOTAL and AGGREGATE functions. The following table summarizes the values available for the options argument and the effect they have on the function’s results.

Change the AutoCalculate summaries displayed on the status bar

• Click on Customize Status Bar

• Click a summary operation without a check mark to display it.