Excel COUNTIFS Function

COUNTIFS counts the number of cells that meet two or more conditions, checking each condition against its own range. It returns a single number. The conditions are combined with AND logic — every condition has to be true for a row to get counted. If you need OR logic across conditions, COUNTIFS can't do it alone.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and Excel 2013. COUNTIFS has been part of Excel since Excel 2007, so version compatibility is rarely an issue.

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameter	Required	Description
criteria_range1	Yes	The first range to check.
criteria1	Yes	The condition applied to criteria_range1. Can be a number, text, cell reference, or expression like ">1000".
criteria_range2, criteria2	No	Additional range/condition pairs. Add as many as you need — up to 127 pairs total.

Every range you add must have the same number of rows and columns as the first. COUNTIFS doesn't warn you before you build the formula — it throws an error the moment the ranges don't line up.

Ranges pair up positionally, not by matching values. Row 5 of criteria_range1 is checked against row 5 of criteria_range2, row 6 against row 6, and so on down the entire range. If orders[Region] row 5 contains "West" and orders[Status] row 5 contains "Shipped," that row counts. If either condition fails on that row, COUNTIFS skips it — even if "West" and "Shipped" both appear elsewhere in the table, just not on the same row.

Basic Example

You're tracking sales orders and want to know how many orders from the West region are marked as Shipped.

=COUNTIFS(orders[Region], "West", orders[Status], "Shipped")

// orders[Region]  — the column to check for "West"
// "West"          — first condition
// orders[Status]  — the column to check for "Shipped"
// "Shipped"       — second condition

COUNTIFS scans orders[Region] row by row looking for "West," then checks the matching row in orders[Status] for "Shipped." Only rows where both are true get counted. If 340 orders came from the West and 210 of those are Shipped, the formula returns 210 — not 340, and not the total row count.

How COUNTIFS Works

Every condition uses AND logic

COUNTIFS never uses OR between condition pairs. Add three range/criteria pairs and a row must satisfy all three to count. This trips up people who expect COUNTIFS to behave like a filter with "any of these" logic — it doesn't. For OR logic, you'll need separate COUNTIFS calls added together, or SUMPRODUCT.

Ranges must match in shape

If criteria_range1 is 500 rows tall, every other range must also be 500 rows tall. Mixing B2:B500 with C2:C300 doesn't just give a wrong answer. It throws an error, which is actually useful — it stops a silent miscalculation before it reaches your report.

Criteria accept operators, not just exact values

Wrap comparison operators in quotes: ">1000", "<>0", "<="&TODAY(). This lets you count numeric ranges, exclude values, or build rolling date windows without a helper column.

=COUNTIFS(orders[Amount], ">500", orders[Amount], "<=2000")
// counts orders between 501 and 2000

Text matching ignores case, but wildcards still work

"shipped," "Shipped," and "SHIPPED" all match the same condition. Wildcards behave normally: * matches any number of characters, ? matches exactly one. "*Pro*" matches "Surface Pro," "Pro Max," and "iPad Pro" in the same pass.

Common Use Cases

Count orders in a date range

Combine two conditions on the same date column to build a rolling window.

=COUNTIFS(orders[Date], ">="&DATE(2026,1,1), orders[Date], "<="&DATE(2026,3,31))
// counts orders placed in Q1 2026

Count by product and region together

Track how many units of a specific product sold in a specific region without building a pivot table.

=COUNTIFS(orders[Product], "Wireless Mouse", orders[Region], "Northeast")

Reference criteria from cells instead of hardcoding

Point the criteria arguments at input cells so the formula updates when someone changes a dropdown, instead of editing the formula itself.

=COUNTIFS(orders[Region], G2, orders[Status], G3)

// G2  — region typed or selected by the user
// G3  — status typed or selected by the user

Count with a wildcard on a partial text match

Count every order for products containing "Pro" in the name, regardless of what comes before or after it.

=COUNTIFS(orders[Product], "*Pro*")   // matches "Surface Pro", "iPad Pro 11"

Handling Errors

COUNTIFS most commonly throws #VALUE!. It rarely returns #NAME? unless the function name itself is misspelled.

Common causes of #VALUE!:

• Criteria ranges have a different number of rows or columns than the first range
• One range spans multiple sheets and the other doesn't
• A range reference got broken when rows or columns were deleted

Because #VALUE! from COUNTIFS almost always signals a structural mismatch in your ranges, wrapping the formula in IFERROR to display a generic fallback message hides the actual problem instead of helping you find it. Fix the range sizes directly rather than suppressing the error.

Notes & Gotchas

Why does COUNTIFS return 0 when I know matching rows exist?

The usual cause is a trailing space or a number stored as text in one of the columns. COUNTIFS compares "West " (with a space) and "West" as different values, even though they look identical in the cell. Run =TRIM() on the source column, or use -- to coerce text numbers before counting.

Why does COUNTIFS return #VALUE!?

This happens when the ranges you pass in don't all cover the same number of rows and columns. If criteria_range1 is B2:B500 and criteria_range2 is C2:C450, COUNTIFS can't line them up row by row and throws the error. Fix it by making every range the same size, ideally by using structured table references so they resize together automatically.

Does COUNTIFS work with dates?

Yes, and it's one of the most common uses. Compare a date column against ">="&DATE(2026,1,1) or a cell reference holding a date. Dates stored as text won't work correctly — Excel needs to recognize them as actual date serial numbers for the comparison operators to behave.

Can COUNTIFS use OR logic between conditions?

No. Every condition pair in a single COUNTIFS formula is joined with AND. To count rows matching condition A or condition B, write two separate COUNTIFS formulas and add the results, or use SUMPRODUCT with a + between the condition arrays.

What happens if a criteria range includes blank cells?

Blank cells simply don't match text or numeric criteria, so they're excluded from the count as expected. To count blanks specifically, use "" as the criteria for that range. To count non-blanks, use "<>".

Related Functions

Function	Use this when...
COUNTIF	You only need to check a single condition.
SUMIFS	You need a sum instead of a count, using the same multi-condition logic.