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.
Excel Function
SUBTOTAL
Returns a subtotal with specified aggregate function.
SUBTOTAL(function_num, ref1, [ref2], ...)Arguments
function_num
RequiredSUBTOTAL 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
RequiredSpecify a reference range. You can drag or click start/end cells.
Practice Preview
Use SUBTOTAL to calculate a subtotal for Sales in column D with 9 (SUM). You can set ranges by dragging from the start cell to the end cell in the same argument field.
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(9, D2:D8)Result
5540
Practice This Function
SUBTOTAL FAQ
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.