How to Sum Only Filtered Cells in Microsoft Excel

Microsoft Excel is a popular spreadsheet software that is widely used for creating, managing and analyzing data. Often, you may find yourself needing to filter your data to get a more concise view of your data. However, when you apply filters, the SUM functions that you use may include both the visible and invisible, or filtered and unfiltered data. In this article, we will show you how to sum only filtered cells in Microsoft Excel.

Introduction to Filtering in Excel

Before we can delve into how to sum only filtered cells in Excel, it’s essential to understand the concept of filtering. Excel allows you to filter data in a spreadsheet by setting up conditions that hide the unwanted data and display only the required data. When you apply a filter, a dropdown menu is added to the header row. You can use this menu to sort, filter, and search for specific data in a column.

For instance, suppose you have a dataset that contains sales data for various products, and you only want to see the data for a specific product, such as “Product A.” You can filter the data to ensure that only the information related to “Product A” is visible while hiding the other data.

Why Summing Filtered Cells is Necessary

When you have a dataset with a lot of data, sometimes, you may want to get a quick summary of the dataset by adding up specific columns or rows. However, when you add up the data using the SUM function, Excel will include the hidden or filtered data, which tends to skew the results. By learning how to sum only filtered data, you can ensure that your calculations are more accurate and reliable.

Summing Only Filtered Cells in Microsoft Excel

There are a few methods that you can use to sum only filtered cells in Microsoft Excel. Some of these methods include:

Using Subtotal Function

Excel offers a Subtotal function that allows you to add up visible cells in the filtered list. To use this method, follow the steps below:

1. Ensure that your data is in a table format (Ctrl+T).
2. Click on any cell inside your data table.
3. Select the “Data” tab from the top menu.
4. Click on “Subtotal.” The “Subtotal” dialog box will appear.
5. In the “At each change in…” dropdown list, select the column that you want to filter and sum.
6. In the “Use function” dropdown list, select “Sum.”
7. In the “Add subtotal to” checkbox list, ensure that only the checkbox for the column that you want to sum is selected.
8. Click on “OK.”

You should now see the total of the filtered cells in the selected column.

Using SUMIF Function

The SUMIF function is another useful method in Excel that you can use to add up only filtered cells. Follow the steps outlined below:

1. Select a cell where you want to display the result.
2. Click on the “fx” function wizard button next to the formula bar.
3. In the “Search for a function” box, type “SUMIF.”
4. Click on “OK.”
5. In the “SUMIF” dialog box, enter the range of cells you want to sum in the “Range” box.
6. Click on the filter drop-down to open the Filter menu.
7. Select the “Filter” option next to “Select All,” and then select the criteria you want to filter.
8. In the “Sum_range” box, enter the same range of cells as you did in step 5.
9. Click on “OK.”

You should now see the total of the filtered cells in the selected range.

Using AGGREGATE Function

The AGGREGATE function in Excel is similar to the SUBTOTAL function, but it allows you to apply multiple functions, such as SUM, MAX, MIN, and others. Here’s how to use it:

1. Select a cell where you want to display the result.
2. Click on the “fx” function wizard button next to the formula bar.
3. In the “Search for a function” box, type “AGGREGATE.”
4. Click on “OK.”
5. In the “AGGREGATE” dialog box, enter “9” in the “Function_num” box.
6. Enter the range of cells you want to sum in the “Array” box.
7. In the “Options” box, enter “4” to ignore hidden rows and columns.
8. If you want to filter data based on specific criteria, you can also specify this in the third argument.
9. Click on “OK.”

You should now see the total of the filtered cells in the selected range.

Conclusion

Summing only filtered cells in Excel can be achieved using various methods, including SUBTOTAL, SUMIF, and AGGREGATE functions. By using these methods, you can ensure that you only add up visible cells in the filtered list, which can enhance the accuracy of your data analyses. We hope that this article has been helpful in guiding you through how to sum only filtered cells in Microsoft Excel.