Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Add SUM and AVERAGE Formulas with Python

Generate SUM, AVERAGE, SUBTOTAL and conditional aggregate formulas from Python so a total row always covers the data you just wrote, however many rows there are.

A total row is the first thing a reader looks at and the first thing that breaks when next month's file has forty rows instead of thirty. The fix is to generate the range from the data you just wrote rather than typing it once and hoping. This guide, part of Working with Excel Formulas in Python, builds total rows that stay correct, then covers averages, filtered subtotals and conditional aggregates.

A total row generated from the last data row A sheet with a header row, three data rows and a blank spacer, then a total row whose formula reads SUM of D2 through D4. The range end is derived from the last appended row, so adding a fourth data row moves both the total row and the range end. SKU Qty Line_Total (D) A-100 4 79.96 B-200 2 99.00 C-300 7 85.75 spacer row Total =SUM(D2:D4) row 1 — header last = ws.max_row (row 4 here) total_row = last + 2 both move together when the data grows

Prerequisites

Bash
pip install openpyxl pandas

A total row that tracks the data

Write the rows, capture the last row number, then place the total two rows below with a range that ends exactly where the data does:

Python
from openpyxl import Workbook

rows = [
    ("A-100", 4, 19.99),
    ("B-200", 2, 49.50),
    ("C-300", 7, 12.25),
]

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["SKU", "Quantity", "Unit_Price", "Line_Total"])

for sku, qty, price in rows:
    ws.append([sku, qty, price])
    r = ws.max_row
    ws[f"D{r}"] = f"=B{r}*C{r}"

first_data, last_data = 2, ws.max_row
total_row = last_data + 2

ws[f"C{total_row}"] = "Total"
ws[f"D{total_row}"] = f"=SUM(D{first_data}:D{last_data})"
ws[f"C{total_row + 1}"] = "Average line"
ws[f"D{total_row + 1}"] = f"=AVERAGE(D{first_data}:D{last_data})"
ws[f"C{total_row + 2}"] = "Lines"
ws[f"D{total_row + 2}"] = f"=COUNT(D{first_data}:D{last_data})"

for r in range(total_row, total_row + 3):
    ws[f"D{r}"].number_format = "#,##0.00"

wb.save("orders_totals.xlsx")
print(ws[f"D{total_row}"].value)     # '=SUM(D2:D4)'

first_data and last_data are the only two numbers the formulas depend on, and both come from the data rather than from a constant. Add a fourth order and every formula moves with it — no edit required.

The spacer row is not decoration. A total immediately under the last data row gets swallowed when a reader turns the range into an Excel table or applies a filter, and it makes ws.max_row-based logic in the next run treat the total as data.

Totalling several columns at once

Most reports need the same aggregate across a block of columns. Generate them in a loop so a new column cannot be forgotten:

Python
from openpyxl import Workbook
from openpyxl.utils import get_column_letter

headers = ["Region", "Q1", "Q2", "Q3", "Q4"]
data = [
    ("North", 26700, 24100, 29800, 31200),
    ("South", 20900, 22400, 19850, 23100),
    ("West", 7600, 8100, 9400, 10250),
]

wb = Workbook()
ws = wb.active
ws.append(headers)
for row in data:
    ws.append(row)

last = ws.max_row
total_row = last + 2
ws[f"A{total_row}"] = "Total"

for col in range(2, len(headers) + 1):
    letter = get_column_letter(col)
    ws[f"{letter}{total_row}"] = f"=SUM({letter}2:{letter}{last})"
    ws[f"{letter}{total_row}"].number_format = "#,##0"

# A row total per region, and a grand total in the corner
grand_col = get_column_letter(len(headers) + 1)
ws[f"{grand_col}1"] = "Year"
for r in range(2, total_row + 1):
    ws[f"{grand_col}{r}"] = f"=SUM(B{r}:{get_column_letter(len(headers))}{r})"

wb.save("quarterly_totals.xlsx")

The corner cell now sums the row totals, which will agree with the sum of the column totals — a small built-in cross-check that catches a mis-generated range instantly when a reader opens the file.

SUBTOTAL when the sheet has a filter

SUM always covers every row in its range, including rows a filter has hidden. If you have added an autofilter so readers can slice the data, they will expect the total to follow their filter — that is SUBTOTAL:

Python
from openpyxl import load_workbook

wb = load_workbook("orders_totals.xlsx")
ws = wb["Orders"]

last_data = 4
total_row = 6

# 109 = SUM, ignoring rows hidden by a filter (9 would include manually hidden rows)
ws[f"D{total_row}"] = f"=SUBTOTAL(109,D2:D{last_data})"
ws.auto_filter.ref = f"A1:D{last_data}"

wb.save("orders_filtered_total.xlsx")
SUM and SUBTOTAL on a filtered sheet With a filter hiding the South row, SUM still returns the total of all three regions while SUBTOTAL 109 returns the total of only the two visible rows. filter active: region ≠ South North 26,700 South (hidden) 20,900 West 7,600 =SUM(B2:B4) 55,200 — includes hidden rows =SUBTOTAL(109,B2:B4) 34,300 — visible rows only The reader sees two rows and expects the total to match.

The function number matters: 109 ignores filtered and manually hidden rows, while 9 ignores only filtered ones. For averages the pair is 101 and 1. A nested SUBTOTAL inside a range is skipped automatically, which is what stops group subtotals being double-counted in a grand total.

Conditional aggregates: SUMIF and AVERAGEIF

When the total needs a condition, generate the criteria range and the criteria cell rather than embedding a literal:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Region", "Rep", "Revenue"])
data = [
    ("North", "Ada", 12000), ("North", "Ben", 14700),
    ("South", "Cal", 9800), ("South", "Dee", 11100),
    ("West", "Eli", 7600),
]
for row in data:
    ws.append(row)
last = ws.max_row

summary_start = last + 2
ws[f"A{summary_start}"] = "Region"
ws[f"B{summary_start}"] = "Revenue"
ws[f"C{summary_start}"] = "Average deal"

for i, region in enumerate(sorted({r[0] for r in data}), start=summary_start + 1):
    ws[f"A{i}"] = region
    ws[f"B{i}"] = f"=SUMIF($A$2:$A${last},$A{i},$C$2:$C${last})"
    ws[f"C{i}"] = f"=AVERAGEIF($A$2:$A${last},$A{i},$C$2:$C${last})"
    ws[f"B{i}"].number_format = "#,##0"
    ws[f"C{i}"].number_format = "#,##0.0"

wb.save("orders_by_region.xlsx")

Every range is anchored with $ and only the criteria cell ($A{i}) moves, so the block behaves exactly like a formula the reader dragged down. If the conditions come from more than one column, SUMIFS takes the value range first and then criteria pairs — the argument order is reversed compared with SUMIF, which is a frequent source of #VALUE!.

Picking the right aggregate function for a generated total Four cards: SUM for every row regardless of filters, SUBTOTAL 109 for the visible rows under a filter, SUMIF for one condition, and SUMIFS for several conditions with the value range first. SUM every row in the range use when the total must always be the full picture SUBTOTAL(109…) visible rows only nested ones skipped use when a filter is on the sheet SUMIF criteria range first values last use for one condition per cell SUMIFS values FIRST then pairs argument order is reversed vs SUMIF

Cross-checking the totals in Python

A generated formula that points at the wrong range still looks plausible. Compute the same aggregate in pandas and compare before you ship the file:

Python
import pandas as pd

df = pd.read_excel("orders_by_region.xlsx", nrows=5)
expected = df.groupby("Region")["Revenue"].sum().to_dict()
print(expected)     # {'North': 26700, 'South': 20900, 'West': 7600}

Log those figures next to the file you produce. When a reader queries a number three weeks later, the log tells you what the data said at the time, independently of what Excel is showing.

Totals in a workbook you did not write

When the file arrives from someone else, the total row is wherever they put it. Locate it by content rather than by position, so a script that refreshes the numbers keeps working when a row is inserted above:

Python
from openpyxl import load_workbook

wb = load_workbook("supplier_report.xlsx")
ws = wb.active

label_col = "C"
total_cells = [
    cell for cell in ws[label_col]
    if isinstance(cell.value, str) and cell.value.strip().lower() in {"total", "grand total"}
]
if not total_cells:
    raise SystemExit("No total row found — check the label column")

total_row = total_cells[-1].row
last_data = total_row - 2
ws[f"D{total_row}"] = f"=SUM(D2:D{last_data})"
print("rewrote the total at row", total_row)

wb.save("supplier_report_refreshed.xlsx")

Searching for the label makes the script self-correcting: if the supplier adds a row of notes at the top, the total moves and so does the formula. Combine it with the formula inventory trick and you can log exactly which aggregates a third-party file contains before your job depends on them.

Common pitfalls and gotchas

SymptomCauseFix
Total misses the last rowsRange end was a constant from an earlier runBuild it from ws.max_row after appending
Total counts itself, growing each runTotal row sits inside the summed rangeLeave a spacer row and end the range at the last data row
AVERAGE ignores rowsNumbers stored as text, or blanksCoerce with pd.to_numeric before writing
Filtered total does not moveUsed SUM on a filtered sheetUse SUBTOTAL(109, …)
#DIV/0! from AVERAGENo numeric cells in the rangeWrap in IFERROR(…, "") or check the count first
SUMIFS returns #VALUE!Argument order confused with SUMIFSUMIFS(sum_range, criteria_range, criteria, …)

Performance and scale notes

SUM over a contiguous range is close to free even at hundreds of thousands of rows. The expensive patterns are per-row conditional aggregates: a SUMIF on each of 50,000 rows scans the whole criteria range 50,000 times, which is why files like that take a minute to open. When the summary is per-category rather than per-row, generate one formula per category — five formulas instead of fifty thousand — or aggregate in pandas with groupby and write literal values, keeping formulas only for the handful of cells the reader may want to inspect.

Conclusion

Total rows break for one reason: someone wrote the range once. Capture the first and last data rows as variables, generate every aggregate from them, leave a spacer so the total never joins the data, and pick SUBTOTAL whenever a filter is in play. Cross-check the result in pandas before the file leaves your machine, and the number at the bottom of the sheet will still be right next quarter.

Frequently asked questions

How do I make the total row grow with my data? Build the range from the last data row rather than a constant — track it as you append, or read ws.max_row immediately after writing the final data row.

Should I use SUM or SUBTOTAL? Use SUBTOTAL(109, range) when the sheet has a filter and the reader expects the total to reflect the visible rows. Use SUM when the total must always cover every row.

Why does AVERAGE ignore some of my rows?AVERAGE skips blanks and text. Numbers stored as text look like data to a reader but are invisible to the function, so coerce the column to numeric before writing it.

Can I total a whole column without knowing its length? Yes — =SUM(D:D) works, but it also picks up anything you later add to the column. Prefer an explicit range ending at the last data row.

Up to the parent guide:

Related guides: