subtotal
1 sub-question across 1 paper, 3 marks total.
Computer Applications Technology 2026
On the Sectors worksheet convert Sectors!A1:B6 to an Excel Table, filter Sector to show names ending with 'Tech', and in B8 use SUBTOTAL to sum the visible Q1 values, choosing a function_num that ignores hidden rows and updates when the filter changes.
Show answerHide answer
Select A1:B6 → Ctrl+T → tick "My table has headers" → OK.
Click the Sector filter arrow → Text Filters → Ends With… → type
Tech→ OK. (Logistics disappears; FinTech, EdTech, AgriTech and HealthTech remain.)In B8 (outside the table):
=SUBTOTAL(109,B2:B6)Save and close 7_Dashboard.xlsx.
Why this earns the marks
marks: table created, filter correctly applied, SUBTOTAL with 109. 109 = SUM that ignores hidden rows, and SUBTOTAL recalculates when the filter changes, unlike plain SUM, which adds hidden rows too — exactly the SUBTOTAL behaviour explained in the spreadsheets notes, now applied to a live filtered table rather than a manually hidden range. Place B8 below the table with a gap row so Excel does not absorb it into the table, because an Excel Table automatically expands to include any data typed immediately adjacent to it — without the gap, your SUBTOTAL cell would become a new table row rather than staying an independent total outside it. If your Sectors sheet Q1 values match Impact, the visible total is 31 (5 + 10 + 11 + 5), which is a useful arithmetic check that the filter and SUBTOTAL are both working as intended, not just present.