What it does
COUNTIFS takes pairs of range and condition: =COUNTIFS(B2:B50,"West",C2:C50,"Delivered"). A row is counted only if it meets every condition. All ranges must have the same number of rows.
A band is built by giving the same column twice: =COUNTIFS(B2:B50,">=70",B2:B50,"<85") counts scores from 70 up to 85. With a date column, ">="&DATE(2025,10,1) and "<"&DATE(2025,11,1) return one month's records.
Conditions are always joined with AND. For OR, add separate COUNTIFS together or use SUMPRODUCT. With the same conditions, SUMIFS adds and AVERAGEIFS averages.
Syntax
=COUNTIFS(criteria_range1, criteria1, ...)=ÇOKEĞERSAY(ölçüt_aralığı1; ölçüt1; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
criteria_range1 | Required | The range tested by the first condition, e.g. the region column B2:B50. |
criteria1 | Required | The first condition: "West", ">=70", "KBL*" or a cell. Further conditions follow as range and criteria pairs, up to 127 pairs. |
Examples
1Orders by region and status
Regions are in column E and statuses in F1 and G1. The region is given as $E2 and the status as F$1, so each cell of the table uses its own row and column.
=COUNTIFS($B$2:$B$8,$E2,$C$2:$C$8,F$1)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Status | Region | Delivered | Pending | |
| 2 | S-1001 | West | Delivered | West | 3 | 1 | |
| 3 | S-1002 | North | Pending | North | 1 | 2 | |
| 4 | S-1003 | West | Pending | ||||
| 5 | S-1004 | West | Delivered | ||||
| 6 | S-1005 | North | Delivered | ||||
| 7 | S-1006 | West | Delivered | ||||
| 8 | S-1007 | North | Pending |
- F2 (filled down to F3)
=COUNTIFS($B$2:$B$8,$E2,$C$2:$C$8,F$1) - G2 (filled down to G3)
=COUNTIFS($B$2:$B$8,$E2,$C$2:$C$8,G$1)
In the West, 3 orders were delivered and 1 are pending; in the North, 1 were delivered and 2 are pending.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Students per score band
The middle band is built by giving the same column twice. The top and bottom bands need just one condition; a single-condition COUNTIFS works like COUNTIF.
=COUNTIFS(B2:B9,">=85")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | 85 and above | 70-84 | Below 70 | |
| 2 | Mia | 92 | 3 | 3 | 2 | |
| 3 | Lucas | 85 | ||||
| 4 | Ella | 70 | ||||
| 5 | Jack | 64 | ||||
| 6 | Grace | 78 | ||||
| 7 | Henry | 88 | ||||
| 8 | Leo | 55 | ||||
| 9 | Chloe | 84 |
- D2
=COUNTIFS(B2:B9,">=85") - E2
=COUNTIFS(B2:B9,">=70",B2:B9,"<85") - F2
=COUNTIFS(B2:B9,"<70")
3 students scored 85 or more, 3 between 70 and 84 and 2 below 70. Lucas, with exactly 85, is kept out of the middle band by the "<85" condition.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Count billing tickets opened in October
Support tickets are logged with a date and a category. F2 gives the date column twice and the category once as conditions.
=COUNTIFS(A2:A7,">="&DATE(2025,10,1),A2:A7,"<"&DATE(2025,11,1),B2:B7,E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Opened | Category | Ticket | Category | In October | |
| 2 | 30-Sep-2025 | Billing | T-201 | Billing | 3 | |
| 3 | 2-Oct-2025 | Billing | T-202 | |||
| 4 | 9-Oct-2025 | Delivery | T-203 | |||
| 5 | 15-Oct-2025 | Billing | T-204 | |||
| 6 | 28-Oct-2025 | Billing | T-205 | |||
| 7 | 1-Nov-2025 | Billing | T-206 |
3 billing tickets were opened in October; the tickets dated 30 September and 1 November fall outside the range.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The criteria ranges are not the same size (
B2:B50againstC2:C49).Fix: Start and end every range on the same rows.
- Result is 0
Cause: No row meets all the conditions together, or two conditions rule each other out (
">=85"with"<70").Fix: Try the conditions one at a time with COUNTIF to see which one brings the count to zero.
Good to know
- In a region × status count table, give the row condition as
$E2and the column condition asF$1; the formula can then be copied across the whole table. - Spell out the bounds of number bands:
">=70"includes 70,">70"does not.
In Nexell
Get Nexell to do it from a sentence
Make a summary table with the number of delivered and pending orders per region
Convert to tableBring your order list into the tool and type this. Nexell builds the count table with conditional count formulas and shows it in the preview first; in the downloaded file, the counts change when an order's status changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I count between two dates with COUNTIFS?
Give the date column twice: =COUNTIFS(A2:A50,">="&DATE(2025,10,1),A2:A50,"<"&DATE(2025,11,1)). Using "before the first day of the next month" as the end works even when dates include a time.
How do I use OR logic with COUNTIFS?
Add two counts: =COUNTIFS(B2:B50,"West",C2:C50,"Open")+COUNTIFS(B2:B50,"South",C2:C50,"Open"). A row cannot be in both regions, so nothing is counted twice.
Related functions
- COUNTIFEĞERSAY · Counts cells that meet a condition.
- SUMIFSÇOKETOPLA · Adds the cells that meet multiple conditions.
- AVERAGEIFSÇOKEĞERORTALAMA · Average of cells meeting multiple conditions.
- SUMPRODUCTTOPLA.ÇARPIM · Multiplies matching items of arrays and sums them; the classic conditional sum: SUMPRODUCT((City="Ankara")*Amount).
- MAXIFSÇOKEĞERMAK · Largest value among rows meeting conditions.