6. Tables, Sorting and Filtering
6.7 SUBTOTAL and AGGREGATE
SUBTOTAL for filtered rows with example — in 30 seconds
What: SUBTOTAL with 109 (sum) or 103 (count) uses only the rows you can see after filtering.
Why: A normal SUM keeps showing the total of all rows, even when the filter shows only Pune. People then quote the wrong number.
Tiny example: Filter City = Pune. SUM still shows 2870. =SUBTOTAL(109,tblOrders[Amount]) shows 544.
SUBTOTAL for filtered rows with example — 30 सेकंदात
काय: 109 (sum) किंवा 103 (count) सह SUBTOTAL फक्त filter नंतर दिसणाऱ्या rows वापरतो.
का: साधा SUM filter फक्त Pune दाखवत असला तरी सगळ्या rows चा total दाखवत राहतो. मग लोक चुकीचा आकडा सांगतात.
छोटं उदाहरण: City = Pune filter लावा. SUM तरीही 2870 दाखवतो. =SUBTOTAL(109,tblOrders[Amount]) 544 दाखवतो.
SUBTOTAL for filtered rows with example — 30 सेकंड में
क्या: 109 (sum) या 103 (count) के साथ SUBTOTAL सिर्फ़ filter के बाद दिखने वाली rows इस्तेमाल करता है.
क्यों: सादा SUM filter सिर्फ़ Pune दिखाए तब भी सारी rows का total दिखाता रहता है. फिर लोग गलत आँकड़ा बताते हैं.
छोटा उदाहरण: City = Pune filter लगाएँ. SUM फिर भी 2870 दिखाता है. =SUBTOTAL(109,tblOrders[Amount]) 544 दिखाता है.
First look at the data (before → after):
Expected result — What each formula shows while the filter is on
| Formula | Shows |
|---|---|
| =SUM(tblOrders[Amount]) | 2870 |
| =SUBTOTAL(109,tblOrders[Amount]) | 544 |
| =SUBTOTAL(103,tblOrders[Order ID]) | 4 |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on subtotal and aggregate.
Before table and formula as text
Before — Filter on City = Pune (4 rows visible)
| Order ID | City | Amount |
|---|---|---|
| BLK-1001 | Pune | 64 |
| AMN-1003 | Pune | 110 |
| BLK-1006 | Pune | 150 |
| BLK-1009 | Pune | 220 |
About this data: Demo table only — not real customer or company data. Built to practise subtotal and aggregate.
Formula / click path
=SUM(tblOrders[Amount])
=SUBTOTAL(109,tblOrders[Amount])
=SUBTOTAL(103,tblOrders[Order ID])
Do it step by step:
- Above the table, type
=SUBTOTAL(109,tblOrders[Amount]). - Next to it, type
=SUBTOTAL(103,tblOrders[Order ID])for the visible order count. - Filter the City column to Pune.
- The two cells change to 544 and 4.
- Clear the filter. They go back to the full totals.
स्टेप बाय स्टेप करून बघा:
- Table च्या वर
=SUBTOTAL(109,tblOrders[Amount])type करा. - दिसणाऱ्या orders मोजायला बाजूला
=SUBTOTAL(103,tblOrders[Order ID])type करा. - City column ला Pune filter लावा.
- दोन्ही cells 544 आणि 4 होतात.
- Filter काढा. ते परत पूर्ण totals दाखवतात.
स्टेप बाय स्टेप करके देखें:
- Table के ऊपर
=SUBTOTAL(109,tblOrders[Amount])type करें. - दिखने वाले orders गिनने के लिए बगल में
=SUBTOTAL(103,tblOrders[Order ID])type करें. - City column पर Pune filter लगाएँ.
- दोनों cells 544 और 4 बन जाती हैं.
- Filter हटाएँ. वे फिर से पूरे totals दिखाती हैं.
=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 |
About this data: Made-up rows so you can copy the pattern for subtotal and aggregate. Currencies may use ₹, £, € or $.
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 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
| 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 |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on subtotal and aggregate.
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.
Loading practice tasks...
- Total cost (Easy): Total of E2:E8.
- Average weight (Easy): Average of D2:D8.
- Heaviest (Easy): Biggest weight in D2:D8.
- Lightest (Easy): Smallest weight in D2:D8.
- Count costs (numbers) (Medium): Count numbers in E2:E8.
- Count all cells (Medium): Count filled cells in E2:E8.
- Pending costs (Medium): How many costs are not numbers?
- Average cost (Medium): Average of E2:E8.
- Cost per kg (Hard): Visible total of E2 to E8 divided by (visible total of D2 to D8 minus D3).
- Hidden-row version (Hard): Count shipments in A2:A8 with code 103.
- Gap between heaviest and lightest (Hard): Heaviest minus lightest in D2:D8 with 104 and 105.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
