How to Use COUNTIFS in Excel with Multiple Criteria

Use COUNTIFS when you need Excel to count rows that meet every condition. Add one range-and-criterion pair for each condition you want to check.

=COUNTIFS($B$5:$B$12,"North",$C$5:$C$12,"Completed")

This formula counts orders where Team is North and Status is Completed. In the example below, the result is 5.

When COUNTIFS is the right formula

Start with the result you need. These functions can use similar conditions, but they return different things:

For this tutorial, the question is: How many North team orders have been completed? Excel checks the Team and Status values on the same row, then adds 1 to the count only when both match.

Build a two-criteria COUNTIFS formula

Use this small order table. The Sales column gives the worksheet some context, but COUNTIFS counts the records rather than adding Sales values.

=COUNTIFS($B$5:$B$12,"North",$C$5:$C$12,"Completed")

1. $B$5:$B$12,"North" is the first pair. Excel checks the Team column for North.
2. $C$5:$C$12,"Completed" is the second pair. Excel checks the Status column for Completed.
3. Only rows that pass both checks are counted. There are 5 in this table.

The dollar signs keep the ranges fixed if you copy the formula. If that part is new to you, see Excel Cell References: Relative, Absolute, and Mixed.

Use cells for a team, status, and date range

Typing text directly into a formula is useful for learning, but report controls are easier to reuse. Put the selected Team in G3, Status in G4, Start date in G5, and End date in G6.

=COUNTIFS($B$5:$B$12,$G$3,$C$5:$C$12,$G$4,$A$5:$A$12,">="&$G$5,$A$5:$A$12,"<"&$G$6+1)

With North, Completed, September 1, and September 30 in those cells, the formula returns 4. The formula includes the order recorded at 4:45 PM on September 30.

The first date rule means “on or after the Start date.” The final rule means “before the day after the End date.” Using "<"&$G$6+1 is useful when order dates can include a time, because it includes every time on the selected end date.

Why COUNTIFS returns this count

COUNTIFS evaluates every criteria pair on the same row. In the date-range formula, a record must be North, Completed, on or after September 1, and before October 1. If any one condition fails, that row does not add 1 to the result.

That is why the pending order does not count, and why the October 1 completed North order does not count. The formula is not adding the Sales values; it is counting qualifying records.

When COUNTIFS returns 0 or an error

Check whether any row meets every condition

A result of 0 can be correct when no row matches all the rules together. Check the spelling of text criteria, the selected cells, dates, and comparison operators. For a text value typed directly into a formula, use quotation marks. For a value stored in a cell, use the cell reference without quotation marks.

Keep every criteria range the same size

Every criteria range must have the same number of rows and columns. For example, do not use $B$5:$B$12 with $C$5:$C$11. Correct the ranges first if Excel returns #VALUE!. For help identifying error codes, read Excel Formula Errors Explained.

Make sure dates are real Excel dates

If a date looks correct but is stored as text, a date comparison may not behave as expected. Enter or convert the value to a real Excel date before changing the formula.

Source and further reading

Microsoft documents the current COUNTIFS syntax, supported versions, criteria pairs, and matching-range-size requirement.