Microsoft Excel Functions & Formulas

Use the SUBTOTAL function to sum a

filtered list

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 sub-

total ...