Skip to content

Excel function · Statistics

Excel COUNTIFS function

COUNTIFS returns how many rows meet all of your conditions together: orders from the West that were delivered, students scoring between 70 and 85, support tickets opened in October.

In Turkish Excel: ÇOKEĞERSAY

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

English Excel=COUNTIFS(criteria_range1, criteria1, ...)
Turkish Excel=Ç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

ArgumentRequiredDescription
criteria_range1RequiredThe range tested by the first condition, e.g. the region column B2:B50.
criteria1RequiredThe 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.

F2fx=COUNTIFS($B$2:$B$8,$E2,$C$2:$C$8,F$1)
Orders by region and status: example table
ABCDEFG
1OrderRegionStatusRegionDeliveredPending
2S-1001WestDeliveredWest31
3S-1002NorthPendingNorth12
4S-1003WestPending
5S-1004WestDelivered
6S-1005NorthDelivered
7S-1006WestDelivered
8S-1007NorthPending
  • 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.

D2fx=COUNTIFS(B2:B9,">=85")
Students per score band: example table
ABCDEF
1StudentScore85 and above70-84Below 70
2Mia92332
3Lucas85
4Ella70
5Jack64
6Grace78
7Henry88
8Leo55
9Chloe84
  • 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.

F2fx=COUNTIFS(A2:A7,">="&DATE(2025,10,1),A2:A7,"<"&DATE(2025,11,1),B2:B7,E2)
Count billing tickets opened in October: example table
ABCDEF
1OpenedCategoryTicketCategoryIn October
230-Sep-2025BillingT-201Billing3
32-Oct-2025BillingT-202
49-Oct-2025DeliveryT-203
515-Oct-2025BillingT-204
628-Oct-2025BillingT-205
71-Nov-2025BillingT-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:B50 against C2: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 $E2 and the column condition as F$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

Type this in Nexell

Make a summary table with the number of delivered and pending orders per region

Convert to table

Bring 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

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free