Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table.
And slicers for Tables, introduced in Version 2013, make Table filtering much easier to do. (Excel 2010 introduced slicers for Pivot Tables.) With Table slicers, you can control your Table’s filtering in a Report worksheet while your Table is parked out of the way in a Data worksheet in the same workbook.
However, until now, we’ve had few ways to summarize the unhidden data in filtered Tables. But now, I’ve figured out how to expand our calculation options considerably with filtered Tables.
Summing All the Data in Filtered Excel Tables
The following figure shows a filtered Table. Because I created the figure in Excel 2013, I could include the slicers shown at the right of the figure. In Excel 2007 and 2010, I could filter the table only by using the filter dropdowns at the top of each column.
The list below the table illustrates different ways we can sum data in a filtered table. Formulas 1-3 ignore the fact that the table is filtered, and they return the grand total of the Sales data…
This formula is a simple sum of the Sales column in the table, which is named Table1:
F13: =SUM(Table1[Sales])
This formula uses the SUMIFS function:
F14: =SUMIFS(Table1[Sales], Table1[Product], “*”)
The syntax of the SUMIFS function is:
=SUMIFS(sum_range, criteria_range, criteria, …)
Because the SUMIFS function can use wildcards, the asterisk (“*”) tells the formula to return the sum of the Sales column for all Products. (I also could have specified Region. But I couldn’t have specified the other columns because, unlike the SEARCH function, the SUMIFS function can’t use wildcards with numeric data.)
F15: =SUMPRODUCT((Table1[Sales]))
The SUMPRODUCT function, can return the sum of a column, just like the SUM function.
The remaining formulas in the list summarize only the visible data.
Built-In Ways to Sum Only Visible Data in Filtered Excel Tables
Formulas 4 and 5 use Excel functions with the built-in ability to ignore hidden rows.
F16: =SUBTOTAL(9, Table1[Sales])
The SUBTOTAL function was designed to work with filtered data. It automatically ignores data in all filtered rows. It has this syntax:
=SUBTOTAL(function_num, ref1, …)
In the formula in cell F16, the function number 9 tells the SUBTOTAL function to return the SUM of the Sales data.
F17: =AGGREGATE(9, 5, Table1[Sales])
The AGGREGATE function works like an advanced version of the SUBTOTAL function. It has two versions. This is the syntax for the reference-form version used here:
AGGREGATE(function_num, options, ref1, …)
In cell F17, as with SUBTOTAL function, the 9 tells the function to return the SUM. Option 5 in the second argument tells the function to ignore hidden rows. And, of course, the ref1 argument in cell F17 tells the function to return the sum of the table’s Sales column.
How to Set Up a Column that Reports Whether Its Row Is Visible
Look again at the table first shown above:
The Visible column is key to how the last three formulas are calculated. The formulas in that column return 1 if the row is visible and 0 if it’s not.
The formula in this column is the trick that allows the remaining formulas to do their job. To my knowledge, this is the only way—without using macros—to write a formula that indicates whether its row is visible.
Here’s the formula for the cell shown:
F13: =(AGGREGATE(3, 5, [@Sales])>0)+0
Here’s how it works:
The number 3 in the first argument tells Excel to use the COUNTA function. The number 5 in the second argument, tells Excel to ignore values in hidden rows. (This argument is key, because if the row is hidden, we want AGGREGATE to ignore any counts it might find.) And the third argument points to the values we care about…the Sales column, in this case.
The formula then tests whether the result is greater than zero. If the result is TRUE, the row is visible; if it’s FALSE, it’s not visible. Finally, the formula adds 0 to this logical result, which converts TRUE to 1 and FALSE to 0.
How to Use the Visible Column with Summary Functions
Here’s the first formula that relies on the Visible column.
F18: =SUMPRODUCT((Table1[Sales])*(Table1[Visible]=1))
This formula uses the SUMPRODUCT to return the sum of the visible rows in Table1. It does this by multiplying each cell in the Sales column by the corresponding cell in the Visible column, then summing the result. Because hidden columns have a zero value, SUMPRODUCT returns the sum of only the visible cells.
Because SUMPRODUCT typically acts like an array formula, the (Table1[Sales]) section of this formula could be as sophisticated as you want. That is, you could perform many complex calculations on each cell in the Sales column. You could multiply it by other columns, square it, apply other worksheet functions to it, and so on…and then sum the results.
F19: =SUMIFS(Table1[Sales],Table1[Visible],1)
This formula merely sums the visible cells in the Sales column. If your table has many rows, this is the preferred method, because the SUMIFS function calculates more quickly than SUMPRODUCT does.
F20: {=SUM(IF(Table1[Visible],Table1[Sales]))}
This array formula works about like the SUMPRODUCT version. You type it in as you normally would (without the braces), then hold down Ctrl and Shift before you press Enter. After you do so, the braces appear in your formula bar as shown.
Note that if your version of Excel supports dynamic arrays, a feature introduced to Excel 365 in 2020, you can enter this formula naturally.
The advantage of having an array formula available in your Excel toolkit is that you can calculate results on filtered data that you couldn’t calculate any other way.
For example, suppose you want to find the growth rate in sales for the visible data. You could use either of these formulas to perform that calculation:
{=SLOPE(IF(Table1[Visible],Table1[Sales]),Table1[Date])}
{=SLOPE(Table1[Sales],IF(Table1[Visible],Table1[Date]))}
These SLOPE function formulas have two unusual characteristics. First, the IF functions have only two arguments, not three. Because we don’t provide a value to return if the test is false, the functions return FALSE instead…which is just what we want.
Second, notice that the first version used IF in the first argument and the second used IF in the second argument. But they return the same result in all cases.
Here’s one final example:
{=STEYX(Table1[Sales],IF(Table1[Visible],Table1[Date]))}
This array formula returns the Standard Error of the Estimate (essentially, the standard deviation around a trend line) for the visible cells.
In short, using the Visible column in your filtered tables gives your formulas a lot more power. Give it a try!
