Guide

Advanced Data Transformation And CleaningDeep dive

Excel Formula Equivalents in pandas

Translate SUMIF, COUNTIF, VLOOKUP, INDEX MATCH, nested IFs and text functions into pandas — with the mask-select-aggregate pattern behind all of them.

Most Python Excel work starts with a workbook that already computes the answer. Somebody has spent years building SUMIFs, an INDEX/MATCH pair and a column of nested IFs, and the automation project has to produce the same numbers without them. That translation is mechanical once you see the pattern behind it, and this section of Advanced Data Transformation and Cleaning walks through it function by function.

The single idea that makes the translation easy: an Excel formula computes one cell at a time and pandas computes a whole column at once. A SUMIF asks "for this row's criteria, total the matching rows"; the pandas version computes every group's total once and looks the answer up. The result is the same and the amount of work is very different.

The shape of every translation

Excel's conditional functions bundle two operations that pandas keeps apart: choosing rows, and doing something with them. Separating them is what makes the pandas version more capable rather than merely different.

Python

import pandas as pd

sales = pd.DataFrame({
    "Region": ["North", "South", "North", "West", "South"],
    "Rep": ["Ana", "Ben", "Cara", "Dev", "Eve"],
    "Product": ["Widget", "Gadget", "Widget", "Widget", "Gadget"],
    "Revenue": [12400.0, 9800.5, 15320.25, 7010.0, 4300.75],
    "Ordered": pd.to_datetime(["2026-01-04", "2026-01-19", "2026-02-02", "2026-02-27", "2026-03-08"]),
})

# =SUMIF(A:A, "North", D:D)
north_total = sales.loc[sales["Region"] == "North", "Revenue"].sum()

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

sales["Region"] == "North" is a boolean Series — a column of True and False the same length as the frame. .loc uses it to select rows, and .sum() aggregates what is left. Every conditional function in Excel decomposes into those three steps, and once you see it the rest of this section is detail.

The function-by-function map

Nine Excel functions cover most of what a working spreadsheet contains, and each has a direct counterpart. The map below is the summary; the guides under this topic take them one at a time with runnable examples and the edge cases that catch people.

Excel	pandas	Notes
`SUMIF` / `SUMIFS`	`df.loc[mask, col].sum()` or `groupby().sum()`	Group once instead of per row
`COUNTIF` / `COUNTIFS`	`mask.sum()` or `value_counts()`	Booleans sum as ones and zeros
`AVERAGEIF`	`df.loc[mask, col].mean()`	`mean` skips NaN; `AVERAGEIF` skips blanks
`VLOOKUP` / `XLOOKUP`	`df.merge(other, on=key, how="left")`	Direction of lookup stops mattering
`INDEX` + `MATCH`	`merge`, or `.map()` from a Series	`map` is the closest one-column form
`IF`	`np.where(cond, a, b)`	Nested IFs become `np.select`
`IFS` / nested `IF`	`np.select(conditions, choices)`	Order matters, first match wins
`LEFT` / `RIGHT` / `MID`	`.str[:n]`, `.str[-n:]`, `.str[a:b]`	Slicing, not functions
`RANK` / `PERCENTILE`	`.rank()`, `.quantile()`	Tie handling is explicit

Python

import numpy as np

# =IF(D2>10000, "Large", "Standard")
sales["Tier"] = np.where(sales["Revenue"] > 10000, "Large", "Standard")

# =IFS(D2>15000,"A", D2>10000,"B", TRUE,"C")
sales["Grade"] = np.select(
    [sales["Revenue"] > 15000, sales["Revenue"] > 10000],
    ["A", "B"],
    default="C",
)

Group once, look up many times

The most common translation mistake is to keep Excel's shape — computing a total per row — instead of adopting the pandas one. A SUMIF written into 50,000 rows scans the column 50,000 times. The pandas equivalent scans it once.

Python

# Excel: =SUMIF(A:A, A2, D:D) copied down 50,000 rows
region_totals = sales.groupby("Region")["Revenue"].sum()
sales["Region total"] = sales["Region"].map(region_totals)

# Or in one step, which is the idiom worth learning
sales["Region total"] = sales.groupby("Region")["Revenue"].transform("sum")

transform is the direct answer to "give every row its group's aggregate" — the exact thing a copied-down SUMIF does — and it is the function that makes most spreadsheet translations collapse to a single line. map from a grouped Series does the same job when you already have the totals for another purpose.

Lookups, and why direction stops mattering

VLOOKUP's most-cursed limitation is that the key must sit to the left of the value. merge joins on a column name, so the physical order of columns is irrelevant, and a lookup that Excel needs INDEX/MATCH for is the same call as one VLOOKUP could handle.

Python

targets = pd.DataFrame({
    "Region": ["North", "South", "West"],
    "Target": [30000.0, 20000.0, 12000.0],
    "Owner": ["Ana", "Ben", "Dev"],
})

# =VLOOKUP(A2, targets, 2, FALSE) — and the third column, in the same call
enriched = sales.merge(targets, on="Region", how="left")
print(enriched[["Region", "Revenue", "Target", "Owner"]].head())

how="left" is the part that maps onto VLOOKUP's semantics: every original row survives, and a key with no match gets NaN where Excel would show #N/A. Checking for those unmatched rows straight after the merge is the equivalent of wrapping the formula in IFERROR, except that it tells you how many failed rather than hiding them one at a time.

Python

missing = enriched.loc[enriched["Target"].isna(), "Region"].unique()
if len(missing):
    print(f"no target defined for: {list(missing)}")

VLOOKUP Equivalent in pandas for Excel Files covers the join semantics in full, including the many-to-one duplication that silently inflates row counts.

Where the two engines genuinely disagree

Three differences produce most of the "the numbers do not match" reports, and all three are worth checking before a formula is retired.

Blanks and zeros. Excel's AVERAGEIF ignores blank cells but includes zeros; pandas' mean ignores NaN and includes zeros too — but a blank cell read from a sheet may arrive as an empty string rather than NaN, in which case the column is text and the mean is not computed at all. The check is one line: frame.dtypes.

Text that looks numeric. A column of numbers stored as text totals to zero in pandas and works fine in Excel, because Excel coerces silently. pd.to_numeric(col, errors="coerce") makes the coercion explicit and leaves NaN where a value was not a number — which then shows up in a null count rather than in a wrong total.

Rounding and display. Excel shows a rounded value and stores the full precision, so a total that looks like it should be 1,000.00 can differ in the cents. Comparing with numpy.isclose rather than == avoids chasing a difference that only exists at the fifteenth decimal place.

Python

import numpy as np

sales["Revenue"] = pd.to_numeric(sales["Revenue"], errors="coerce")
matches = np.isclose(sales["Revenue"].sum(), 48831.5, atol=0.005)
print("totals agree:", matches)

The cleaning steps that precede any of this — stripping whitespace, fixing types, dropping blank rows — are in Cleaning Excel Data with Pandas.

Validating a translation against the original

The reliable way to retire a formula is to run both for one period and compare, rather than to reason about whether they agree. Read the workbook, compute your version alongside the sheet's existing column, and print the rows where they differ.

Python

import pandas as pd

sheet = pd.read_excel("current-report.xlsx", sheet_name="Detail")
sheet["Python total"] = sheet.groupby("Region")["Revenue"].transform("sum")

drift = sheet.loc[
    ~np.isclose(sheet["Excel total"], sheet["Python total"], atol=0.005),
    ["Region", "Excel total", "Python total"],
]
print(f"{len(drift)} row(s) differ")
print(drift.head(20))

Nine times out of ten the differences are informative rather than embarrassing: a stale range in the SUMIF that stopped at row 5,000, a region spelled two ways, a filter left applied when the workbook was saved. Finding those is usually worth more than the automation itself. Compare Two Excel Files for Differences with Python generalises the comparison to whole workbooks.

What should stay in the workbook

Not every formula is worth translating. Anything whose meaning depends on the sheet — a cell reference, a conditional format, a subtotal that follows a filter — belongs where it is. So does a display formula the recipients edit themselves: replacing it with a static value written by a script removes a capability they were using.

The rule that holds up: translate the formulas that produce the report's numbers, and leave the ones that produce its behaviour. A workbook where Python writes the values and Excel keeps a handful of presentation formulas is not a compromise — it is usually the right design, and Working with Excel Formulas in Python covers writing those remaining formulas from code.

Array formulas and the modern dynamic functions

Excel's newer functions — FILTER, UNIQUE, SORT, SEQUENCE, LET — describe operations on whole ranges rather than single cells, which makes them much closer to pandas than the classics they replace. The translations are correspondingly direct.

Python

import pandas as pd

# =FILTER(A2:D200, (A2:A200="North")*(D2:D200>10000))
filtered = sales[(sales["Region"] == "North") & (sales["Revenue"] > 10000)]

# =UNIQUE(A2:A200)
regions = sales["Region"].drop_duplicates()

# =SORT(A2:D200, 4, -1)
ranked = sales.sort_values("Revenue", ascending=False)

# =SUMPRODUCT((A2:A200="North")*(C2:C200="Widget")*D2:D200)
weighted = ((sales["Region"] == "North") & (sales["Product"] == "Widget")).mul(sales["Revenue"]).sum()

SUMPRODUCT is worth singling out because it is the function experienced spreadsheet authors reach for when SUMIFS runs out of expressiveness, and its translation is the most literal of all: the multiplication of boolean arrays in Excel is exactly the & of boolean Series in pandas, and the final sum is the same sum. Anyone who has written a lot of SUMPRODUCT formulas already thinks in columns, and will find pandas familiar rather than foreign.

The legacy array formulas entered with Ctrl+Shift+Enter translate the same way, and are often the best candidates to move: they are the slowest thing a workbook can contain, they break silently when a range is resized, and nobody remembers how they work.

Reading the formulas out of the workbook first

Before translating anything it helps to know what is actually there. openpyxl reads formulas as text, so a short script can inventory every distinct formula pattern in a workbook and count how often each appears — which turns "translate the spreadsheet" into a list ordered by importance.

Python

import re
from collections import Counter
from openpyxl import load_workbook

book = load_workbook("current-report.xlsx")           # formulas, not cached values
patterns = Counter()
for sheet in book.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                shape = re.sub(r"\b[A-Z]{1,3}\d+\b", "REF", cell.value)
                patterns[shape] += 1

for shape, count in patterns.most_common(15):
    print(f"{count:6d}  {shape[:90]}")

Replacing cell references with a placeholder collapses ten thousand copied-down formulas into a single pattern with a count beside it, which is exactly the view needed to decide what to translate first. It also surfaces the surprises — a formula that appears once in the middle of a column of identical ones, which is nearly always where a manual override is hiding. Read Formula Results with openpyxl data_only covers the other half of that read: getting the values those formulas produced.

Writing the answers back where the formulas were

A translation project has an awkward middle stage: the numbers are computed in Python but the recipients still expect the workbook they know. The workable pattern is to keep the layout and replace only the values, which openpyxl does without disturbing the styling, the header block or the charts pointing at the range.

Python

import pandas as pd
from openpyxl import load_workbook

summary = (
    pd.read_excel("source-data.xlsx", sheet_name="Detail")
      .groupby("Region", as_index=False)["Revenue"].sum()
      .sort_values("Revenue", ascending=False)
)

book = load_workbook("monthly-report.xlsx")
sheet = book["Summary"]
for offset, (region, revenue) in enumerate(summary.itertuples(index=False), start=0):
    sheet.cell(row=4 + offset, column=2, value=region)
    sheet.cell(row=4 + offset, column=3, value=float(revenue))
book.save("monthly-report-2026-09.xlsx")

Two details make this safe over time. Writing float(revenue) rather than the NumPy scalar avoids the type that openpyxl cannot serialise — a failure that appears as an unhelpful error about an unsupported value. And saving under a new name keeps the template intact, so a bad run costs a minute rather than the original file.

The alternative — deleting the formula column and appending a new one — moves every cell to the right of it and breaks anything that referenced those positions, which is the specific hazard described in Populate an Excel Template Without Losing Formatting. Writing values into the cells that already exist avoids the whole category.

Deciding how far to take it

There is a point of diminishing returns, and it is worth naming. Translating the formulas that produce a report's headline numbers usually pays for itself immediately: those are the ones that break silently when a range stops at row 5,000, and the ones that make a workbook take a minute to open. Translating the last few — a percentage in a footer, a conditional label in a status column — buys very little and costs the recipients the ability to adjust them.

A practical stopping rule is to translate everything that feeds a number somebody makes a decision on, and leave everything that only affects how the sheet reads. That keeps the workbook editable where editing is legitimate, keeps the automation responsible for the parts that must be right, and leaves a clean line between the two that the next person can see without being told.

Dates: the functions with the most hidden differences

Date formulas translate cleanly in form and badly in detail, because the two systems disagree about what a date is. Excel stores a serial number counting days from 1899-12-30 and applies a display format; pandas stores a timestamp with nanosecond precision. Most of the resulting confusion comes from that gap rather than from the functions themselves.

Python

import pandas as pd

# =YEAR(E2), =MONTH(E2), =TEXT(E2,"YYYY-MM")
sales["Year"] = sales["Ordered"].dt.year
sales["Month"] = sales["Ordered"].dt.month
sales["Period"] = sales["Ordered"].dt.strftime("%Y-%m")

# =EOMONTH(E2, 0) and =EDATE(E2, 3)
sales["Month end"] = sales["Ordered"] + pd.offsets.MonthEnd(0)
sales["In three months"] = sales["Ordered"] + pd.DateOffset(months=3)

# =NETWORKDAYS(E2, TODAY())
sales["Working days"] = [
    len(pd.bdate_range(start, pd.Timestamp.today().normalize())) for start in sales["Ordered"]
]

MonthEnd(0) is the one worth memorising: with an offset of zero it snaps a date already at month-end to itself, where MonthEnd(1) would push it forward a month — the same off-by-one that EOMONTH's second argument produces in a spreadsheet.

NETWORKDAYS has no vectorised equivalent because holiday calendars vary; bdate_range handles the weekend part and a custom holidays argument covers the rest. The wider set of date problems that an Excel export produces — serials arriving as integers, timezones, quarter grouping — is covered in Working with Dates and Times in Excel Data.

Key takeaways

Every conditional Excel function decomposes into a boolean mask, a selection and an aggregation; once you see that, the translations are mechanical.
transform is the direct equivalent of a copied-down SUMIF or COUNTIF — one pass over the column instead of one per row.
merge replaces VLOOKUP, INDEX/MATCH and XLOOKUP alike, and the lookup direction stops being a constraint.
np.where handles a single IF and np.select handles nested ones, with first-match-wins ordering.
Mismatched totals almost always come from text-typed numbers, blanks that are empty strings, or rounding — check dtypes before doubting the logic.
Validate by running both versions side by side for one period; the differences usually reveal a bug in the spreadsheet.

Frequently asked questions

Why is there no single pandas function called SUMIF? Because SUMIF collapses two ideas that pandas keeps separate: selecting rows and aggregating them. Once they are separate you can filter on anything, aggregate several columns at once, and group by more than one key — none of which SUMIF can express.

Do I have to rewrite every formula in the workbook? No, and usually you should not. Translate the formulas that feed the numbers a report depends on, and leave presentational ones in the sheet. A hybrid workbook where Python writes values and Excel keeps a few display formulas is a perfectly stable arrangement.

How do I keep the same answers as the spreadsheet? Rebuild the formula's result in pandas, write it beside the original for one period, and compare. Differences almost always come from rounding, from blank cells counted differently, or from a filter that silently excluded rows — all of which are worth finding before the formula is retired.

Which Excel functions have no pandas equivalent? The ones that describe the sheet rather than the data: CELL, INDIRECT, OFFSET with a moving anchor, and anything that depends on cell addresses. If a formula's meaning depends on where it sits, it belongs in the workbook.

Is a pandas version always faster? For a few hundred rows the difference is invisible. Past tens of thousands it is dramatic, because Excel recalculates a formula per cell while pandas performs one vectorised operation per column — and array formulas across large ranges are the slowest thing a workbook can contain.

What about XLOOKUP? XLOOKUP maps onto merge in exactly the way VLOOKUP does, with the added conveniences — searching right to left, an if-not-found value — coming free because merge never cared about column order in the first place.

Up one level: Advanced Data Transformation and Cleaning — the wider cleaning and reshaping toolkit.
SUMIF and SUMIFS Equivalent in pandas — conditional totals, one condition or several.
COUNTIF and COUNTIFS Equivalent in pandas — counting rows that match, and counting distinct values.
INDEX MATCH Equivalent in pandas — lookups in any direction, with map and merge.
Excel IF Formulas as pandas Conditional Columns — np.where, np.select and the nested-IF ladder.
Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas — string slicing and joining on whole columns.
RANK and PERCENTILE Formulas in pandas — ranking with explicit tie handling, and quantiles.
Running Totals and Year-Over-Year Growth in pandas — cumulative sums and period comparisons without dragging a formula down.
VLOOKUP Equivalent in pandas for Excel Files — the join semantics behind every lookup translation.