Ignore filtered cells using SUBTOTAL
The SUBTOTAL function applies a function (see Table 8) to a range of data, but it ignores cells hidden by a filter and cells that already contain a SUBTOTAL. For example, =SUBTOTAL(2, "B2:B16") counts the number of cells in B2:B16 that are not hidden by a filter.
Table 8. Function index for the SUBTOTAL function.
| Function index
| Function
|
| 1
| AVERAGE
|
| 2
| COUNT
|
| 3
| COUNTA
|
| 4
| MAX
|
| 5
| MIN
|
| 6
| PRODUCT
|
| 7
| STDEV
|
| 8
| STDEVP
|
| 9
| SUM
|
| 10
| VAR
|
| 11
| VARP
|
 | Do not forget that the SUBTOTAL function ignores cells that use the SUBTOTAL function. I have a spreadsheet that tracks investments. My retirement investments are grouped together with a subtotal. The same is true of my regular investments. I can use a single subtotal that includes the entire range without worrying about the subtotal cells.
|
