Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

SUMIF and SUMIFS Equivalent in pandas

Translate conditional totals into pandas: boolean masks, several conditions joined with &, wildcards as string methods, and transform for a SUMIF copied down a column.

SUMIF and SUMIFS are the workhorses of a real spreadsheet, and they are the first formulas anybody automating a report has to reproduce. The pandas equivalent is not a function but a pattern: build a boolean mask, select rows with it, and sum the column you want. This guide, part of Excel Formula Equivalents in pandas, covers the one-condition case, the multi-condition case, and the grouped form that replaces a formula copied down 50,000 rows.

SUMIF decomposed into the three steps pandas keeps separate A comparison produces a boolean mask, .loc uses the mask to select matching rows, and .sum totals the chosen column — the three operations Excel bundles into one function. mask, select, aggregate build a mask Region == 'North' select rows .loc[mask, 'Revenue'] aggregate .sum() each step is an object you can print, which a formula is not

Prerequisites

Bash
pip install pandas openpyxl

Every example below runs against this sample workbook:

Python
import pandas as pd

sales = pd.DataFrame({
    "Region": ["North", "South", "North", "West", "South", "North", "West"],
    "Rep": ["Ana", "Ben", "Cara", "Dev", "Eve", "Ana", "Dev"],
    "Product": ["Widget", "Gadget", "Widget", "Widget", "Gadget", "Gadget", "Widget"],
    "Revenue": [12400.0, 9800.5, 15320.25, 7010.0, 4300.75, 6120.0, 11450.5],
    "Units": [124, 98, 153, 70, 43, 61, 114],
    "Ordered": pd.to_datetime([
        "2026-01-04", "2026-01-19", "2026-02-02", "2026-02-27",
        "2026-03-08", "2026-03-15", "2026-03-30",
    ]),
})
sales.to_excel("sales.xlsx", sheet_name="Detail", index=False)

SUMIF with one condition

Python
# =SUMIF(A:A, "North", D:D)
north = sales.loc[sales["Region"] == "North", "Revenue"].sum()
print(f"{north:,.2f}")

Three things are happening, and separating them is what makes everything later easy. sales["Region"] == "North" produces a boolean Series — one True or False per row. .loc[mask, "Revenue"] selects the Revenue values from the rows where the mask is True. .sum() totals them.

Because the mask is an ordinary object you can inspect it, which Excel does not let you do:

Python
mask = sales["Region"] == "North"
print(f"{mask.sum()} of {len(mask)} rows match")
print(sales.loc[mask, ["Rep", "Revenue"]])

That inspection step is where translation bugs get caught. A SUMIF returning an unexpected total gives you no way to see which rows it added; a mask shows you immediately.

SUMIFS with several conditions

Conditions combine with & for AND and | for OR. The parentheses are mandatory — Python's operator precedence binds & more tightly than ==, so omitting them produces a confusing error about ambiguous truth values.

Python
# =SUMIFS(D:D, A:A, "North", C:C, "Widget")
mask = (sales["Region"] == "North") & (sales["Product"] == "Widget")
print(sales.loc[mask, "Revenue"].sum())

# =SUMIFS(D:D, D:D, ">10000", A:A, "<>West")
mask = (sales["Revenue"] > 10000) & (sales["Region"] != "West")
print(sales.loc[mask, "Revenue"].sum())

Excel expresses comparisons as strings — ">10000" — because a formula argument has to be text. pandas uses real operators, which means no quoting rules to remember and no silent failure when a criteria string is malformed.

Dates work the same way, and a half-open interval avoids the boundary double-count that catches people summing month by month:

Python
# =SUMIFS(D:D, F:F, ">=2026-02-01", F:F, "<2026-03-01")
february = (sales["Ordered"] >= "2026-02-01") & (sales["Ordered"] < "2026-03-01")
print(sales.loc[february, "Revenue"].sum())

The grouped form that replaces a copied-down SUMIF

A copied-down SUMIF against one grouped pass Filling a SUMIF down fifty thousand rows re-scans the column for every row, while groupby with transform computes each group's total once and aligns it back to every member row. SUMIF filled down 50,000 formulas one scan each recalculates on edit transform('sum') one pass total aligned to each row computed once same result the answers match; the work does not

A SUMIF written into every row of a 50,000-row sheet scans the whole column 50,000 times, which is why those workbooks take a minute to open. The pandas equivalent scans once and distributes the answer.

Python
# Excel: =SUMIF(A:A, A2, D:D) filled down the sheet
sales["Region total"] = sales.groupby("Region")["Revenue"].transform("sum")

# Several keys, several aggregates — no extra passes
sales["Region product total"] = sales.groupby(["Region", "Product"])["Revenue"].transform("sum")
sales["Share of region"] = sales["Revenue"] / sales["Region total"]
print(sales[["Region", "Product", "Revenue", "Region total", "Share of region"]])

transform is the function to reach for whenever the requirement is "give every row its group's total". The share calculation on the last line is the reason it matters: with the group total on each row, a percentage-of-total column is one division rather than a second SUMIF.

Producing a summary table instead

When the output is a summary rather than an enriched detail sheet, groupby().agg() gives it directly — and gives several aggregates in one pass, which SUMIFS cannot do at all.

Python
summary = sales.groupby("Region", as_index=False).agg(
    Revenue=("Revenue", "sum"),
    Units=("Units", "sum"),
    Orders=("Revenue", "size"),
    Best=("Revenue", "max"),
)
summary["Average order"] = (summary["Revenue"] / summary["Orders"]).round(2)
print(summary.sort_values("Revenue", ascending=False))

Naming the outputs in agg is worth the extra characters: the resulting frame has flat, meaningful column names rather than a multi-level index that then has to be flattened before it can be written to a sheet. That single habit removes most of the friction between an aggregation and Write a Pandas DataFrame to Excel Without the Index.

Wildcards and partial matches

Excel's criteria accept * and ?. The pandas equivalents are string methods, which are more capable and slightly more verbose.

Python
# =SUMIF(C:C, "Widg*", D:D)
starts = sales["Product"].str.startswith("Widg", na=False)
print(sales.loc[starts, "Revenue"].sum())

# =SUMIF(B:B, "*a*", D:D) — contains
contains = sales["Rep"].str.contains("a", case=False, na=False)
print(sales.loc[contains, "Revenue"].sum())

# Several values at once — Excel needs one SUMIF per value plus addition
chosen = sales["Region"].isin(["North", "West"])
print(sales.loc[chosen, "Revenue"].sum())

na=False is not optional in practice. Without it, a missing value in the column makes str.contains return NaN for that row, and using a mask containing NaN raises rather than skipping the row — a failure that only appears once real data arrives.

isin deserves particular attention because it has no clean Excel equivalent at all. A SUMIF over five acceptable values means five formulas added together, which is exactly the kind of thing that silently stops being maintained when a sixth value appears.

Weighted totals, and the SUMPRODUCT case

Once a report grows past simple conditional sums it usually reaches for SUMPRODUCT, which multiplies arrays element by element before totalling them. That is a natural operation on columns, so it reads better in pandas than it does in a formula bar.

Python
# =SUMPRODUCT((A2:A100="North")*(D2:D100)*(E2:E100))
mask = sales["Region"] == "North"
weighted = (sales["Revenue"] * sales["Units"]).loc[mask].sum()

# A weighted average price, which SUMIF alone cannot express
by_region = sales.groupby("Region").apply(
    lambda part: (part["Revenue"] * part["Units"]).sum() / part["Units"].sum(),
    include_groups=False,
)
print(by_region.round(2))

The include_groups=False argument keeps recent pandas versions from warning about the grouping column being passed into the function; without it the code still works but emits a deprecation notice that clutters a scheduled job's log.

Weighted aggregates are the point where a spreadsheet usually starts to sprawl into helper columns — a product column here, a subtotal row there — and where moving the calculation into Python collapses several sheets into a handful of lines. The helper columns were only ever there because a formula could not hold two operations at once.

Reproducing a subtotal row

Reports frequently end with a total row, and a SUMIF-based sheet gets it from another formula. In pandas the equivalent is to compute the summary and append the total explicitly, which has the advantage of being visible rather than hidden in row 200.

Python
summary = sales.groupby("Region", as_index=False)["Revenue"].sum()
total = pd.DataFrame({"Region": ["Total"], "Revenue": [summary["Revenue"].sum()]})
with_total = pd.concat([summary, total], ignore_index=True)
print(with_total)

Building the total from the summary rather than from the source frame is deliberate: it guarantees the total equals the sum of the rows above it, which is exactly the property that breaks in a spreadsheet when a SUM range stops one row short of the data. If the two ever need to disagree — because some rows are excluded from the breakdown — that becomes an explicit decision rather than a range that quietly drifted.

Common pitfalls

SymptomCauseFix
ValueError: The truth value of a Series is ambiguousand/or used instead of &/|Use the bitwise operators and parenthesise each condition
Total is 0 or values are concatenatedThe column is text, not numericpd.to_numeric(col, errors="coerce") before summing
Fewer rows match than in ExcelTrailing spaces or case differences in the key.str.strip() and .str.casefold() first
str.contains raises on missing valuesNaN in the columnPass na=False
Boundary dates counted twiceTwo inclusive comparisonsUse a half-open interval: >= start and < end
transform result is all NaNGrouping on a column containing NaNdropna=False on groupby, or fill the key first

Performance and scale

Conditional totals over 200,000 rows A vectorised masked sum in pandas finishes in milliseconds, a grouped transform is barely slower, and a copied-down SUMIF recalculated by Excel is orders of magnitude more work. SUMIF filled down quadratic scanning groupby transform one pass masked sum one pass relative cost the gap widens with every row added

The performance difference is not marginal. A conditional total over 200,000 rows is a single vectorised pass in pandas — milliseconds — while the same work as a copied-down SUMIF is a quadratic amount of scanning that Excel repeats on every recalculation.

Two habits keep the pandas version fast as data grows. Compute masks once and reuse them rather than rebuilding the same comparison for each aggregate. And prefer a single groupby().agg() over several separate masked sums when the groups are the same, because each pass over the frame costs something even when that something is small.

Python
# One pass, three answers
by_region = sales.groupby("Region")["Revenue"].agg(["sum", "mean", "count"])

# Rather than three passes over the same rows
north = sales.loc[sales["Region"] == "North", "Revenue"]

For genuinely large files, the read is the bottleneck long before the aggregation is — which is the argument in Read a Large Excel File in Chunks with Pandas.

Conclusion

SUMIF becomes df.loc[mask, col].sum(), SUMIFS becomes the same thing with conditions joined by &, and a SUMIF copied down a column becomes groupby().transform("sum") — one pass instead of one per row. Build the mask as a named variable so you can inspect what matched, coerce numeric columns before summing, and reach for groupby().agg() when the output is a summary table rather than an enriched detail sheet.

Frequently asked questions

How do I write SUMIFS with a date range? Build the mask with two comparisons combined by &, exactly as you would for any other pair of conditions: (df'Ordered' >= start) & (df'Ordered' < end). Using a half-open interval avoids the classic double-counting of the boundary day.

What is the difference between transform('sum') and groupby().sum()? groupby().sum() returns one row per group. transform('sum') returns one value per original row — the group's total, aligned back to every member. transform is the direct equivalent of a SUMIF copied down a column.

Why does my sum come out as 0 or as concatenated text? The column is text rather than numeric. Excel coerces silently; pandas does not. Run pd.to_numeric(col, errors='coerce') first, then check how many NaN values it produced.

How do I reproduce SUMIF's wildcard criteria? Use .str.contains() with a regular expression, or .str.startswith(). Excel's asterisk becomes .* and its question mark becomes a single-character match; remember to pass na=False so missing values do not raise.

Can I sum several columns at once? Yes — pass a list of column names to the selection: df.locmask, 'Revenue', 'Units'.sum(). Excel needs a separate SUMIFS per column, which is one of the places the translation is plainly better.