COUNTIFS: counting with multiple criteria

In short: =COUNTIFS(range1,criteria1,range2,criteria2,…). You give it pairs of range and criteria, and a row is counted only when every pair matches in that row.

Try it now

The sheet lists orders with a city and an amount. Count the orders from Hamburg worth 500 or more. Both conditions have to be true in the same row.

Task In E2, count the orders from Hamburg with an amount of at least 500.

	A	B	C	D	E
1	Order no.	City	Amount $		Hamburg, 500 or more
2	B-4417	Hamburg	820
3	B-4402	Berlin	1200
4	B-4425	Hamburg	340
5	B-4409	Cologne	650
6	B-4431	Hamburg	500
7	B-4414	Munich	980
8	B-4420	Hamburg	1450
9	B-4406	Berlin	300

The COUNTIFS syntax

Argument	Meaning	In the example
criteria_range1	The first column to check	B2:B9 (the cities)
criteria1	The condition for that column	"Hamburg"
criteria_range2	A second column (optional)	C2:C9 (the amounts)
criteria2	The condition for the second column	">=500"

Put together: =COUNTIFS(B2:B9,"Hamburg",C2:C9,">=500"), which returns 3. Add more pairs at the end as needed. Microsoft allows up to 127 pairs, though two or three cover most real reports. Criteria work exactly as in COUNTIF: text and comparisons in quotes, and wildcards such as "*Ltd*" are fine.

Unlike SUMIFS, there is no sum range up front. COUNTIFS starts straight away with the first pair.

COUNTIFS between two values

"How many amounts are between 500 and 1,000?" comes up all the time. Use the same column twice, once with the lower limit and once with the upper limit:

=COUNTIFS(C2:C9,">=500",C2:C9,"<=1000")

You decide whether the limits themselves count: >= and <= include them, > and < leave them out. With an order of exactly 500, that choice changes the answer.

Counting dates in a range

Dates work the same way, because Excel stores every date as a number. The tidy approach is to put the start and end date in their own cells and join each operator to its cell with an ampersand:

=COUNTIFS(B2:B9,">="&E2,B2:B9,"<="&F2)

The operator stays inside the quotes, the reference stays outside. To report on another month, you only change E2 and F2. Without helper cells, build the date with DATE: ">="&DATE(2026,3,1). Typing a date as text, as in ">=3/1/2026", often works but depends on the regional settings of whoever opens the file.

Task In G2, count how many jobs came in between the dates in E2 and F2, which is March 2026. The first and the last day count. Use the cells E2 and F2 so that the period can later be changed there alone.

	A	B	C	D	E	F	G
1	Job	Date	Amount $		From	To	Jobs in period
2	A-5012	3/9/2026	1340		3/1/2026	3/31/2026
3	A-5003	2/18/2026	560
4	A-5021	3/31/2026	2100
5	A-5009	3/1/2026	870
6	A-5025	4/6/2026	430
7	A-5016	3/20/2026	1720
8	A-5001	2/6/2026	990
9	A-5018	3/14/2026	650

Criteria from cells

Hard-coded values mean editing the formula for every new question. A small input cell is better. For an equality check the reference alone does the job, as in B2:B9,E2. For greater or less than, join the operator and the cell: C2:C9,">="&F2. That way anyone can change the department or the limit without touching the formula.

Task In G2, count how many employees from the department in E2 have at least as many overtime hours as F2 says. Take both criteria from the cells so the report works for any department and any limit.

	A	B	C	D	E	F	G
1	Name	Department	Overtime hours		Department	Minimum hours	Count
2	Jana Weber	Sales	12		Sales	10
3	Murat Kaya	IT	8
4	Lena Hoffmann	Sales	6
5	Tom Schulz	Purchasing	14
6	Sara Klein	IT	15
7	Ali Demir	Sales	10
8	Mia Wagner	HR	3
9	Paul Becker	Sales	9

OR instead of AND

COUNTIFS always joins its conditions with AND. For OR, say orders from Hamburg or Berlin, count each case and add the results:

=COUNTIFS(B2:B9,"Hamburg",C2:C9,">=500")+COUNTIFS(B2:B9,"Berlin",C2:C9,">=500")

This works as long as the cases cannot overlap. A cell cannot say Hamburg and Berlin at the same time, so no row is counted twice.

Common COUNTIFS mistakes

Ranges of different sizes

Every range must have the same number of rows and columns. =COUNTIFS(B2:B9,"Hamburg",C2:C10,">=500") returns #VALUE!.

The reference inside the quotes

">=E2" compares against the text E2 and counts nothing. Write ">="&E2.

> where you meant >=

With "<"&F2 the last day of the period drops out. For "from ... to" you nearly always want >= and <=.

Times hidden in the dates

If the date column also holds times, March 31 at 2 pm is greater than March 31 itself, so "<="&F2 misses it. Use "<"&F2+1 instead.

An empty criteria cell

If a criteria argument points to an empty cell, COUNTIFS treats it as 0. The result is usually 0, with no error to warn you.

COUNTIFS in German Excel

A German Excel calls the function ZÄHLENWENNS and separates arguments with semicolons. The criteria are written the same way:

English	German
=COUNTIFS(B2:B9,"Hamburg",C2:C9,">=500")	=ZÄHLENWENNS(B2:B9;"Hamburg";C2:C9;">=500")
COUNTIF	ZÄHLENWENN
DATE	DATUM

Our exercises accept both spellings. You can pick your Excel language below each exercise.

Which Excel versions have COUNTIFS?

COUNTIFS is available in all current versions: Microsoft 365 for Windows and Mac, Excel 2024, 2021, 2019 and 2016, and Excel for the web. You can use it in shared workbooks without worrying about who has which version.

FAQ