How to Use SUBTOTAL in Excel

SUBTOTAL totals a range while skipping other SUBTOTALs inside it — so group subtotals and a grand total never double count.

You will learn to

• Write a group subtotal
• Write a grand total that ignores the subtotals
• Use a different SUBTOTAL function number

How it works

SUBTOTAL(function_num, ref1, …) applies a summary function such as SUM (9), AVERAGE (1), COUNT (2) or MAX (4) to a range — and skips any cells in that range that are themselves SUBTOTAL formulas. That means you can put a subtotal under each group and a grand total over the whole column without double counting.

The function numbers 101–111 do the same but also ignore rows you have hidden manually; 1–11 ignore only filtered-out rows. A SUM over the same column would add every subtotal again and overstate the total.

=SUBTOTAL(9,B2:B12)