Excel Function

SUBTOTAL

Returns a subtotal with specified aggregate function.

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

Arguments

function_num

Required

SUBTOTAL operation code: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV.S, 8=STDEV.P, 9=SUM, 10=VAR.S, 11=VAR.P. Use 101-111 for the same operations while excluding manually hidden rows.

ref1

Required

Specify a reference range. You can drag or click start/end cells.

Practice Preview

Use SUBTOTAL to subtotal Sales in column D with 109 (SUM / exclude manually hidden rows), excluding the manually hidden row.

Example Table

#	AProduct ID	BProduct Name	CCategory	DSales
2	P001	Apple	Food	1200
3	P002	Banana	Food	980
4	P003	Notebook	Stationery	450
5	P004	Pen	Stationery	300
6	P005	Grapes	Food	1500
7	P006	Ruler	Stationery	250
8	P007	Peach	Food	860

How to Use (Formula)

=SUBTOTAL(109, D2:D8)

Result

4560

Practice This Function

Start Function Practice Back to Function List

SUBTOTAL FAQ

How do I use SUBTOTAL in Excel?

Use SUBTOTAL when you want to summarize filtered data. Enter Function number, Reference range in that order, then add optional arguments when needed.

Where can I practice SUBTOTAL?

Use the "Start Function Practice" button to jump straight into SUBTOTAL drills.

What arguments does SUBTOTAL use?

The main arguments are Function number, Reference range. Enter them in order.