The remaining formulas in the list summarize only the visible data. The SUMPRODUCT function, can return the sum of a column, just like the SUM function.

But I couldn’t have specified the other columns because, unlike the SEARCH function, the SUMIFS function can’t use wildcards with numeric data.) =SUMIFS(sum_range, criteria_range, criteria, …)īecause the SUMIFS function can use wildcards, the asterisk (“*”) tells the formula to return the sum of the Sales column for all Products. This formula is a simple sum of the Sales column in the table, which is named Table1:į14: =SUMIFS(Table1, Table1, “*”) Formulas 1-3 ignore the fact that the table is filtered, and they return the grand total of the Sales data… The list below the table illustrates different ways we can sum data in a filtered table. In Excel 20, I could filter the table only by using the filter dropdowns at the top of each column. Because I created the figure in Excel 2013, I could include the slicers shown at the right of the figure. The following figure shows a filtered Table.

Summing All the Data in Filtered Excel Tables But now, I’ve figured out how to expand our calculation options considerably with filtered Tables. However, until now, we’ve had few ways to summarize the unhidden data in filtered Tables. (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. Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table.Īnd slicers for Tables, introduced in Version 2013, make Table filtering much easier to do.

Excel how many rows after filter