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

COUNTIF and COUNTIFS Equivalent in pandas

Count matching rows by summing a boolean mask, count blanks and distinct values in one call, and replace a copied-down COUNTIF with groupby().transform('size').

COUNTIF answers "how many rows match", which in pandas is the same boolean mask as a conditional sum with a different final step. What makes counting worth its own guide is the family of related questions around it — how many distinct values, how many blanks, what proportion — several of which need an awkward array formula in Excel and one method call here. This guide is part of Excel Formula Equivalents in pandas.

The counting questions, and where each lives Excel spreads counting across COUNT, COUNTA, COUNTBLANK, COUNTIF and an array formula for distinct values, while pandas answers each with a single method on the column. count rows matching mask.sum() mask.mean() for a share one expression count values present col.count() col.isna().sum() empty strings are not NaN count distinct col.nunique() col.value_counts() an array formula in Excel three questions, three methods, no array formulas

Prerequisites

Bash
pip install pandas openpyxl
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)

COUNTIF and COUNTIFS

Python
# =COUNTIF(A:A, "North")
print((sales["Region"] == "North").sum())

# =COUNTIFS(A:A, "North", C:C, "Widget")
print(((sales["Region"] == "North") & (sales["Product"] == "Widget")).sum())

# =COUNTIF(D:D, ">10000")
print((sales["Revenue"] > 10000).sum())

The trick is that a boolean Series sums as ones and zeros, so .sum() on a mask is a count. That also means .mean() on the same mask gives the proportion matching, which Excel expresses as a COUNTIF divided by a COUNTA:

Python
mask = sales["Revenue"] > 10000
print(f"{mask.sum()} orders, {mask.mean():.1%} of the total")

Getting a count and a share from the same object, without writing the condition twice, is the sort of small ergonomic gain that adds up across a report.

Counting values, blanks and distinct entries

Excel splits these across COUNT, COUNTA, COUNTBLANK and a SUMPRODUCT array formula. pandas has one method for each and they read the way they sound.

Python
print(sales["Revenue"].count())        # =COUNT  — non-null numeric values
print(sales["Rep"].notna().sum())      # =COUNTA — non-blank entries
print(sales["Rep"].isna().sum())       # =COUNTBLANK
print(sales["Region"].nunique())       # distinct regions
print(sales["Region"].value_counts())  # the breakdown, sorted

value_counts() is the one worth reaching for first when investigating a new file. It answers "what is actually in this column" in a single line, and it is how spelling variants — North, north, North — get discovered before they distort a total.

Python
print(sales["Region"].str.strip().str.casefold().value_counts())

If the two counts differ, the column needs the cleanup described in Strip Whitespace and Normalise Text Columns with Pandas before any conditional count means anything.

Counts per group

Counting within groups Grouping by a key and applying size distributes each group's row count back to every member row, which is what a COUNTIF referencing the current row's key does one row at a time. per-group counts groupby('Region') one group per key transform('size') count per group aligned to rows every row gets its group's count size counts rows; count counts non-null values in a column

A COUNTIF copied down a column — "how many other rows share this row's region" — is the same transform pattern as the conditional sum, with size instead of sum.

Python
sales["Orders in region"] = sales.groupby("Region")["Revenue"].transform("size")

counts = sales.groupby(["Region", "Product"]).size().reset_index(name="Orders")
print(counts)

size counts every row in the group including nulls; count counts non-null values in the column you apply it to. The difference is invisible on clean data and important on real exports, where a group of ten rows may have only seven populated revenue values.

Python
per_region = sales.groupby("Region").agg(
    Rows=("Revenue", "size"),
    WithRevenue=("Revenue", "count"),
    Missing=("Revenue", lambda s: s.isna().sum()),
)
print(per_region)

That three-column view is the fastest way to find a group whose totals are quietly built on partial data — the problem tackled directly in Find and Report Missing Values in an Excel File.

Counting distinct values within groups

This is the query that needs a genuinely unpleasant array formula in Excel and one call here.

Python
print(sales.groupby("Region")["Rep"].nunique())

detail = sales.groupby("Region").agg(
    Reps=("Rep", "nunique"),
    Products=("Product", "nunique"),
    Orders=("Revenue", "size"),
)
print(detail)

"How many distinct reps sold in each region" is a question every sales report eventually asks, and the difficulty of expressing it in a spreadsheet is a good part of why so many reports do not.

Conditional counts with wildcards and ranges

Python
# =COUNTIF(C:C, "Widg*")
print(sales["Product"].str.startswith("Widg", na=False).sum())

# =COUNTIFS(D:D, ">=5000", D:D, "<12000")
band = sales["Revenue"].between(5000, 12000, inclusive="left")
print(band.sum())

# Several acceptable values — Excel needs one COUNTIF per value
print(sales["Region"].isin(["North", "West"]).sum())

between with an explicit inclusive argument is worth preferring over two comparisons: it states the boundary behaviour in the code rather than leaving it to be inferred, which is the detail that makes two people's counts disagree.

Building a data-quality summary from counts

Counting stops being a formula translation and starts being useful the moment several counts are put side by side. A short profile of every column — how many values, how many missing, how many distinct — is the first thing worth running against any workbook that arrives from somewhere else.

Python
import pandas as pd

def profile(frame: pd.DataFrame) -> pd.DataFrame:
    return pd.DataFrame({
        "dtype": frame.dtypes.astype(str),
        "present": frame.notna().sum(),
        "missing": frame.isna().sum(),
        "distinct": frame.nunique(dropna=True),
        "example": [frame[c].dropna().iloc[0] if frame[c].notna().any() else None
                    for c in frame.columns],
    })

print(profile(sales))

Two patterns in that table are worth reacting to immediately. A column whose distinct count equals its row count is an identifier, and should not be aggregated. A column whose distinct count is two or three in a hundred thousand rows is a category, and converting it with astype("category") will make every later comparison faster and every group-by smaller.

The profile is also the fastest way to spot the column that has quietly become text. A dtype of object on something that should be numeric explains a total of zero long before anybody starts doubting the logic, which is the diagnosis path set out in Check Excel Data Types with Pandas.

Counting into a report someone reads

A count is rarely the deliverable on its own; it becomes one when it is framed against an expectation. Writing the count beside the number that was expected turns a figure into a check, and it costs one extra column.

Python
expected = {"North": 3, "South": 2, "West": 2}
actual = sales["Region"].value_counts().to_dict()

check = pd.DataFrame({
    "Region": sorted(set(expected) | set(actual)),
})
check["Expected"] = check["Region"].map(expected).fillna(0).astype(int)
check["Actual"] = check["Region"].map(actual).fillna(0).astype(int)
check["Difference"] = check["Actual"] - check["Expected"]
print(check)

Using set(expected) | set(actual) rather than either alone is the detail that makes this work: a region that appears in the data but not the expectation, or vice versa, shows up as a row with a difference rather than being silently dropped. That is the same reasoning behind the reconciliation approach in Find Rows in One Excel File Missing From Another.

Common pitfalls

SymptomCauseFix
Count is higher than expectedEmpty strings counted as presentReplace "" with NaN before counting
count() and size disagreecount excludes nulls, size does notChoose deliberately; report both when investigating
Distinct count differs from ExcelCase or whitespace variantsNormalise with .str.strip().str.casefold() first
Boundary values counted twiceTwo inclusive range conditionsbetween(..., inclusive="left")
value_counts() hides missing valuesIt excludes NaN by defaultPass dropna=False
A group is missing from the resultGrouping key is NaN for those rowsgroupby(..., dropna=False)

Counting across two files

Reconciliation questions are counting questions in disguise: how many invoices are in the ledger but not the statement, how many appear twice, how many changed. All three come out of the same pair of counts once both files are in frames.

Python
ledger = pd.read_excel("ledger.xlsx", dtype={"Invoice": "string"})
statement = pd.read_excel("statement.xlsx", dtype={"Invoice": "string"})

in_ledger = set(ledger["Invoice"])
in_statement = set(statement["Invoice"])

print(f"only in ledger    : {len(in_ledger - in_statement)}")
print(f"only in statement : {len(in_statement - in_ledger)}")
print(f"in both           : {len(in_ledger & in_statement)}")
print(f"duplicated in ledger: {ledger['Invoice'].duplicated().sum()}")

Reading the key column as string rather than letting pandas infer it is what makes this reliable. An invoice number that looks numeric becomes an integer in one file and a float in the other the moment a single blank appears, and then the two sets share nothing at all — a mismatch that looks catastrophic and is entirely an artefact of the read.

The counts alone are usually enough to decide whether a difference is worth investigating. When it is, the row-level comparison is the natural next step, and it is covered in Compare Two Excel Files for Differences with Python.

Performance and scale

Counting is the cheapest thing pandas does — a boolean mask is a compact array of bytes and summing it is a single pass. The cost, when there is one, comes from building the mask on a text column with a regular expression, which is materially slower than an equality comparison.

Cost of building the mask, by comparison type An equality comparison on a category column is the cheapest, a literal substring search costs more, and a regular expression evaluated per row is the most expensive by a wide margin. regex contains per-row engine literal contains regex=False equality on strings direct compare equality on category integer codes relative cost the count is free; building the mask is not
Python
# Fast: exact comparison on a categorical or string column
exact = (sales["Product"] == "Widget").sum()

# Slower: a regex evaluated per row
pattern = sales["Product"].str.contains(r"^Widg\w+$", na=False, regex=True).sum()

# Fast again: fixed substring, no regex engine
literal = sales["Product"].str.contains("Widg", na=False, regex=False).sum()

Passing regex=False when the pattern is a literal substring is a free improvement that people rarely make. On a column with hundreds of thousands of rows, converting a repeated-value text column to category dtype first is another — comparisons then operate on integer codes rather than strings.

Conclusion

COUNTIF is a boolean mask summed, COUNTIFS is the same with conditions joined by &, and a copied-down COUNTIF is groupby().transform("size"). Beyond the direct translations, pandas answers questions Excel makes hard: distinct counts per group in one call, a full value breakdown with value_counts(), and the proportion matching from the same mask that produced the count.

Frequently asked questions

Why does mask.sum() count rows? Because a boolean Series sums as ones and zeros: True is 1 and False is 0, so the sum is the number of True values. mask.mean() gives the proportion instead, which is the share of rows matching.

What is the equivalent of COUNTA and COUNTBLANK? COUNTA is dfcol.notna().sum() (or .count(), which excludes NaN by default), and COUNTBLANK is dfcol.isna().sum(). Watch for empty strings read from a sheet, which are not NaN and are counted as present.

How do I count distinct values? dfcol.nunique() for the count, and dfcol.value_counts() for the breakdown. Excel needs an array formula with SUMPRODUCT and COUNTIF to do the same thing, which is one of the clearest wins in this translation.

Does COUNTIFS with an OR condition translate? Excel needs several COUNTIFS added together for an OR. pandas uses the | operator, or .isin(...) when the condition is membership in a list — one expression either way.