All versionsExamples need Excel 2021 or laterMath

SUBTOTAL

SUBTOTAAL in Dutch Excel

Calculates a total, count, average or other summary over the visible rows of a list, skipping filtered rows and other subtotals.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABC
1Visible total€28,250.00€ 28.250,00=SUBTOTAL(9, C4:C10)=SUBTOTAL(9; C4:C10)
2
3Entry no.JournalDebit
425-0401VRK€12,400.00€ 12.400,00
525-0402MEM€8,500.00€ 8.500,00
625-0403INK€3,150.00€ 3.150,00
725-0404MEM€15,000.00€ 15.000,00
825-0405BNK€2,275.00€ 2.275,00
925-0406MEM€4,750.00€ 4.750,00
1025-0407VRK€9,800.00€ 9.800,00
Note: A filter on Journal is set to MEM, so rows 4, 6, 8 and 10 are hidden. Only rows 5, 7 and 9 are on screen.

Result: €28,250.00€ 28.250,00

=SUBTOTAL(9, C4:C10)
=SUBTOTAL(9; C4:C10)

What SUBTOTAL does and when to use it

What it does: it totals (or averages, counts and so on) a list while skipping the rows a filter hides, for example the total of only the manual entries the filter shows.

When to use it: under a list that people filter, and for the grand total of a report with section subtotals. A code picks the calculation: 9 sums, 1 averages, 2 counts numbers.

Three main gains:

  • Follows the filter: rows hidden by a filter drop out of the total; SUM keeps adding them.
  • No double counting: cells in the range that hold a SUBTOTAL are skipped, so section subtotals and the grand total do not overlap.
  • Hidden by hand, your choice: the codes 101 to 111 also skip rows you hid yourself.

How Excel evaluates it

Excel reads function_num first and picks the calculation: the last digit or two select the function, and 100 or more switches on skipping of manually hidden rows.

It then goes through ref1 row by row. A row hidden by a filter is always skipped. A cell that contains a SUBTOTAL formula is skipped in every case, so section totals are never counted twice.

The remaining cells go into the chosen calculation, which follows the normal rules of that function, for example text ignored by SUM.

Watch out: A row hidden by Hide or a collapsed group is skipped only with codes 101 to 111; with codes 1 to 11 it still counts.

Mental model

A calculator that only reads what is on screen. Pull down the blinds on some rows and it no longer sees them; a line that already says 'subtotal' it skips, because it knows it has counted those numbers.

Terms used on this page: range, cell

Syntax

Official SUBTOTAL documentation
SUBTOTAL(function_num, ref1, [ref2], ...)
SUBTOTAL(function_num; ref1; [ref2]; ...)
ArgumentTypeMeaning
function_numRequiredWhich summary: 9 = sum, 1 = average; add 100 to also skip rows hidden by hand.
ref1RequiredThe column of cells to summarize, usually one column of a list.
ref2OptionalMore ranges to include, up to 254.

Return value:A number: the chosen calculation over the visible or all rows

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Total of the manual entries a filter shows

You have exported the XAF auditfile of Veldhuis Installatie BV to a sheet and put a filter on the Journal column. You filter on MEM to look at the manual entries, and the total at the top has to show the MEM lines only, not every journal line in the export.

ABCD
1Visible total€28,250.00€ 28.250,00=SUBTOTAL(9, C4:C10)=SUBTOTAL(9; C4:C10)
2
3Entry no.JournalDebit
425-0401VRK€12,400.00€ 12.400,00
525-0402MEM€8,500.00€ 8.500,00
625-0403INK€3,150.00€ 3.150,00
725-0404MEM€15,000.00€ 15.000,00
825-0405BNK€2,275.00€ 2.275,00
925-0406MEM€4,750.00€ 4.750,00
1025-0407VRK€9,800.00€ 9.800,00
11
Formula cell (C1)InputHidden or filtered rowNote: A filter on Journal is set to MEM, so rows 4, 6, 8 and 10 are hidden. Only rows 5, 7 and 9 are on screen.

Formula in C1

=SUBTOTAL(9, C4:C10)
=SUBTOTAL(9; C4:C10)

Result: €28,250.00€ 28.250,00

Step by step
  1. Excel reads function_num 9, which means SUM.
  2. It goes through C4:C10 row by row. Rows 4, 6, 8 and 10 are hidden by the filter, so C4 (12400, VRK), C6 (3150, INK), C8 (2275, BNK) and C10 (9800, VRK) are skipped.
  3. The visible cells are C5, C7 and C9, holding 8500, 15000 and 4750.
  4. It adds them: 8500 + 15000 + 4750 = 28250.
  5. Clear the filter and the same formula returns every journal line: 12400 + 8500 + 3150 + 15000 + 2275 + 4750 + 9800 = 55875. A SUM(C4:C10) returns 55875 in both cases.

Watch out: Put the formula in C11, directly under the list, and Excel may treat that row as part of the filtered range: filter on VRK and the total row can vanish with the rows it sums.

More on this example: why it works

Why it works

SUBTOTAL checks whether each row is visible before it adds, and a filter hides rows, so the total follows the filter without any change to the formula. The total sits above the list in row 1, which no filter on row 3 can hide.

Intermediate

Cost accounts in scope, skipping rows hidden by hand

From the trial balance (saldibalans) of Veldhuis Installatie BV you listed six cost accounts. The client's cost specification covers only four of them, so you hid accounts 4510 and 4650 with right-click Hide instead of deleting them, because you still have to explain them separately. The total at the top must be the amount in scope, ready to compare with the specification.

ABCD
1Amount in scope€678,550.00€ 678.550,00=SUBTOTAL(109, C4:C9)=SUBTOTAL(109; C4:C9)
2
3AccountNameBalance
440004000Personnel costs€482,600.00€ 482.600,00
545004500Housing costs€96,400.00€ 96.400,00
645104510Energy€18,750.00€ 18.750,00
746004600Vehicle costs€41,300.00€ 41.300,00
846504650Travel costs€12,900.00€ 12.900,00
948004800Depreciation€58,250.00€ 58.250,00
10
Formula cell (C1)InputHidden or filtered rowNote: Rows 6 (account 4510) and 8 (account 4650) are hidden with right-click Hide. There is no filter on this sheet.

Formula in C1

=SUBTOTAL(109, C4:C9)
=SUBTOTAL(109; C4:C9)

Result: €678,550.00€ 678.550,00

Step by step
  1. Excel reads function_num 109: the last digits, 9, select SUM, and the 100 switches on skipping of rows hidden by hand.
  2. It goes through C4:C9. Row 6 (18750, energy) and row 8 (12900, travel costs) are hidden, so both are skipped.
  3. The visible balances are 482600, 96400, 41300 and 58250. It adds them: 482600 + 96400 + 41300 + 58250 = 678550.
  4. With function_num 9 instead of 109, the hidden rows would count: 678550 + 18750 + 12900 = 710200. The 9 only ignores rows hidden by a filter, and there is none here.
  5. Unhide row 6 because account 4510 turns out to be in scope after all, and C1 becomes 678550 + 18750 = 697300 on its own.

Watch out: A row with a height set very small looks hidden but is not, and SUBTOTAL(109) still counts it. Use Hide, not dragging the row border.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Excel distinguishes between rows a filter hides and rows you hide yourself, and function_num decides whether SUBTOTAL respects the second kind. The 100-series respects both, so it gives the same answer whichever way the rows were hidden, which makes it the safer default for totals that a reviewer reads.

More to watch out for

  • A reviewer who opens your file sees 678550 and a list that seems complete, and the hidden accounts drop out of sight. Add the number of hidden accounts next to the total, for example with SUBTOTAL(3, A4:A9) minus SUBTOTAL(103, A4:A9), which gives 6 - 4 = 2.

Variations

=SUBTOTAL(103, A4:A9)
=SUBTOTAL(103; A4:A9)

Counts the visible accounts, 4, using the COUNTA code; useful as a label beside the total.

Advanced

Visible manual postings only

The journal lines of Veldhuis Installatie BV are filtered to periods 11 and 12, the last two months of the year. You want the visible amount of the MEM (manual) entries only.

SUMIFS ignores the filter, and SUBTOTAL has no condition argument, so you let SUBTOTAL check the visibility of each row and SUMPRODUCT apply the journal condition.

ABCD
1JournalMEM
2Visible amount€30,450.00€ 30.450,00=SUMPRODUCT(SUBTOTAL(109, OFFSET(C5, ROW(C5:C11) - ROW(C5), 0)) * (B5:B11 = B1))=SUMPRODUCT(SUBTOTAL(109; OFFSET(C5; ROW(C5:C11) - ROW(C5); 0)) * (B5:B11 = B1))
3
4PeriodJournalAmount
51111MEM€6,200.00€ 6.200,00
61212VRK€18,400.00€ 18.400,00
71212MEM€9,750.00€ 9.750,00
81010MEM€3,300.00€ 3.300,00
91111INK€5,100.00€ 5.100,00
101212MEM€14,500.00€ 14.500,00
111010VRK€7,600.00€ 7.600,00
12
Formula cell (B2)InputHidden or filtered rowNote: A filter on Period shows only periods 11 and 12, so rows 8 and 11 (period 10) are hidden. Code 109 leaves them out, and so would code 9.

Formula in B2

=SUMPRODUCT(SUBTOTAL(109, OFFSET(C5, ROW(C5:C11) - ROW(C5), 0)) * (B5:B11 = B1))
=SUMPRODUCT(SUBTOTAL(109; OFFSET(C5; ROW(C5:C11) - ROW(C5); 0)) * (B5:B11 = B1))

Result: €30,450.00€ 30.450,00

Step by step
  1. Excel starts inside. ROW(C5:C11) gives 5 to 11 and ROW(C5) gives 5, so the subtraction produces the offsets 0, 1, 2, 3, 4, 5 and 6.
  2. OFFSET(C5, offsets, 0) turns those seven numbers into seven separate one-cell references: C5, C6, C7, C8, C9, C10 and C11.
  3. SUBTOTAL(109, ...) sums each one-cell reference on its own. A visible cell returns its value, a hidden cell returns 0. Result: 6200, 18400, 9750, 0, 5100, 14500, 0. Rows 8 and 11 are the zeros.
  4. B5:B11 = B1 compares each journal with MEM: TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE.
  5. The multiplication treats TRUE as 1 and FALSE as 0: 6200 × 1, 18400 × 0, 9750 × 1, 0 × 1, 5100 × 0, 14500 × 1, 0 × 0 gives 6200, 0, 9750, 0, 0, 14500, 0.
  6. SUMPRODUCT adds the array: 6200 + 9750 + 14500 = 30450. The period 10 MEM line (3300) is excluded because its row is hidden.
  7. Clear the filter and the result becomes 6200 + 9750 + 3300 + 14500 = 33750, the same as SUMIFS(C5:C11, B5:B11, B1).

Watch out: Using SUMIFS here returns 33750 while the screen shows only periods 11 and 12. Nothing looks wrong, but the figure includes a hidden row.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

SUBTOTAL cannot apply a condition, but given a single cell it reports that cell's value if the row is visible and 0 if not, which is a visibility flag. OFFSET hands it every row as a separate one-cell reference, and SUMPRODUCT combines the flags with the journal test and adds the result without Ctrl+Shift+Enter.

More to watch out for

  • OFFSET is volatile: it recalculates on every change in the workbook. On an auditfile export with hundreds of thousands of lines this construction slows the file down noticeably.
  • If the anchor C5 and the range C5:C11 do not start on the same row, every flag shifts one row and the result silently mixes journal lines.

Variations

=SUMIFS(C5:C11, B5:B11, B1)
=SUMIFS(C5:C11; B5:B11; B1)

The condition without visibility: 33750. Use it when the answer should not depend on who filtered the sheet last.

MasterExcel 2021 or later

Average open item per visible line in an aging report

The receivables aging list (ouderdomsanalyse debiteuren) of Veldhuis Installatie BV was built with Data > Subtotal, so each aging band ends in a SUBTOTAL row, and the outline lets you collapse a band. You collapse the over 90 days band and want the average open amount per visible customer line, without the band totals and without the collapsed lines.

The formula has to survive a view with no visible lines.

ABCD
1Average per visible line€10,525.00€ 10.525,00=LET( amounts, C4:C10, total, SUBTOTAL(109, amounts), lines, SUBTOTAL(102, amounts), IF(lines = 0, 0, ROUND(total / lines, 2)) )=LET( amounts; C4:C10; total; SUBTOTAL(109; amounts); lines; SUBTOTAL(102; amounts); IF(lines = 0; 0; ROUND(total / lines; 2)) )
2
3Aging bandRelationOpen amount
40 to 30 daysD1001€12,400.00€ 12.400,00
50 to 30 daysD1002€8,650.00€ 8.650,00
6Total 0 to 30 days€21,050.00€ 21.050,00
7Over 90 daysD1003€15,200.00€ 15.200,00
8Over 90 daysD1004€6,800.00€ 6.800,00
9Over 90 daysD1005€3,950.00€ 3.950,00
10Total over 90 days€25,950.00€ 25.950,00
11
Formula cell (C1)InputHidden or filtered rowNote: C6 holds =SUBTOTAL(9, C4:C5) and C10 holds =SUBTOTAL(9, C7:C9). The over 90 days band is collapsed, which hides rows 7 to 9; the band total in row 10 stays visible.

Formula in C1

=LET(
  amounts, C4:C10,
  total, SUBTOTAL(109, amounts),
  lines, SUBTOTAL(102, amounts),
  IF(lines = 0, 0, ROUND(total / lines, 2))
)
=LET(
  amounts; C4:C10;
  total; SUBTOTAL(109; amounts);
  lines; SUBTOTAL(102; amounts);
  IF(lines = 0; 0; ROUND(total / lines; 2))
)

Result: €10,525.00€ 10.525,00

Step by step
  1. LET names the range amounts, C4:C10. It stays a reference, which SUBTOTAL needs to know which rows are visible.
  2. total is SUBTOTAL(109, amounts). C6 and C10 hold SUBTOTAL formulas and are skipped. Rows 7 to 9 are hidden by the collapsed band and skipped because of the 100. What remains is C4 and C5: 12400 + 8650 = 21050.
  3. lines is SUBTOTAL(102, amounts), the COUNT code with the same skipping rules. It counts C4 and C5: 2.
  4. lines is not 0, so IF evaluates ROUND(21050 / 2, 2) = 10525.00.
  5. Expand the band and the rows come back: total = 12400 + 8650 + 15200 + 6800 + 3950 = 47000, lines = 5, and the result is 47000 / 5 = 9400.00. The band totals are still ignored.
  6. Collapse both bands and lines becomes 0. Without the IF, 0 / 0 would show #DIV/0!; with it, C1 shows 0.

Watch out: The mirror-image mistake is 9 and 2 instead of 109 and 102. A collapsed band is hidden by hand, not filtered, so the result is 9400.00 whether the band is collapsed or not, a plausible number that ignores what you see.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Two SUBTOTAL calls with matching codes (109 and 102) see exactly the same set of cells, so the division is a true average of what you see on screen. A plain AVERAGE over C4:C10 would include both band totals and every collapsed line: (12400 + 8650 + 21050 + 15200 + 6800 + 3950 + 25950) / 7 = 13428.57.

Do not use this construction when the column can contain error values, because SUBTOTAL passes the error through; AGGREGATE with function 1 and option 3 averages visible rows while skipping errors and nested subtotals, at the cost of a function fewer colleagues recognize.

More to watch out for

  • Mixing codes, for example 109 with 2, divides the visible total by a count that includes hidden rows: 21050 / 5 = 4210.00.
  • A band total typed as a value, or built with SUM, is not a SUBTOTAL, so it is no longer skipped and the average drifts upward. Keep every band total a SUBTOTAL formula.

Variations

=SUBTOTAL(101, C4:C10)
=SUBTOTAL(101; C4:C10)

The AVERAGE code on its own gives the same 10525 here; the LET version adds the guard for an empty view and makes total and count available for a label.

Common mistakes

SymptomCauseFix
Hidden rows still counted in the totalRows hidden by right-click Hide still count, because the formula uses 9 instead of 109.Use 109 here, and the 101 to 111 codes in general.
The total vanishes when you filterThe total sits in a row that the filter hides, so it disappears the moment someone filters.Put the total above the header row, or leave an empty row between the list and the total so the filter range stops before it.
Grand total counts every amount twiceA grand total with SUM over a column that contains section subtotals, which counts every amount twice.Use SUBTOTAL for both the section totals and the grand total; it skips cells that hold SUBTOTAL formulas.