If SUBTOTAL(109, ...) and SUBTOTAL(9, ...) return the same number on a workbook that contains hidden rows, one of the two is wrong. HotXLS, the native Excel spreadsheet component for Delphi and C++Builder, behaved exactly that way until version 2.197.0, because its calculation engine had no way to ask a worksheet whether a given row was hidden
The symptom rarely arrives as a bug report about formula codes. It arrives as a mismatch: a batch job on the server computes a total, a user opens the same file in Excel with a filter applied, and the two numbers differ by whatever the filtered-out rows happened to sum to. Nobody suspects the aggregation function, because the formula string in the cell is identical in both places. The difference is entirely in what the evaluator was allowed to see
Why does SUBTOTAL 109 include hidden rows?
Because in most engine designs the layer that evaluates a formula never learns about row visibility. HotXLS was a textbook case: the calculation engine in lxCalc.pas reached cell values through a single TXLSGetValue callback that answers with a value for a (sheet, row, column) triple and nothing else. Visibility is a presentation attribute stored on the row record, and no part of that record travelled down the call chain. The engine therefore had one aggregation path, and both halves of the SUBTOTAL function-number table resolved to it. That is not a rounding-error class of defect: it is the whole reason the second half of the table exists. ECMA-376 Part 1, published as ISO/IEC 29500-1, defines SUBTOTAL in its formula function definitions (§18.17.7) with a first argument that selects both the inner aggregation and the hidden-row policy. Codes 1 through 11 map to AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, and VARP while including values on manually hidden rows. Codes 101 through 111 select the same eleven aggregations and exclude them. A user who types 109 instead of 9 is making a deliberate statement about hidden data, and an engine that collapses the distinction silently overrules that statement
What the function numbers map to inside the engine
HotXLS resolves the SUBTOTAL first argument in CalcSubtotalFunc, which normalises codes 101 through 111 down onto the same inner function identifiers as codes 1 through 11 and then dispatches on the aggregation itself. Most of the family flows through the incremental ExcelSum accumulator, the one that handles SUM, COUNT, COUNTA, MIN, MAX, and AVERAGE. Five of them cannot: STDEV, VAR, STDEVP, VARP, and PRODUCT need a closed-form pass over the data, so CalcSubtotalFunc routes inner codes 12, 46, 193, 194, and 183 to a separate reducer, SubtotalReduceVariance. That split is the first thing worth mapping before touching anything, because two independent aggregation paths mean two independent cell-walk loops, and a fix applied to only one of them produces the worst possible outcome: SUBTOTAL(109, ...) respects the filter while SUBTOTAL(107, ...) on the same range does not. Counting the loops in HotXLS turned up six of them once AGGREGATE was included, spread across range evaluation, plain range collection, and three separate reducers
Why a scratch field instead of six new signatures?
Because threading a new parameter through six cell-walk functions, plus everything that calls them, is a wide change to a hot code path for the sake of one boolean. HotXLS already had a precedent for the alternative: a transient field on the calculator, in the same spirit as the scratch field that GetRangeInfo uses to record when a 3D reference resolved into an external workbook. Version 2.197.0 added a second one. The engine gained a callback type, TXLSIsRowHidden, declared as a function of (SheetIndex, row) returning Boolean, stored in FIsRowHidden, plus a transient FIgnoreHiddenRows flag. The flag is armed at the entry of CalcSubtotalFunc when the function code falls in 101 through 111, and at the entry of CalcAggregateFunc for the AGGREGATE option codes that select hidden-row exclusion. Every cell-walk loop then inspects it and skips one row when it is set, adding a single line each
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Two details in the arming code carry the correctness of the whole scheme. The flag is saved and restored rather than simply set and cleared, because a SUBTOTAL argument can contain an expression that runs its own evaluation while the outer aggregation is still on the stack, and that nested work must not inherit or destroy the outer gate. And the restore lives in a finally block, because CalcSubtotalFunc has several early exits for error codes; a flag left armed after an error return would silently corrupt the next unrelated formula in the recalculation order
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;The Assigned test is what keeps the change compatible. HotXLS extended the calculator constructor with a third parameter defaulting to nil, so any code that builds a TXLSCalculator with the old two-argument call still compiles and still gets the legacy include-hidden behaviour. Nothing about the existing API changed shape
Where does the hidden-row bit actually come from?
From the worksheet, through two different sources, because HotXLS carries two workbook engines. The legacy BIFF side answers from TXLSRowInfoList.GetHidden, reached through TXLSWorkbook.GetRowHidden. The OOXML side answers from TXLSXWorksheet.GetRowHidden, reached through TXLSXWorkbook.GetCalcRowHidden. Both are wired into the calculator at construction time, alongside the cell-value callback they mirror. The row conventions are where this kind of bridge normally goes wrong, so they are worth stating explicitly. The calculator hands the callback a 0-based row, matching the coordinates TXLSGetValue already uses. The XLSX worksheet keys its row-hidden map by 1-based row number, exactly as Excel numbers rows, which is also what the public RowHidden[ARow] property exposes. The XLSX bridge therefore adds one before the lookup, and the BIFF bridge does not, because TXLSRowInfoList is already 0-based. Both bridges treat a sheet index or row outside the valid range as visible, so an out-of-bounds query degrades into the old include-hidden answer instead of dropping data
What changes for filtered workbooks
This is the case that generates the support tickets. Applying an AutoFilter in HotXLS through ApplyAutoFilter evaluates the column criteria and hides every data row that does not match, which is precisely what Excel does when a user clicks a filter dropdown. Before v2.197.0 those hidden rows were invisible to the user and fully visible to the calculation engine, so a server-side SUBTOTAL(109, ...) reported the unfiltered total. Now the same call reports the filtered one
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Manual hiding works the same way, since RowHidden[ARow] := True is the same state the filter writes. That equivalence is deliberate in Excel and now holds in HotXLS too. One consequence deserves a note in whatever documentation ships with your generated workbooks: a total computed with code 109 is a view-dependent number, so a recipient who clears the filter changes it. When a report must state a fixed figure regardless of what the reader does to the view, code 9 is the correct choice and always was. Filters, validation, and tables are covered together in the article on data validation, AutoFilter, and tables. Because hiding rows does not touch any formula, it also does not dirty the dependency graph on its own, which is worth knowing if you rely on incremental recalculation over the dirty subgraph to keep large workbooks responsive
AGGREGATE option codes and one limit that is still open
AGGREGATE is SUBTOTAL with a second policy argument, and HotXLS handles it in CalcAggregateFunc. The option argument encodes independent switches: whether nested SUBTOTAL and AGGREGATE calls inside the range are skipped, whether values on hidden rows are skipped, and whether error values are suppressed rather than propagated. HotXLS arms the shared hidden-row gate for option codes 2, 3, 6, and 7, and suppresses error values for option codes 4 through 7. The function-number argument then selects the aggregation exactly as SUBTOTAL does, including the routing of variance, standard deviation, and product through their own reducers. One documented gap remains, and it is better stated here than discovered in production: the ignore-nested-SUBTOTAL semantics associated with the low option codes are not implemented in HotXLS. Detecting a nested SUBTOTAL inside a referenced range requires marking the evaluator recursion state so an inner aggregation can announce itself to the outer one, which is a larger change than the hidden-row gate. In practice the exposure is small, because real workbooks almost always place SUBTOTAL formulas outside the ranges other SUBTOTAL formulas aggregate over. If your generator does build overlapping aggregation ranges, do not rely on the low option codes to deduplicate them
The arity guard that shipped alongside it
Version 2.197.0 also closed a validation gap in the same dispatcher, and the design reason is the same one that motivated the scratch field: put the check where it can be written once. Roughly 280 built-in function bodies each verified their own argument count against Item.ChildCount, which left no consistent boundary for the case of too many arguments. A call like =SIN(1,2) reached a function body that examined its first argument, ignored the surplus, and returned a plausible number where Excel returns #VALUE!. HotXLS already stored the declared arity of every built-in in its function registry, exposed as THashFunc.ArgsCnt with -1 marking a variadic function such as SUM, IF, or CONCAT. Version 2.197.0 forwarded that through a new TXLSFormula.FuncArgsCntByPtg property and added one gate at the top of GetValueItemFunc, the main dispatcher
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;The guard rejects too many arguments and deliberately says nothing about too few. Omitting a trailing optional argument is legal in Excel for VLOOKUP, SUBSTITUTE, and a long list of others, so a symmetric check would have broken correct formulas to catch incorrect ones. Unknown identifiers report as variadic and skip the gate entirely, which is what keeps user-defined functions out of its way; if you register your own functions, the behaviour described in the guide to the formula engine and custom functions is unaffected. Centralising the too-few case is a separate job, because each of those 280 bodies has its own error code semantics and they have to be reviewed one at a time rather than assumed
The calculation engine described here, both workbook facades, and the AutoFilter and row-visibility APIs that feed it are part of the HotXLS Delphi spreadsheet component, which ships with full source for Delphi and C++Builder and requires no Excel installation on the machine that runs it