How to Make Excel Subtract and Add Only Filtered Values

Excel is a powerful tool for data analysis and calculations. One common task is to perform calculations on a subset of data by applying filters. However, by default, Excel includes hidden and filtered values in calculations, which can lead to inaccurate results. In this article, we will show you how to make Excel subtract and add only filtered values.

Using the SUBTOTAL Function

The SUBTOTAL function is a versatile tool that allows you to perform calculations on a range of data, excluding hidden and filtered values. To use this function for subtraction and addition, follow these steps:

1. Select an empty cell where you want the result to appear.
2. Enter the formula =SUBTOTAL(9, range) to add the filtered values or =SUBTOTAL(109, range) to subtract the filtered values.
3. Replace range with the actual range of cells you want to include in the calculation. For example, if you want to add the values in column A, you would use =SUBTOTAL(9, A:A).
4. Press Enter to get the result.

By using the SUBTOTAL function, you can ensure that only the visible, filtered values are included in your calculations.

Using the AGGREGATE Function

The AGGREGATE function is another powerful tool that allows you to perform calculations on a range of data, excluding hidden and filtered values. To use this function for subtraction and addition, follow these steps:

1. Select an empty cell where you want the result to appear.
2. Enter the formula =AGGREGATE(9, 7, range) to add the filtered values or =AGGREGATE(10, 7, range) to subtract the filtered values.
3. Replace range with the actual range of cells you want to include in the calculation. For example, if you want to add the values in column A, you would use =AGGREGATE(9, 7, A:A).
4. Press Enter to get the result.

The AGGREGATE function offers more flexibility than the SUBTOTAL function as it allows you to specify which types of values to ignore, such as hidden or error values.

Using a Helper Column

If you prefer a non-formula approach, you can use a helper column to calculate the filtered values and then perform the desired subtraction or addition. Here's how:

1. Add a new column next to the column you want to calculate.
2. In the first cell of the helper column, enter the formula =SUBTOTAL(3, range) to count the visible cells or =SUBTOTAL(109, range) to sum the visible cells.
3. Drag the formula down to fill the helper column with the respective calculations for each row.
4. In a separate cell, use the SUM function to add or subtract the values in the helper column as needed. For example, if the helper column is column B, you would use =SUM(B:B) to add all the values.

This method allows you to see the individual calculations for each row and provides more control over the process.

Conclusion

By using the SUBTOTAL or AGGREGATE functions, or by creating a helper column, you can make Excel subtract and add only filtered values. These methods ensure that your calculations are based on the visible data, excluding any hidden or filtered values that could skew the results. Choose the method that suits your needs and start performing accurate calculations in Excel today!

References