COUNTIF Multiple Criteria: How to Count with Two Conditions

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

To count rows that meet two or more conditions in Excel, use the COUNTIFS function. It applies each criterion to its own range and counts a row only when every condition is true. This is the standard way to handle COUNTIF multiple criteria, and it replaces the older trick of combining COUNTIF with array formulas.

Quick Answer

COUNTIFS counts rows where all criteria are met across two or more ranges [1].
Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) [1].
You can supply up to 127 range and criteria pairs [1].
Every range must have the same number of rows and columns as the first range [1].
Criteria can be numbers, text, cell references, or expressions like ">500" [1].

Syntax

Argument	Required?	Meaning
criteria_range1	Required	The first range to evaluate against criteria1 [1].
criteria1	Required	The condition for criteria_range1. Can be a number, expression, cell reference, or text such as `32`, `">32"`, `B4`, or `"apples"` [1].
criteria_range2, criteria2, ...	Optional	Additional ranges and their matching criteria. Up to 127 pairs are allowed [1].

The general form is:

$$ \text{COUNTIFS}(R_1, c_1, R_2, c_2, \dots, R_n, c_n) $$

Each range $R_i$ is checked against its criterion $c_i$, and a row is counted only when all $n$ conditions hold.

How It Works

COUNTIFS evaluates one cell at a time across the ranges you give it. It checks the first cell of every range against its paired criterion. If all of those first cells pass, the count goes up by 1. Then it moves to the second cell of every range, and so on until all cells are evaluated [1].

That row-by-row behavior is what makes COUNTIFS different from adding two separate COUNTIF results. Adding COUNTIF(A2:A21,"East") + COUNTIF(B2:B21,">500") counts East rows plus high-amount rows, which double-counts rows that satisfy both and includes rows that satisfy only one. COUNTIFS counts only the rows where both conditions are true at the same time.

The ranges do not have to be adjacent. You can point criteria_range1 at column A and criteria_range2 at column C, as long as both cover the same number of rows and columns [1].

Criteria follow the same rules as COUNTIF. Text and expressions go in quotes, cell references do not. Wildcards work in text criteria: ? matches any single character and * matches any sequence of characters. To match a literal question mark or asterisk, put a tilde ~ before it [1]. If a criterion points at an empty cell, COUNTIFS treats that cell as the value 0 [1].

Worked Example

The table below is a sales log with Region, Amount, and Sales Rep columns for 20 rows. Cell D2 holds a COUNTIFS formula that counts East region sales above 500.

Row	A (Region)	B (Amount)	C (Sales Rep)	D
1	Region	Amount	Sales Rep	Count East >500
2	East	620	Ana	`=COUNTIFS(A2:A21,"East",B2:B21,">500")` -> displays 8
3	West	450	Ben
4	East	780	Cara
5	North	510	Dan
6	East	300	Eve
7	South	900	Finn
8	East	550	Gia
9	West	720	Hana
10	East	610	Ivan
11	North	480	Jo
12	East	530	Kim
13	South	400	Liam
14	East	850	Mia
15	West	390	Nia
16	East	505	Omar
17	North	660	Pia
18	East	720	Quinn
19	South	580	Rae
20	East	490	Sam
21	West	810	Tia

The formula in D2 is:

=COUNTIFS(A2:A21,"East",B2:B21,">500")

It returns 8. Reading the rows one at a time, the East rows with an amount above 500 are rows 2, 4, 8, 10, 12, 14, 16, and 18. Row 6 (East, 300) and row 20 (East, 490) fail the amount test, and every non-East row fails the region test. That gives 8 matching rows.

More Examples

Two text criteria. To count East rows handled by a specific rep, pair the region with the rep column:

=COUNTIFS(A2:A21,"East",C2:C21,"Ana")

This returns 1, because only row 2 is both East and Ana.

A cell reference as a criterion. If you type the region name in F1, you can point at it instead of hardcoding text:

=COUNTIFS(A2:A21,F1,B2:B21,">500")

Change F1 to "West" and the result updates. Cell references are not wrapped in quotes [1].

A numeric threshold from another cell. To make the amount cutoff adjustable, put 500 in F2 and use:

=COUNTIFS(A2:A21,"East",B2:B21,">"&F2)

The & joins the operator to the cell value so the criterion reads as ">500".

Wildcard text matching. To count rows where the rep name starts with a specific letter, use an asterisk:

=COUNTIFS(A2:A21,"East",C2:C21,"A*")

The asterisk matches any sequence of characters after the A [1].

Three conditions. COUNTIFS scales to more pairs. To count East rows above 500 handled by reps whose names start with A, C, or G, you would need separate formulas or a different approach, since COUNTIFS pairs one criterion per range. For related aggregation tasks, see Excel SUMIF and SUMIFS: Syntax and Examples.

Errors and How to Fix Them

#VALUE! error. This usually means the ranges have different sizes. Every range must have the same number of rows and columns as criteria_range1 [1]. Check that A2:A21 and B2:B21 both cover 20 rows.

Wrong count from a text criterion. If you write =COUNTIFS(A2:A21,East,...) without quotes, Excel treats East as a name and returns an error or an unexpected result. Text criteria need quotes, cell references do not [1].

A number stored as text. If amounts are stored as text, ">500" may not match them the way you expect. Convert the column to numbers first.

Empty cell criteria. If a criterion references an empty cell, COUNTIFS treats that cell as 0 [1]. That can silently change your result. Fill the reference cell or use an explicit value.

Wildcard surprises. A criterion like "East" matches any cell containing East, including "Northeast". Use exact text when you want an exact match, and remember the tilde escape for literal ? and * characters [1].

Common Mistakes

Using COUNTIF instead of COUNTIFS for two conditions. COUNTIF takes a single range and criterion. For two or more conditions, switch to COUNTIFS. See COUNTIF Function in Excel: Syntax and Examples for the single-condition version.
Adding two COUNTIF results. COUNTIF(...) + COUNTIF(...) double-counts rows that meet both conditions. COUNTIFS counts each row once.
Mismatched range sizes. If criteria_range1 is A2:A21 and criteria_range2 is B2:B20, you get an error. Keep every range the same height and width [1].
Forgetting quotes around text and operators. "East" and ">500" need quotes. Bare East and >500 do not work as intended [1].
Mixing up the order of range and criterion. Each range is immediately followed by its own criterion. Swapping them produces wrong counts or errors [1].
Ignoring blank cells in the criteria range. Blank cells are treated as 0 when a criterion references an empty cell, which can inflate or deflate your count [1].

Limitations

COUNTIFS counts rows, so it cannot return the sum, average, or any other aggregate of the matching values. If you need totals instead of counts, use SUMIFS. It also cannot count distinct values. If a row appears twice in your data, COUNTIFS counts it twice, and there is no built-in option to deduplicate.

COUNTIFS can apply two conditions to the same column if you repeat the range. For example, counting amounts between 500 and 800 uses =COUNTIFS(B2:B21,">=500",B2:B21,"<=800"). What it cannot do is OR logic across several values in one call, which needs separate COUNTIFS results added together. For multi-condition lookups and matches, see Excel INDEX MATCH with Multiple Criteria: Step by Step. For counting non-blank cells, see COUNTIF Not Blank in Excel: Formula and Examples.

Frequently Asked Questions

Can COUNTIF handle two criteria?

No. COUNTIF accepts only one range and one criterion. To count with two or more conditions, use COUNTIFS, which takes additional range and criterion pairs [1]. The two functions share the same criteria rules, so anything you can write in COUNTIF you can write in COUNTIFS.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF counts cells in one range that meet one condition. COUNTIFS counts rows where every condition across multiple ranges is true [1]. COUNTIFS also supports up to 127 range and criteria pairs, while COUNTIF is limited to a single pair [1].

How do I count with two criteria in different columns?

List each range with its criterion in order: =COUNTIFS(A2:A21,"East",B2:B21,">500"). The first range and criterion handle the region, the second handle the amount. Both ranges must cover the same number of rows [1].

Can I use a cell reference as a criterion in COUNTIFS?

Yes. Point at the cell without quotes, such as =COUNTIFS(A2:A21,F1,B2:B21,">500"). If the cell is empty, COUNTIFS treats it as 0 [1]. For an operator plus a cell value, join them with &, as in ">"&F2.

Why does my COUNTIFS return 0?

Common causes are text criteria with typos or extra spaces, or a criterion that does not match the stored data type. Mismatched range sizes give #VALUE! rather than 0 [1]. Check that text criteria are spelled exactly as stored and wrapped in quotes. If amounts are stored as text, ">500" will not match them. For more on counting text, see COUNTIF Cell Contains Text in Excel: Formula and Examples.

References

COUNTIFS function | Microsoft Support