How to sum only the visible rows after filtering

SUM adds every cell in a range, hidden or not, so it can't do this on its own. Grid by Zaweba has two functions that can: SUBTOTAL, which ignores rows hidden by a filter, and AGGREGATE, which can also skip hidden rows and error values. Use either and the total follows the filter as you change it.

Using SUBTOTAL

SUBTOTAL takes a function number and a range. The number chooses the calculation: 9 is a sum, 1 an average, 2 a count of numbers, 4 a maximum, 5 a minimum, and so on. Put =SUBTOTAL(9, and the column you want, below or above your data, then filter the list. The total changes to match the rows left showing.

Rows hidden by a filter are always left out. Rows you hide by hand are a separate case. The function numbers from 101 upwards do the same jobs but also ignore rows you've hidden yourself, so 109 is a sum of only the rows you can see, however they were hidden.

SUBTOTAL also ignores other SUBTOTAL formulas in its range. That makes it safe to put subtotals for each group in a list and a grand total at the bottom, without counting anything twice.

Using AGGREGATE

AGGREGATE is the more capable cousin. Like SUBTOTAL it takes a function number, but it also takes an options number that says what to ignore: hidden rows, error values, nested subtotals, or combinations of them. Its list of calculations is longer too, including large and small values and percentiles.

The case where AGGREGATE really helps is a column with an error in it. A single #N/A breaks a SUM and a SUBTOTAL alike. AGGREGATE set to ignore errors carries on and totals everything else, which is handy in a sheet fed by lookups that sometimes miss.

Tables make it easier

Turn your data into a table and switch on the total row. The total row uses SUBTOTAL for you, so when you filter the table using the drop-downs in its header row, the totals follow. Each total cell has a choice of calculation, and structured references keep the formulas readable.

Filtering step by step

Click inside the data and switch on AutoFilter, or use the table's own drop-downs. Each column header gets a drop-down with a search box, a checklist of values, text, number and date filters, top 10, and above or below average. Choose what you want to see and the other rows hide. Your SUBTOTAL or AGGREGATE result updates straight away.

For a quick check without any formula, select the visible cells in a column and look at the status bar. Grid shows the Average, Count and Sum of the selection there.

Where filtered totals go wrong

Putting the total inside the filtered range. If the total row is part of the list, filtering can hide it. Keep it above the header or below the data, or use a table's total row, which stays put.

Using SUM out of habit. A SUM under a filtered list looks right until someone filters, and then quietly gives the total for everything.

Mixing the 9 and 109 styles in one sheet. Decide whether manually hidden rows should count, and use the same choice throughout so different totals agree.

Both functions sit in Grid by Zaweba's Maths and Trig family, and they follow the same rules as other spreadsheets, so a workbook that relies on them gives the same answers when you share it as an .xlsx.