Typically, when you apply filters to a range or table in a spreadsheet, the downstream formulas do not filter in sync. To filter formulas in sync with source data, you can use the tilde operator (~) in Row Zero. The ~ is added right before the cell reference in the formula (e.g. ~A:A will exclude filtered and hidden rows when calculating formulas). This works similar to the SUBTOTAL function, but is more flexible and can be applied to any function or formula. View an example of filtering formulas here in Row Zero, an enterprise-grade spreadsheet for big data.
View example of filtering formulas
- How to filter downstream formulas
- Filtering with SUBTOTAL function
- Filtering downstream charts and pivot tables
How to filter downstream formulas
Typically when you filter data, any formulas that reference that data do not incorporate the applied filter, as shown below.
To apply filters to formulas and functions, add the tilde operator ~ right before the cell reference in the formula (e.g. =SUM(~A2:A6) in the example below) This will dynamically filter formulas in sync with source data and is a feature unique to Row Zero.
The tilde operator works like the SUBTOTAL function, but can easily be applied to any formula or function.
For example, SUM(~A2:A6) is equivalent to SUBTOTAL(9, A2:A6). However the SUBTOTAL function is limited to 11 preset functions, whereas you can use ~ to subtotal any function and dynamically filter any formula in your spreadsheet.
Filtering with SUBTOTAL function
The SUBTOTAL function applies calculations in sync with filters applied by ignoring filtered out rows. The function takes two arguments:
- function_num - a function number that determines the function to use for the subtotal. The function numbers are:
- AVERAGE
- COUNT
- COUNTA
- MAX
- MIN
- PRODUCT
- STDEV
- STDEVP
- SUM
- VAR
- VARP
- values - the values to subtotal
SUBTOTAL Example
Below is a SUBTOTAL function example using the formula =SUBTOTAL(9, A2:A6)
As you can see, the SUBTOTAL result changes as you filter the source data.
SUBTOTAL any function
In Row Zero, you can use ~ to subtotal any function, so you can easily subtotal COUNTIF, AVERAGEIF, MEDIAN, etc.
Below we show how to subtotal COUNTIF using "~". Simply add a ~ before the cell reference in your COUNTIF formula to dynamically filter COUNTIF in sync with source filters.
You can also subtotal formulas without functions. For example =~A:A sums column A with filters applied.
Filtering downstream charts and pivot tables
In Row Zero, when you filter a cell range or data table, any downstream charts and pivot tables also filter in sync with their source data. This is also true for slicers and pivots of pivots.
Note that sorting source data will also change the sort or order of the X-axis in charts if the data is a number or text and not a date or time - dates and times always chart in chronological order.
Conclusion
In Row Zero, you can use the ~ operator to dynamically filter formulas in sync with source data. The ~ operator is added right before the cell reference and works like SUBTOTAL for any function or formula. Row Zero also supports the SUBTOTAL function for Excel compatibility, but it is limited to 11 preset functions. While you can also use SUBTOTAL in Excel and Google Sheets, the ~ operator is unique to Row Zero. View live examples of filtering formulas in a spreadsheet here or try Row Zero for free to get started.