How to Sum Filtered Cells in Microsoft Excel

Microsoft Excel is an essential tool for managing and analyzing data, but many users struggle with summing filtered cells efficiently. Whether you’re working with large datasets or simply need to focus on specific information, knowing how to sum filtered cells can save you time and effort.

How to Sum Filtered Cells in Microsoft Excel

Summing filtered cells in Microsoft Excel is a valuable skill that can enhance your data analysis capabilities. By using specific functions, you can quickly and accurately sum only the visible cells in your filtered dataset. Here are the steps to do it efficiently:

• Step 1. Filter Your Data

First, select the range of cells you want to filter. Go to the “Data” tab on the Ribbon and click “Filter.” This will add drop-down arrows to your column headers. Use the drop-down arrows to filter your data as needed.

• Step 2. Select the Cell for Your Sum

Next, click on the cell where you want the sum of the filtered data to appear.

• Step 3. Use the SUBTOTAL Function

Type =SUBTOTAL(9, into the selected cell. Highlight the range of cells you want to sum. For example, if you’re summing column B from row 2 to row 50, your formula will look like =SUBTOTAL(9, B2:B50). Press Enter. The SUBTOTAL function will sum only the visible (filtered) cells.

• Step 4. Verify the Results

Check the cell with your SUBTOTAL formula to ensure it displays the correct sum based on your filtered data.

You may also find valuable insights in the following articles offering tips for Microsoft Excel:

Note

FAQs

How do I sum only visible cells in Excel?

Use the SUBTOTAL function with the appropriate function number, such as =SUBTOTAL(9, range).

Can I use the SUM function to sum filtered cells?

No, the SUM function sums all cells in a range, including hidden ones.

What is the difference between SUBTOTAL and AGGREGATE functions?

SUBTOTAL is simpler and mainly for filtered data, while AGGREGATE offers more options and can ignore errors.

Will the SUBTOTAL function update if I change my filter?

Yes, SUBTOTAL automatically updates when you change the filter.

Can I sum filtered cells across multiple sheets?

Yes, but you need to use a more complex formula or VBA for multi-sheet operations.