6. Tables, Sorting and Filtering
6.7 SUBTOTAL and AGGREGATE
=SUBTOTAL(function_num, ref1, …) – calculates only visible rows after filtering.
| function_num (includes manually hidden rows) | function_num (ignores manually hidden rows) | Function |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 9 | 109 | SUM |
Filtered-out rows are always ignored; the 100-series also ignores rows you hid manually.
=AGGREGATE(function_num, options, ref1, …) – like SUBTOTAL with more functions (19, e.g. 14 = LARGE, 15 = SMALL) and options to ignore errors:
| options | Ignores |
|---|---|
| 5 | hidden rows |
| 6 | error values |
| 7 | hidden rows and error values |
| Formula | Result |
|---|---|
=SUBTOTAL(109,tblOrders[Amount]) |
Sum of visible orders |
=SUBTOTAL(103,tblOrders[Order ID]) |
Count of visible orders |
=AGGREGATE(9,6,D2:D100) |
Sum ignoring #N/A/#DIV/0! |
=AGGREGATE(14,6,tblOrders[Amount],2) |
2nd largest amount, ignoring errors |
Worked example – dynamic heading. ="Showing "&SUBTOTAL(103,tblOrders[Order ID])&" orders worth ₹"&TEXT(SUBTOTAL(109,tblOrders[Amount]),"#,##0") updates as the user filters.
Ravindra Bagale's Tip
Filtered data cha total =SUM() ne kadhla tar lapleli rows pan yetat – khup students chukicha total report kartat. Filter asel tar SUBTOTAL(109,…) vapra. Ani column madhe errors asle tar SUM la #N/A yeto – AGGREGATE(9,6,…) vapra.
Practice task
Build a filter-aware summary box above tblOrders: visible order count, visible sales and visible average delivery time. Add an AGGREGATE total for a column that contains errors.