SUBTOTAL and AGGREGATE Equivalents in pandas

Two Excel functions exist mainly to cope with messy sheets. SUBTOTAL sums, averages or counts a range while ignoring other SUBTOTAL results inside it — so a grand total over a column that already contains regional subtotals does not count everything twice — and, with function numbers from 101 upwards, it also ignores rows someone has hidden. AGGREGATE goes further and can ignore error values such as #DIV/0! and #N/A. When a workbook like that is read into pandas, those rules disappear: the subtotal rows become ordinary rows, the hidden rows are back, and errors arrive as text. This page rebuilds each behaviour in pandas, using openpyxl to find out which rows were subtotals and which were hidden, and compares every number with LibreOffice's calculation of the original formulas. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

The workbook has two sheets. Sales lists reps by region with a SUBTOTAL row after each region and a grand total, and the row for Ben is hidden. Meters has five readings, two of which are errors — a division by zero and an #N/A. Next to each table are the Excel formulas being reproduced. LibreOffice recalculates them for comparison.

Step 1 — What Excel calculates

soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true,false,false,-1' sales_subtotals.xlsx > /dev/null 2>&1
cut -d, -f5- sales_subtotals-Sales.csv | head -6
cut -d, -f4- sales_subtotals-Meters.csv | head -5
soffice --headless --convert-to xlsx --outdir calculated sales_subtotals.xlsx > /dev/null 2>&1   # a copy with results stored

These are the targets. On Sales, a plain SUM over C2:C8 gives 7,980 — every rep plus both regional subtotals, so everything counted twice. SUBTOTAL(9, …) gives 3,990, because it skips the cells that are themselves SUBTOTAL results. SUBTOTAL(109, …) gives 3,190: it also skips Ben's hidden row. The averages follow the same pattern — 798 over five reps, 797.5 over the four visible ones — and the maximum is 1,200. On Meters, SUM returns #N/A because one error poisons the whole calculation, while AGGREGATE with option 6 ignores the errors: a sum of 425, an average of 141.67, a second-largest value of 120 and a median of 120.

Step 2 — Read the sheet with the information SUBTOTAL uses

import pandas as pd
from openpyxl import load_workbook

wb = load_workbook("sales_subtotals.xlsx")                       # formulas, not values
ws = wb["Sales"]
rows = []
for r in range(2, ws.max_row + 1):
    amount = ws.cell(r, 3).value
    rows.append({"row": r, "Region": ws.cell(r, 1).value, "Rep": ws.cell(r, 2).value,
                 "Amount": amount if isinstance(amount, (int, float)) else None,
                 "is_subtotal": isinstance(amount, str) and amount.upper().startswith(("=SUBTOTAL(", "=_XLFN.AGGREGATE(")),
                 "hidden": bool(ws.row_dimensions[r].hidden)})
sales = pd.DataFrame(rows)
print(sales.to_string(index=False))

SUBTOTAL decides what to skip from two facts that a values-only read throws away: whether a cell contains a SUBTOTAL or AGGREGATE formula, and whether its row is hidden. openpyxl, loaded without data_only, still has both — the formula text in the cell and the hidden flag on the row dimension — so they become two boolean columns next to the data. Recognising subtotal rows by their formula is more reliable than looking for the word total in a label, which catches a rep called Totalcare and misses a row labelled Sum North. The subtotal rows' amounts are left empty, since the point is to recalculate them, not to trust them. The printed table shows the result: three subtotal rows flagged, and row 3 — Ben — marked as hidden.

Step 3 — SUBTOTAL in pandas

detail = sales[~sales["is_subtotal"]]
visible = detail[~detail["hidden"]]

naive = pd.read_excel("calculated/sales_subtotals.xlsx", sheet_name="Sales", usecols="A:C").iloc[:7]
results = {
    "naive sum of C2:C8": naive["Amount"].sum(),                 # what summing the column in pandas gives
    "SUBTOTAL(9)  sum": detail["Amount"].sum(),
    "SUBTOTAL(109) sum": visible["Amount"].sum(),
    "SUBTOTAL(1)  average": detail["Amount"].mean(),
    "SUBTOTAL(101) average": visible["Amount"].mean(),
    "SUBTOTAL(4)  max": detail["Amount"].max(),
}
for k, v in results.items():
    print(f"{k:24} {v:>10,.2f}")
print(detail.groupby("Region")["Amount"].sum().to_dict())      # the regional SUBTOTAL rows, rebuilt

With the two flags in place, every SUBTOTAL variant is a filter followed by an ordinary pandas aggregation. Function numbers 1 to 11 include hidden rows: drop the subtotal rows and aggregate. Numbers 101 to 111 ignore hidden rows: drop those too. Each number maps to one pandas method — 1 is mean, 2 count, 3 the count of non-blank cells, 4 max, 5 min, 6 prod, 7 and 8 the sample and population standard deviations, 9 sum, 10 and 11 the two variances. The results match LibreOffice to the cent: 3,990 and 3,190 for the sums, 798 and 797.5 for the averages, 1,200 for the maximum. The regional subtotals come back with a groupby — 2,000 for North and 1,990 for South — without ever reading the stored subtotal cells. The first line is the trap this page is about: reading the calculated workbook with read_excel and summing the column gives 7,980, exactly the double count of Excel's plain SUM, because the regional subtotals are ordinary numbers once the formulas are gone.

Step 4 — AGGREGATE: ignore the errors

raw = [c.value for c in load_workbook("calculated/sales_subtotals.xlsx", data_only=True)["Meters"]["B"][1:6]]
print("openpyxl sees:", raw)                                      # error values as text
meters = pd.read_excel("calculated/sales_subtotals.xlsx", sheet_name="Meters", usecols="A:B")
print("read_excel sees:", meters["Reading"].tolist())             # error values already NaN
readings = pd.to_numeric(pd.Series(raw), errors="coerce")         # the explicit route from openpyxl values
print({"AGGREGATE(9,6) sum": readings.sum(), "AGGREGATE(1,6) average": round(readings.mean(), 2),
       "AGGREGATE(14,6,k=2) 2nd largest": readings.nlargest(2).iloc[-1], "AGGREGATE(16,6,0.5) median": readings.quantile(0.5)})

The two reads show something worth knowing. openpyxl returns the stored error values as text — '#DIV/0!' and '#N/A' — while read_excel quietly turns them into NaN before you see them. So pandas, by default, already behaves like AGGREGATE option 6 rather than like Excel's SUM: errors vanish and sum, mean, median and nlargest skip them. That is convenient, and also a risk, because a column full of #REF! errors produces a perfectly plausible total from the few cells that still work. When errors matter, read the values with openpyxl, count the error strings, and then coerce them with pd.to_numeric(errors="coerce") as the code does. The four results match LibreOffice — a sum of 425, an average of 141.67, a second-largest reading of 120 and a median of 120. The function numbers map onto pandas in the same way as for SUBTOTAL, with the extras AGGREGATE adds: 12 is the median, 13 the mode, 14 and 15 nlargest and nsmallest with k, and 16 to 19 percentiles and quartiles via quantile.

A quick way to surface errors before they disappear is to count them per column straight from the openpyxl values — sum(isinstance(v, str) and v.startswith("#") for v in raw) — and stop or warn when the count is not zero. In a monthly report that reads a workbook maintained by hand, a sudden jump in error cells is usually the first sign that someone has deleted a sheet or a column the formulas depended on.

The AGGREGATE options, translated

AGGREGATE's second argument combines three switches: ignore nested subtotals, ignore hidden rows, ignore errors. Option 6 — errors only — is the most used, but it does not skip nested subtotals, which surprises people: an AGGREGATE(14, 6, …) asking for the largest value in a column that contains subtotal rows will happily return a subtotal. Options 0 to 3 skip nested subtotals; 1, 3, 5 and 7 skip hidden rows; 2, 3, 6 and 7 skip errors. In pandas each switch is one of the filters above — drop the subtotal rows, drop the hidden rows, coerce errors to NaN — so any option is a combination of the three.

Common pitfalls

• Summing a column that contains subtotal rows. pandas sees plain numbers; drop those rows first.
• Detecting subtotals by label. Check the formula with openpyxl instead.
• Reading values only. data_only=True and read_excel lose formulas and hidden-row flags.
• Errors as text. Coerce with pd.to_numeric(errors="coerce") before aggregating.
• Option 6 and nested subtotals. It ignores errors but still counts subtotal rows.

Conclusion

SUBTOTAL and AGGREGATE are filters plus an aggregation. Read the sheet with openpyxl to flag rows whose formula is a SUBTOTAL or AGGREGATE and rows that are hidden, drop the flagged rows the function number says to skip, coerce error values to NaN, and apply the matching pandas method. Recalculating the original workbook in LibreOffice gives the numbers to check against.

Frequently asked questions

What is the pandas equivalent of SUBTOTAL(9, …)? Drop the rows that contain SUBTOTAL formulas and sum the rest; use groupby(...).sum() to rebuild the subtotals themselves.

What is the difference between SUBTOTAL 9 and 109? Both skip nested subtotals; 109 also ignores rows that have been hidden manually.

How do I ignore #DIV/0! and #N/A when summing in pandas? Convert the column with pd.to_numeric(errors="coerce"), which turns error text into NaN, then sum; pandas skips NaN by default.

How do I find hidden rows in an Excel file with Python? Load it with openpyxl and check ws.row_dimensions[row].hidden for each row.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in pandas.
• Add Subtotals and Grand Totals to a pandas Pivot Table — building subtotals in pandas.
• IFERROR and ISNA Equivalents in pandas — error handling.
• Skip Hidden and Filtered Rows When Reading Excel — hidden rows when reading.