Use SUBTOTAL to Run Calculations on Filtered Data

A standard Excel formula like SUM will not be updated when a filter is applied to a worksheet.

If you want your calculations for a worksheet to change each time filters are set, use the SUBTOTAL function.

The SUBTOTAL function makes reference to multiple Excel formulas with numerical codes. Code 109 is for the SUM formula; 101 for AVERAGE;104 for MAX; 103 for COUNTA. Follow the code with the full range of data in a column. For example, =SUBTOTAL(109,L2:L22) or =SUBTOTAL(103,L2:L22)

When the user filters the worksheet, only the calculation for the displayed rows will be returned.