Use the SUBTOTAL function to sum a filtered list - Basic Statistical Functions - Microsoft Excel Tutorials

Use the SUBTOTAL function to sum a filtered list using Microsoft Excel

When using the Filter option in the Data menu, it is not advisable to use the SUM function to sum up filtered rows because it sums up all rows, including those that are hidden. Instead, use the SUBTOTAL function to get the subtotal of a list or database that is visible.

SUBTOTAL(function_num, ref1, ref2, ...)

function_num: A number from 1 to 11 that specifies a particular function to use for calculating subtotals. (1 = AVERAGE, 2 = COUNT, 3 = COUNTA , 4 = MAX, 5 = MIN, 6 = PRODUCT, 7 = STDEV, 8 = STDEVP, 9 = SUM, 10 = VAR, and 11 = VARP)

ref1, ref2, ...: From 1 to 29 ranges or references for which a subtotal is desired.

To sum a filtered list:

1. In cells B2:B10 type group numbers from 1 to 3.
2. In cells C2:C10 type the daily sales for each group.
3. From the Data menu, select Filter | AutoFilter.
4. Select group 1 in the column B filter.
5. Select cell C12 and type the following formula: =SUBTOTAL(9,C2:C10).

Use the SUBTOTAL function to sum a filtered list