Question

How to sum only filtered cells in excel?

Answer 1

In Excel, to sum only filtered cells, use the SUBTOTAL function with the argument 109 followed by the range of cells you want to sum. This function updates automatically if the filters or data change.

Step-by-step explanation:

To sum only filtered cells in Excel, you can use the SUBTOTAL function. This function provides a way to perform calculations, like summing, on only those cells that are visible after a filter has been applied.

Here's a step-by-step explanation:

1. First, apply your filters to your data as needed.
2. Next, click on the cell where you want the sum to appear.
3. Type in =SUBTOTAL(109, range) where 'range' represents the cells you want to sum.
4. The '109' in the SUBTOTAL function signifies that you want to sum the filtered cells. If you use '9' instead, it sums all cells, not just the filtered ones.
5. Hit Enter, and Excel will display the sum of only the visible cells in the selected range.

If the data changes or if you adjust the filters, the SUBTOTAL function will automatically update to reflect the sum of the newly visible cells.