Ravindra BagaleCourses & study guides मराठी Track your progress

SUBTOTAL vs SUM in Excel: Ignore Hidden and Filtered Rows (Function Codes 9 vs 109) and the Table Total Row

By Last updated:

You filter an orders table down to one city, but the total under it still shows the profit of every city. Or a report says 150 when you can count only 14 rows of 10. In both cases SUM is adding rows you cannot see. SUBTOTAL adds only what is visible, and it is a favourite interview question: "What is the difference between SUM and SUBTOTAL?" In this chapter you answer it with real numbers.

What you'll learn in this chapter

  • SUBTOTAL syntax and its function codes
  • Codes 1-11 vs 101-111: manually hidden rows
  • Filtered rows: ignored by every SUBTOTAL code
  • The Table Total Row, which uses SUBTOTAL
  • The hidden-row interview question, and how to unhide rows

Practice file: excel-subtotal-vs-sum-practice.xlsx: Orders (a table with row 8 hidden and SUM/SUBTOTAL answers), Hidden row trick, and Codes.

Made-up data: the 24 orders are made up for practice. They imitate a quick-commerce app such as Blinkit or Amazon Now in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar.

1. The data

The orders sit in a table named Orders, A1:M25, one row per order: Order_ID, Order_Date, City, Area, Product_Name, Quantity, Cost_Price, Selling_Price, Payment_Mode, Status, Delivery_Time_mins, Delivery_Status and Profit (column M). It is the same made-up data as the COUNTA, UNIQUE and COUNTIF chapter.

  1. 24 orders in total.
  2. The profit of all 24 orders adds up to 327.
  3. Pune has 6 orders with a total profit of 6.

2. SUBTOTAL: the syntax

=SUBTOTAL(function_num, ref1, [ref2], ...)

  1. function_num is a code that picks the calculation: 9 or 109 means SUM, 3 or 103 means COUNTA, and so on.
  2. ref1 is the range or table column, for example Orders[Profit].
Function Code (includes manually hidden rows) Code (ignores manually hidden rows)
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV.S 7 107
STDEV.P 8 108
SUM 9 109
VAR.S 10 110
VAR.P 11 111

With nothing hidden, =SUM(Orders[Profit]), =SUBTOTAL(9,Orders[Profit]) and =SUBTOTAL(109,Orders[Profit]) all give 327. The difference shows up only when rows disappear.

3. Rows hidden by a filter

  1. Click the City filter button, keep only Pune, click OK.
  2. Six rows stay visible.
Formula Result Why
=SUM(Orders[Profit]) 327 SUM adds every row, visible or not
=SUBTOTAL(9,Orders[Profit]) 6 SUBTOTAL skips rows hidden by a filter
=SUBTOTAL(109,Orders[Profit]) 6 same

Both sets of codes ignore rows hidden by a filter. Put these formulas above or beside the table, not in rows that the filter can hide.

4. Rows you hide yourself

Clear the filter (Data > Clear). Now hide one row by hand:

  1. Right-click the row number 8 (ORD1007, Sambhaji Nagar, profit 80).
  2. Click Hide.
Formula Result Why
=SUM(Orders[Profit]) 327 counts the hidden row
=SUBTOTAL(9,Orders[Profit]) 327 codes 1-11 include manually hidden rows
=SUBTOTAL(109,Orders[Profit]) 247 codes 101-111 ignore them: 327 − 80
=SUBTOTAL(103,Orders[Order_ID]) 23 visible orders only
=SUBTOTAL(104,Orders[Profit]) 100 highest visible profit

So the rule is:

  1. Filtered rows: every SUBTOTAL code ignores them.
  2. Manually hidden rows: only the 101-111 codes ignore them.
  3. SUM never ignores anything.
One profit column, three answers SUM SUBTOTAL 9 SUBTOTAL 109 Nothing hidden 327 327 327 Filter City = Pune 327 6 6 Row 8 hidden by hand 327 327 247 Filtered rows: every SUBTOTAL code skips them. Rows hidden by hand: only 101-111 skip them (327 − 80 = 247). SUM never skips anything.

Figure 1. Same profit column, three answers. Filter City = Pune: SUM 327, SUBTOTAL 9 and 109 both 6. Row 8 hidden by hand: SUM 327, SUBTOTAL 9 327, SUBTOTAL 109 247.

Classroom line

This is a favourite question of interviewers: what is the difference between SUBTOTAL and SUM?

5. The Table Total Row

An Excel Table can add a total row for you, and it uses SUBTOTAL:

  1. Click any cell in the table.
  2. Table Design > Total Row (tick the box).
  3. A Total row appears under the last row. Excel usually puts a sum under the last numeric column, here Profit: 327.
  4. Click the total cell under Order_ID, open its drop-down and choose Count: 24.

Click the cell and look at the formula bar: =SUBTOTAL(109,[Profit]) and =SUBTOTAL(103,[Order_ID]). Because the total row uses SUBTOTAL, it follows every filter:

Filter Count of Order_ID Sum of Profit
none 24 327
City = Pune 6 6
+ Payment_Mode = Online 5 18
+ Status = Delivered 4 26

This is quick analysis straight from the filter buttons, with no formula typed.

The Total Row (SUBTOTAL) follows every filter No filter Count of Order_ID: 24 Sum of Profit: 327 City = Pune Count of Order_ID: 6 Sum of Profit: 6 + Online Count of Order_ID: 5 Sum of Profit: 18 + Delivered Count of Order_ID: 4 Sum of Profit: 26 Total cells: =SUBTOTAL(103,[Order_ID]) =SUBTOTAL(109,[Profit]) Table Design > Total Row, then pick Count or Sum from the drop-down in each total cell.

Figure 2. The Total Row follows the filters: 24 orders and 327 profit; City = Pune gives 6 and 6; adding Online gives 5 and 18; adding Delivered gives 4 orders and 26 profit.

Ravindra Bagale's Tip

When you share a filtered report, use SUBTOTAL (or the Table Total Row) for every total on it. A SUM under a filtered list is the most common reason a report "doesn't add up" when someone checks it with a calculator.

6. The hidden-row interview question

The question: "15 rows each show a total of 10. Why does the sum say 150, when I can see only 14 rows?"

  1. 15 × 10 = 150, so SUM is counting one row you cannot see.
  2. Look at the row numbers on the left. Where a row is hidden, the numbers jump (6, then 8) and you see a double line between them.
  3. Select the rows around it (6 to 8), right-click a row number, click Unhide.
  4. Or use =SUBTOTAL(109,B2:B16): it gives 140, the total of the 14 visible rows.

Classroom line

Wherever a row is hidden, you see two lines.

The hidden-row trick: 15 rows of 10 A B 1 Item Total 2 Item 1 10 3 Item 2 10 4 Item 3 10 5 Item 4 10 6 Item 5 10 8 Item 7 10 9 Item 8 10 10 Item 9 10 11 Item 10 10 12 Item 11 10 ← 6, then 8: row 7 is hidden (double line) ... rows 13 to 16 also hold 10 =SUM(B2:B16) → 150 =SUBTOTAL(109,B2:B16) → 140 =SUBTOTAL(102,B2:B16) → 14 visible Fix: select rows 6 to 8 > right-click > Unhide

Figure 3. Row 7 is hidden: the row numbers jump from 6 to 8 with a double line. SUM(B2:B16) = 150 but SUBTOTAL(109,B2:B16) = 140, the 14 visible rows.

7. The whole chapter on one page

SUM SUBTOTAL 9 SUBTOTAL 109
Rows hidden by a filter counted ignored ignored
Rows hidden by hand counted counted ignored
  1. =SUBTOTAL(code, range). 9 and 109 are SUM, 3 and 103 COUNTA, 1 and 101 AVERAGE.
  2. 101-111 ignore hand-hidden rows; 1-11 include them; both ignore filtered rows.
  3. The Table Total Row uses SUBTOTAL, so it follows filters.
  4. A double line between row numbers means a hidden row; select around it > Unhide.

8. Try at home

Try at home

Task 1

  1. Filter Status = Delivered. What does the Total Row show for Profit? (227.)

Task 2

  1. In the practice file, unhide row 8. What does =SUBTOTAL(109,Orders[Profit]) show now? (327.)

Task 3

  1. Filter City = Nagpur and Kolhapur together. How many orders, and what profit? (8 orders, 218.)

Recap

In short

  1. SUM adds every row, visible or not.
  2. SUBTOTAL ignores rows hidden by a filter, with any code.
  3. 101-111 also ignore rows you hide by hand.
  4. The Table Total Row is SUBTOTAL, so it follows filters.
  5. Double line between row numbers = hidden row.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: SUM vs SUBTOTAL 9 vs SUBTOTAL 109. Example data table with SUM with row 3 hidden: ₹1,000; Row 3 hidden by a filter: ₹800; Row 3 hidden by hand instead: ₹800.
Card 1 of 2: SUM vs SUBTOTAL 9 vs SUBTOTAL 109 · Download PNG
Revision card: SUBTOTAL codes and the Table Total Row. Example data table with Average with SUBTOTAL: ₹250; Largest amount: ₹400; Table Total Row with Lamp filtered out: ₹600.
Card 2 of 2: SUBTOTAL codes and the Table Total Row · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Distinct groups (Easy): How many different values in B2:B11?
  2. List groups (Easy): Unique list of B2:B11.
  3. Filter names (Easy): Names in A2:A11 where B is "Admin".
  4. Sum of filtered (Medium): Sum of D2:D11 where B is "IT", using FILTER.
  5. Count filtered (Medium): Rows where D is above the average of D2:D11.
  6. Sorted values (Medium): D2:D11 sorted largest first.
  7. Top value via SORT (Medium): The largest E2:E11 value using SORT and INDEX.
  8. Second largest (Hard): Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 (Hard): A spill of numbers 1 to 5.
  10. Two-condition filter (Hard): Sum of D2:D11 where B is "Admin" or "IT".
  11. Names joined (Hard): Names where B is "Admin", joined with ", ".

See all practice tasks in the Task Hub →