How to Sum Filtered Rows in Excel

To sum filtered rows in Excel, you can use the SUBTOTAL function, which is specifically designed to work with filtered data. The SUBTOTAL function can perform various calculations, including SUM, AVERAGE, COUNT, and more on a set of filtered data.

Here's how to sum filtered rows using the SUBTOTAL function in Excel:

1. First, apply a filter to your data by selecting the range of data you want to filter, and then click on the "Data" tab in the Excel toolbar. Click on the "Filter" button to apply the filter to the selected range.
2. Set the filter criteria by clicking on the drop-down arrow in the header of the column you want to filter. Choose the criteria you want to apply, and Excel will hide the rows that don't meet your criteria.
3. In an empty cell, enter the SUBTOTAL function to sum the filtered data. Use the formula =SUBTOTAL(9, range), where range is the range of cells you want to sum. The number 9 in the formula represents the SUM function in the SUBTOTAL function.

For example, if you want to sum the filtered data in cells A2:A20, you would enter the formula =SUBTOTAL(9, A2:A20).

4. Press Enter to get the sum of the filtered rows. The SUBTOTAL function will only sum the visible rows in the specified range, ignoring the hidden rows.

##Example

Let's assume you have a dataset of monthly sales in column A, and you only want to sum the sales for months with sales above $1,000.

1. Apply a filter to the data by selecting the range A2:A13 and clicking on the "Data" tab, then "Filter."
2. Set the filter criteria by clicking on the drop-down arrow in the header of column A. Choose "Number Filters" > "Greater Than," and enter 1000 in the field. Click "OK," and Excel will hide the rows with sales less than or equal to $1,000.
3. In an empty cell, enter the SUBTOTAL function: =SUBTOTAL(9, A2:A13).
4. Press Enter, and you will get the sum of the filtered rows, which only includes the sum of monthly sales greater than $1,000.