Managing Data
Use a 'Visible' Column in Formulas to Ignore
Hidden Rows in
Filtered Tables
Although Excel provides two worksheet functions that ignore
filtered rows in a Table, nearly any function can ignore those
hidden rows if you use this new trick.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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 13 ignore the fact that
the table is filtered, and they return the grand total of the
Sales data:
F13: =SUM(Table1[Sales])
This formula is a simple
sum of the Sales column in the table, which is named Table1.
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.
BuiltIn Ways to Sum Only Visible Data in Filtered Excel Tables
Formulas 4 and 5 use Excel functions with the builtin
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 SUBTTOTAL
function. It has two versions. This is the syntax for the
referenceform 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
Method
2 of the SUMPRODUCT function 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 SUMIFS calculates more quickly than SUMPRODUCT
does.
F20: {=SUM(IF(Table1[Visible],Table1[Sales]))}
This array formula that 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.
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 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!
Take Your Next Steps
If you're looking for additional help with this topic, I can help you in
three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
Tags: #excel, #SUMIFS, #SUMPRODUCT, #SUM,
#AGGREGATE, #STEYX, #SLOPE, #COUNTA, #visible, #visible column, #database,
#Excel Table, #tables, #table slicers, #slicers, #filter
