COUNTIFS function in Excel

COUNTIFS counts the rows that meet several conditions at once.

COUNTIFS counts rows that satisfy every one of a set of conditions. It is how a list becomes a table of figures: how many students of each class fall in each grade band, how many orders of each status came from each city.

Syntax

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

Argument	What it is
`criteria_range1`	The first range to test.
`criteria1`	The condition for the first range.
`criteria_range2, criteria2, ...`	More pairs. A row is counted only if it passes every condition.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=COUNTIFS(B2:B9,"8A",C2:C9,">=60")
Result: 3
Students in 8A with 60 or more in Maths.
=COUNTIFS(C2:C9,">=33",D2:D9,">=33",E2:E9,">=33")
Result: 7
Students who passed all three subjects. Tara's empty Science cell does not pass.
=COUNTIFS(C2:C9,">=40",C2:C9,"<60")
Result: 2
Two conditions on one column: Maths marks from 40 up to 59.
=COUNTIFS(B2:B9,"8B",D2:D9,"<50")
Result: 1
8B students below 50 in Science.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=COUNTIFS(C2:C13,"North",D2:D13,"Bag")
Result: 3
Sales of bags in the North.
=COUNTIFS(A2:A13,">="&DATE(2026,3,1),A2:A13,"<"&DATE(2026,4,1))
Result: 3
Sales made during March 2026.

How it works

Each pair names a range and a condition. COUNTIFS looks along each row and counts it only when all the conditions are true for that row. The conditions are joined by "and".

All the ranges must have the same number of rows and columns. They do not have to sit side by side, but they must be the same shape, or the result is #VALUE!.

The conditions are written as in COUNTIF: text and comparisons in quotation marks, cell references joined with &, wildcards allowed.

Bands

Two conditions on the same column give a range of values. The dependable pattern includes the lower end and excludes the upper, so that no value is counted in two bands or in none:

Band	Formula
below 33	`=COUNTIFS(C2:C9,"<33")`
33 to 59	`=COUNTIFS(C2:C9,">=33",C2:C9,"<60")`
60 to 74	`=COUNTIFS(C2:C9,">=60",C2:C9,"<75")`
75 and above	`=COUNTIFS(C2:C9,">=75")`

Check your work: the bands should add up to the total count.

A cross table

With classes down the side and bands or categories across the top, one formula, copied across and down, builds a two-way summary:

=COUNTIFS($B$2:$B$9,$H2,$C$2:$C$9,">="&I$1)

This is a small version of what a PivotTable does. For large lists and many breakdowns, a PivotTable is quicker to build. For a fixed report layout, COUNTIFS gives you full control.

Dates

Count the rows in a period with a start and an end, as in the last example: on or after the first day, and before the first day of the next period. Writing the end as "before the next start" keeps the formula right for months of any length.

"Or" conditions

COUNTIFS has no "or". For "North or East", add two formulas, or use a list in curly brackets inside SUM:

=SUM(COUNTIFS(C2:C13,{"North","East"}))

Be careful adding when the conditions are on different columns: a row that meets both would be counted twice. Subtract the overlap, or use SUMPRODUCT.

Counting unique combinations

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2) in a helper column shows how many times each pair of values occurs. Above 1 means the combination is repeated, such as the same invoice number entered twice for the same supplier.

Common mistakes

Ranges of different sizes.
Expecting "or" between conditions.
A band written with "<=59" on one side and ">=60" on the other, which loses 59.5.
Empty cells. A condition such as ">=33" is false for an empty cell, so an absent student is counted as not passing. Decide whether that is what you want.

In Google Sheets

COUNTIFS is the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.