COUNTIFS in Excel: count rows with multiple conditions

What COUNTIFS does

COUNTIFS counts cells or rows that meet multiple conditions. The syntax is `=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)`.

The key idea is AND logic. A row is counted only if condition one is true and condition two is true and any additional conditions are also true. For example, `=COUNTIFS(B2:B100,"North",C2:C100,"Open")` counts rows where the region is North and the status is Open.

COUNTIFS is common in dashboards, grade sheets, inventory trackers, support reports, and sales summaries because real questions usually involve more than one condition.

Arguments explained

`criteria_range1` is the first range to test. `criteria1` is the rule for that range. Additional ranges and criteria are added in pairs. Every criteria range must be the same size and shape. If B2:B100 has 99 cells, C2:C100 should also have 99 cells.

Text criteria use quotation marks, such as `"East"` or `"Late"`. Numeric comparisons use quoted operators, such as `">=80"`. To compare against a cell, join the operator and cell reference: `">="&F2`.

COUNTIFS differs from COUNTIF by allowing multiple criteria ranges. COUNTIF asks one question. COUNTIFS asks several questions about the same rows.

Think of each range-and-criteria pair as one filter applied to the table. Excel does not count a row after the first matching condition; it waits until every listed condition has been checked.

Practical examples

A beginner school example is counting students who passed a test in one class. If B2:B50 contains Class and C2:C50 contains Score, `=COUNTIFS(B2:B50,"A",C2:C50,">=70")` counts students in class A with scores of at least 70.

A business example is counting overdue invoices for a region. If B2:B200 contains Region, C2:C200 contains Status, and D2:D200 contains Due Date, `=COUNTIFS(B2:B200,"West",C2:C200,"Unpaid",D2:D200,"<"&TODAY())` counts unpaid West invoices due before today.

This kind of formula is useful because it updates automatically when invoice statuses or due dates change.

When to use it

Use COUNTIFS when you need a count based on several conditions: category plus status, region plus date, class plus score, or owner plus priority. It is especially useful for summary cards and operational checks.

Do not use COUNTIFS when you need OR logic unless you deliberately combine multiple COUNTIFS formulas. Do not use it to add matching amounts; use SUMIFS. Also avoid ranges of different sizes because that can break the formula.

Related functions

COUNTIF handles one condition. SUMIFS uses similar criteria logic but adds values instead of counting rows. SUMIF is the one-condition version for totals. Learning COUNTIFS makes SUMIFS easier because the criteria pattern is almost the same.

Practice COUNTIFS on Calcu after COUNTIF, then move into SUMIFS exercises with multiple criteria.