COUNTIFS with Multiple Criteria

All versionsCOUNTIFSAND logic

COUNTIFS counts the rows where every condition is true — “how many West orders of at least $100,” “how many open tickets assigned to Ana.” Same idea as SUMIFS, but it counts rows instead of adding a column.

Quick formula: to count rows where region (B) is West and amount (D) is at least $100:

=COUNTIFS(B2:B8, "West", D2:D8, ">=100")

Each range, criteria pair narrows the count further.

Functions used (tap for the full reference guide):

COUNTIFS COUNTIF

The example

The same sales log. How many West orders are $100 or more?

	A	B	C	D
1	Rep	Region	Product	Amount
2	Ana	West	Widget	$120
3	Ben	East	Widget	$90
4	Cy	West	Gadget	$60
5	Dot	West	Widget	$200
6	Eve	South	Widget	$75
7	Fin	West	Gadget	$40
8	West orders ≥ $100:			2

The formula

The count is:

=COUNTIFS(B2:B7, "West", D2:D7, ">=100") // Ana + Dot = 2

How it works

Each pair is a filter; COUNTIFS counts rows that survive all of them:

Pair 1, B2:B7, "West", keeps West rows (Ana, Cy, Dot, Fin).
Pair 2, D2:D7, ">=100", keeps amounts of 100 or more. The >= goes inside quotes.
Both true only for Ana (120) and Dot (200) → count of 2.

Try it: interactive demo

Live demo

Pick a region and minimum amount; watch the COUNTIFS update.

Region

Minimum amount

Count:

Variations

Count between two values

Two conditions on the same column give a range — amounts from $50 to $150:

=COUNTIFS(D2:D7, ">=50", D2:D7, "<=150")

Count with a wildcard

Count reps whose name starts with “A”:

=COUNTIFS(A2:A7, "A*")

Pitfalls & errors

Operators must be inside quotes. Write ">=100", not >=100. To compare against a cell, concatenate: ">="&G1.

Ranges must match in size. Every criteria range needs the same number of rows, or COUNTIFS returns #VALUE!.

COUNTIFS counts blanks as not-matching. Use "<>" to count non-blank cells, or "" to count blanks.

Practice workbook

📊

Download the free COUNTIFS with Multiple Criteria practice workbook

The sales log with live COUNTIFS, count-between, and wildcard examples, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count with multiple conditions in Excel?

Use COUNTIFS with one range/criteria pair per condition: =COUNTIFS(B2:B8, "West", D2:D8, ">=100"). A row is counted only when all conditions are true.

How do I count values between two numbers?

Apply two conditions to the same column: =COUNTIFS(D2:D7, ">=50", D2:D7, "<=150") counts values from 50 to 150 inclusive.

Why is my COUNTIFS returning 0?

Usually the operator isn't quoted (use ">=100"), the criteria ranges are different sizes, or a number is stored as text. Check types and that every range spans the same rows.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: COUNTIFS · COUNTIF