Chapter 17 · 17.18
Add up only the filtered rows, even with an extra condition
You filter a sales list on one category, but the total below it still shows the amount for all rows. That is correct: SUM also adds up the rows the filter hides. If you only want to add up what you see, use SUBTOTAL. And if you want an extra condition within the filtered rows, or want to skip error values, there are two more handy formulas.
As an example, we use the sales list of Bloemisterij Het Zonnetje: the date in A, the product in B, the category in C and the amount in D, from row 2 to row 300. There is a filter on the header row (see Sort and filter).
Where do you put the formulas?
Not below the list: a formula directly below the list can itself become hidden when you filter, and move along when you sort. But not just anywhere next to the list either: a filter hides entire rows, so a formula in F3 disappears too when row 3 is filtered out. So put them in the header row (row 1, next to the headings), or on a separate worksheet. In the example they are in row 1, from column F onwards.
The sum of the visible rows
=SUBTOTAL(109,D2:D300)
The first number chooses the calculation. 9 is the sum, and so is 109, with one difference: 109 also skips rows you have hidden manually (right-click, Hide). 9 only skips rows hidden by the filter. So use 109 if you want to add up what you see.
| Calculation | Filtered | Also manually hidden |
|---|---|---|
| Sum | 9 | 109 |
| Number of non-empty cells | 3 | 103 |
| Average | 1 | 101 |
| Maximum | 4 | 104 |
You count the number of visible rows with =SUBTOTAL(103,B2:B300).

Visible and an extra condition
You have filtered on the category Boeketten (bouquets) and within that you only want to add up the funeral arrangements (product Rouwstuk). SUMIFS ignores the filter, and SUBTOTAL has no condition. Online you will find a formula for this using SUMPRODUCT, SUBTOTAL and OFFSET. In Euro-Office, that gives the error value #NUM!. With a helper column it does work, and more simply:
- Add a column Visible next to the list, here column E. Select E2:E300, type =SUBTOTAL(103,D2) and press Ctrl+Enter. Each row gets a 1 as long as it is visible, and a 0 when the filter hides it.
- Add up with SUMIFS and include the helper column as an extra condition: =SUMIFS(D2:D300,B2:B300,"Rouwstuk",E2:E300,1).
If you change the filter, the helper column and the sum recalculate straight away.
Skipping error values
If there is a #N/A or #DIV/0! somewhere in the column, SUM and SUBTOTAL also return an error value. AGGREGATE can skip errors:
=AGGREGATE(9,7,D2:D300)
The 9 is the sum again, and the 7 means: skip hidden rows and error values.
Pitfalls
- Hidden columns are still included: SUBTOTAL only looks at hidden rows.
