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.
![](https://static.wixstatic.com/media/af7fa4_c22a5c379ba946a0a899626fbd1c3ed8~mv2.png/v1/fill/w_980,h_245,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/af7fa4_c22a5c379ba946a0a899626fbd1c3ed8~mv2.png)
If you want your calculations for a worksheet to change each time filters are set, use the SUBTOTAL function.
![](https://static.wixstatic.com/media/af7fa4_3a96803f14fd41c9a6a8920927413c3a~mv2.png/v1/fill/w_980,h_376,al_c,q_90,usm_0.66_1.00_0.01,enc_auto/af7fa4_3a96803f14fd41c9a6a8920927413c3a~mv2.png)
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)
![](https://static.wixstatic.com/media/af7fa4_3b6ad0b6c03a4d1b8ba316f38ce7d180~mv2.png/v1/fill/w_824,h_356,al_c,q_85,enc_auto/af7fa4_3b6ad0b6c03a4d1b8ba316f38ce7d180~mv2.png)
![](https://static.wixstatic.com/media/af7fa4_81146b06ab8c40d29a01f302b8733c4e~mv2.png/v1/fill/w_825,h_240,al_c,q_85,enc_auto/af7fa4_81146b06ab8c40d29a01f302b8733c4e~mv2.png)
When the user filters the worksheet, only the calculation for the displayed rows will be returned.