When you’ve filtered data in Excel, the SUM function still sums cells even if they’re not visible. To SUM only the visible data, you can use the SUBTOTAL function. SUBTOTAL ignores hidden rows and columns.
In this example, there are 2156 rows of data that are filtered so that only those rows whose City is ‘Paris’ are shown.
The formulas below the filtered data are:
C2159 - =SUM(C2:C2157)
D2159 - =SUM(D2:D2157)
C2160 - =SUBTOTAL(9,C2:C2157)
D2160 - =SUBTOTAL(9,D2:D2157)
You can do more than just sum with SUBTOTAL. The first argument (9) is what tells SUBTOTAL to sum. Look up SUBTOTAL_worksheet_function in help to see the other possible arguments.