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
If you calculate the total of filtered data with =SUM(), the hidden rows are included too – many students report the wrong total. If there is a filter, use SUBTOTAL(109,…). And if the column has errors, SUM gives #N/A – use AGGREGATE(9,6,…).
Ravindra Bagale's Tip – मराठी
Filtered data चा total =SUM() ने काढला तर लपलेल्या rows पण येतात – बरेच students चुकीचा total report करतात. Filter असेल तर SUBTOTAL(109,…) वापरा. आणि column मध्ये errors असतील तर SUM ला #N/A येतो – AGGREGATE(9,6,…) वापरा.
Ravindra Bagale's Tip – हिंदी
Filtered data का total =SUM() से निकाला तो छुपी rows भी जुड़ जाती हैं – बहुत से students गलत total report करते हैं. Filter लगा हो तो SUBTOTAL(109,…) इस्तेमाल करो. और column में errors हों तो SUM में #N/A आता है – AGGREGATE(9,6,…) इस्तेमाल करो.
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.