Convert an Excel formula to pandas
If you are replacing a spreadsheet with a Python script, the formulas are the part to port carefully. SUMIFS becomes a boolean mask followed by sum, VLOOKUP becomes merge, and IF becomes numpy where. The Formula Translator writes the pandas version next to the plain-English explanation so you can see why each line exists.
Worked example: SUMIFS to pandas
Orders sheet: column B is Region, column C is Order date, column D is Revenue.
=SUMIFS(D2:D5000,B2:B5000,"West",C2:C5000,">="&DATE(2026,1,1))import pandas as pd
orders["order_date"] = pd.to_datetime(orders["order_date"])
mask = (orders["region"] == "West") & (orders["order_date"] >= "2026-01-01")
west_revenue = orders.loc[mask, "revenue"].sum()Each criteria pair becomes a comparison joined with &, and every comparison needs its own parentheses. Convert the date column first, otherwise pandas compares text. sum() on an empty selection returns 0, matching SUMIFS. For COUNTIFS take len() of the filtered frame, and for a total per region use orders.groupby("region")["revenue"].sum() instead of a fixed filter.
How it works
- 01Paste the formula into the Formula Translator.
- 02Open the pandas tab and copy the code.
- 03Rename the DataFrame and columns to match your data.
- 04Check dtypes with df.dtypes, since numbers and dates read as text are the most common bug.
Common questions
- How do I convert VLOOKUP to pandas?
- Use merge with how="left" on the key column. Drop duplicate keys from the lookup table first, because VLOOKUP takes only the first match and merge keeps them all.
- What replaces IF in pandas?
- numpy.where(condition, value_if_true, value_if_false) for a single test, and numpy.select for several, which is the equivalent of IFS.
- Does it handle blank cells?
- Blank cells load as NaN in pandas. Most aggregates skip NaN, but comparisons with NaN are False, so check for them explicitly when it matters.
Try it on your own formula
The Formula Translator is free for 5 formulas a day, with no account.