AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas

AVERAGEIFS, MAXIFS and MINIFS answer questions like "average order value in the North this quarter" or "largest deal each rep closed". They filter a range by one or more criteria and aggregate what is left. In pandas the filter is a boolean mask and the aggregate a method call, but a faithful translation also has to match what Excel does with blanks, text and — the classic surprise — a criterion that matches nothing. This guide, part of Excel Formula Equivalents in pandas, covers the one-off, table and per-row forms. Conditional sums and counts have their own guides: SUMIF and SUMIFS and COUNTIF and COUNTIFS.

Prerequisites

pip install pandas openpyxl

A sales sheet with the usual imperfections — a blank amount and a "TBC" typed into a numeric column:

import datetime as dt

import pandas as pd

sales = pd.DataFrame({
    "Region": ["North", "North", "South", "North", "North", "East"],
    "Rep": ["Ann", "Bob", "Cai", "Cai", "Ann", "Dee"],
    "Date": [dt.date(2026, 1, 3), dt.date(2026, 1, 15), dt.date(2026, 2, 1),
             dt.date(2026, 2, 20), dt.date(2026, 3, 2), dt.date(2026, 3, 5)],
    "Amount": [120, None, 300, "TBC", 80, 50],
})
sales.to_excel("sales.xlsx", index=False)
df = pd.read_excel("sales.xlsx")
amount = pd.to_numeric(df["Amount"], errors="coerce")
print(amount.tolist())        # [120.0, nan, 300.0, nan, 80.0, 50.0]

AVERAGEIFS silently skips blank and text cells in the average range. pd.to_numeric(..., errors="coerce") turns both into NaN, and pandas' mean, max and min skip NaN by default, so the two behave the same. Keep a count of the coerced values, though — a "TBC" is information that Excel hid from you.

Step 1 — One result with several criteria

=AVERAGEIFS(D:D, A:A, "North", C:C, ">="&DATE(2026,1,1), C:C, "<"&DATE(2026,4,1)) becomes a mask per criterion, combined with &:

north_q1 = (
    (df["Region"] == "North")
    & (df["Date"] >= "2026-01-01")
    & (df["Date"] < "2026-04-01")
)
print(amount[north_q1].mean(), amount[north_q1].max(), amount[north_q1].min())
# 100.0 120.0 80.0

Four North rows match the criteria, but only two have numeric amounts — the blank and "TBC" are skipped — so the average is (120 + 80) / 2 = 100. Recalculating the same formula in a spreadsheet gives 100 too. Wrap each condition in parentheses: & binds more tightly than == and >=, and leaving them out raises a confusing error.

Step 2 — Match Excel when nothing matches

Here the two tools really differ. With a criterion that matches no rows, Excel's MAXIFS and MINIFS return 0, while AVERAGEIFS returns #DIV/0!. pandas returns NaN for all three:

west = df["Region"] == "West"
print(amount[west].mean(), amount[west].max())       # nan nan

If a translated report must reproduce the workbook's numbers exactly, fill the max and min with 0:

def maxifs(values, mask, excel_compatible=True):
    result = values[mask].max()
    return 0 if excel_compatible and pd.isna(result) else result

print(maxifs(amount, west), maxifs(amount, west, excel_compatible=False))   # 0 nan

Think before choosing the compatible mode. A maximum of 0 for a region with no sales looks like a real value and will be charted as one; NaN, written to Excel as an empty cell, is the honest answer. Many teams move to blanks when they leave the workbook behind, and note the change in the report.

Step 3 — Criteria with operators and wildcards

Excel criteria are strings such as ">=100", "<>North" or "a*". Translate each into the equivalent comparison. Wildcards in Excel are case-insensitive, so casefold the text before testing it:

big = amount >= 100                                    # ">=100"
not_north = df["Region"] != "North"                    # "<>North"
rep_a = df["Rep"].str.casefold().str.startswith("a")   # "a*"
print(amount[big].mean())                              # 210.0
print(amount[rep_a].mean())                            # 100.0

For patterns with wildcards in the middle, such as "A?n", use fnmatch.fnmatchcase on casefolded values, as shown in XLOOKUP Equivalent in pandas. Note that "<>North" in Excel also matches blank region cells; in pandas != is true for NaN as well, so the behaviour agrees.

Step 4 — A table of results with groupby

Workbooks often hold a summary block where each row repeats the formula with a different criterion — one AVERAGEIFS per region. That whole block is one groupby:

table = amount.groupby(df["Region"]).agg(["mean", "max", "min", "count"])
print(table)
#          mean    max    min  count
# Region
# East     50.0   50.0   50.0      1
# North   100.0  120.0   80.0      2
# South   300.0  300.0  300.0      1

Adding count shows how many numeric values each result is based on. An average of one value is still an average, but readers deserve to know. For several grouping columns — region and rep, say — pass a list: amount.groupby([df["Region"], df["Rep"]]). Groups with no rows at all simply do not appear, rather than showing 0 or an error as a formula would.

Step 5 — Per-row results with transform

Another common layout puts the formula in a column next to the data, filled down, so each row compares itself with its group: =D2/AVERAGEIFS(D:D, A:A, A2). transform returns one value per row, aligned with the original frame:

df["region_avg"] = amount.groupby(df["Region"]).transform("mean")
df["region_max"] = amount.groupby(df["Region"]).transform("max")
df["share_of_best"] = amount / df["region_max"]
print(df[["Region", "Rep", "region_avg", "share_of_best"]].round(2).values.tolist())

Each row receives the average and maximum of its own region, computed once per group rather than once per row as in the workbook. The rows with no numeric amount get NaN for their own share but still receive their region's average, just like the filled-down formula.

Step 6 — Report what Excel skipped

The workbook never told anyone that one North sale was "TBC" and another blank; the average simply left them out. Now that the calculation is in code, make those rows visible next to the result, per group:

text_values = amount.isna() & df["Amount"].notna()
blanks = df["Amount"].isna()
skipped = pd.DataFrame({
    "text": text_values.groupby(df["Region"]).sum(),
    "blank": blanks.groupby(df["Region"]).sum(),
    "used": amount.notna().groupby(df["Region"]).sum(),
})
print(skipped.loc["North"].to_dict())       # {'text': 1, 'blank': 1, 'used': 2}

Half of North's rows were left out of its average. That may be fine — or it may mean the North average is based on too little data to publish. Writing a "based on n sales" column next to each average costs nothing and prevents readers from over-trusting a figure built on one or two values. The same table helps whoever maintains the source sheet: the "text" count lists exactly the cells that need a number typed in.

Step 7 — Check the translation against the workbook

Before retiring the workbook, compare the pandas results with the values Excel calculated. Load the cached formula results with data_only=True and compare them with your numbers, allowing for floating-point noise:

from openpyxl import load_workbook

def cached(path, sheet, cell):
    return load_workbook(path, data_only=True)[sheet][cell].value

# expected = cached("sales_report.xlsx", "Summary", "B2")
expected = 100.0                      # the value Excel showed for North
assert abs(amount[north_q1].mean() - expected) < 1e-9

A handful of such checks, one per translated formula, catches criteria that were copied wrongly and the empty-case differences from Step 2. Reading cached values is covered in Read Formula Results with openpyxl data_only.

Common pitfalls

• Criteria on text numbers. A criterion of ">=100" on a column of text numbers matches nothing in either tool; convert the column first.
• Dates as text. Comparing a text date column with "2026-01-01" compares strings. Make sure the column is a real datetime.
• Whole-column references. D:D in Excel includes the header, which is text and therefore ignored. In pandas, read the sheet with its header row so the label never enters the column in the first place.
• Hidden rows. Excel's AVERAGEIFS includes rows hidden by a filter; so does pandas. If the report relied on SUBTOTAL or AGGREGATE to exclude hidden rows, filter those rows out explicitly.
• Averaging averages. A regional average computed from rep averages ignores how many sales each rep made. Aggregate the raw rows.

Conclusion

AVERAGEIFS, MAXIFS and MINIFS become a boolean mask and a mean, max or min call. Convert values with pd.to_numeric(errors="coerce") to reproduce Excel's skipping of blanks and text, combine criteria with parenthesised &, casefold for wildcards, and use groupby().agg() for summary blocks and groupby().transform() for filled-down columns. Decide deliberately whether to reproduce Excel's 0 when nothing matches, and check a few results against the workbook's cached values.

Frequently asked questions

What does MAXIFS return when no rows match? Excel returns 0, while pandas returns NaN. If a translated report must show exactly what the workbook showed, fill the NaN with 0 — but consider whether a blank is more honest, since 0 looks like a real maximum.

Does AVERAGEIFS count blank or text cells? No. AVERAGEIFS ignores empty cells and text in the average range. In pandas, convert the column with pd.to_numeric(errors="coerce"); mean then skips the resulting NaN values in the same way.

How do I compute AVERAGEIFS for every row, like a formula filled down? Use groupby on the criteria columns with transform("mean"). Every row receives the average of its own group, which matches a formula filled down a column with criteria that reference the same row.

How do I use a wildcard criterion such as "a"?* Use a case-insensitive string test on the criteria column, such as str.startswith after casefold, or fnmatch for patterns with ? and * in the middle.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated to pandas.
• SUMIF and SUMIFS Equivalent in pandas — conditional sums.
• Rank and Percentile Formulas in pandas — ranking within groups.
• Use Different Aggregations per Column in a pandas Pivot — several aggregates in one table.