Form correct sum, even with filtered data in Excel

In this tutorial, you will learn how to correctly calculate sums in Excel even with filtered data. Users often believe that the regular SUM formula can also be used with filtered data. However, this is not the case - Excel still shows you the sum of all values, regardless of the filter settings. In this tutorial, I will show you an alternative method, the so-called Subtotal function, which solves this problem.

Key Takeaways

• The regular SUM function does not take into account filtered data.
• With the SUBTOTAL function, you can calculate correct sums even when using filters.

Step-by-Step Guide

First, you should ensure that you have the data in a suitable format to apply filters. If you haven't created a table yet, convert your data range into a table. You can do this by pressing Ctrl + T.

Once you have created the table, you can enable the filter option. Simply click on the arrow in the column header to make filter adjustments. For example, you may want to display only BMW vehicles.

After setting the filter, you will see that only the BMW vehicles are visible in your table. However, the sum formula you used previously remains the same. Here lies the problem - the sum now includes the hidden vehicles as well.

To calculate the sum correctly, you need to use the SUBTOTAL function. You can do this by entering =SUBTOTAL(9; into a cell. The argument 9 stands for the SUM function.

Now you need to select the entire range of values you want to sum. You can do this simply by selecting the entire range of the price column.

After closing the parenthesis, press Enter. The cell will now show you the sum of the visible BMW vehicles only. You can also try this method for other types of vehicles.

If you apply a different filter, for example, for Audi, you can use the same formula and it will show you the correct sum. It makes your work easier when you keep in mind that the regular SUM does not work with filtered data.

So now you have learned that you should not use the SUM function when filters are active. Instead, the SUBTOTAL function is the correct choice to obtain precise results.

Summary

In this tutorial, you have learned how to achieve correct sums with filtered data in Excel using the SUBTOTAL function. By applying this special function, you avoid errors in your calculations and work more efficiently with your data.