Excel Guides

Performing Calculations while Filtering in Excel

When filtering data in Excel, it is often useful to be able to perform calculations on the visible data only. This can be done using a simple formula, or by using the SUBTOTAL function.

To use a formula, first select the cells that you want to perform the calculation on. Then, in the formula bar, type =SUM(A1:A10), replacing A1:A10 with the cell range that you want to sum. This will give you the sum of all visible cells in that range.

If you want to use the SUBTOTAL function, first select the cells that you want to perform the calculation on. Then, go to Data > Filter. In the drop-down menu next to "Show rows where:", choose "Custom...". In the "Criteria" box, type SUBTOTAL(9,A1:A10). This will show only the visible cells in that range.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.