Excel COUNTIFS Function — Count with Multiple Conditions (with Examples)

Contents

What does COUNTIFS do?
Syntax
5 real examples
OR logic with COUNTIFS
COUNTIFS vs COUNTIF
FAQ

What does COUNTIFS do?

COUNTIFS counts the number of rows where every condition you specify is true at the same time. One condition: how many sales were above 1,000? Two conditions: how many sales were above 1,000 AND in the North region? Three conditions: above 1,000, North region, AND in Q1 2026? COUNTIFS handles all of these — COUNTIF can only handle the first.

It is one of the most frequently used formulas in business reporting, HR analytics, financial dashboards, and data quality checks. If you work with structured data tables, you will use COUNTIFS almost every day.

Syntax

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

Argument	Description
criteria_range1 required	The first column to check a condition against.
criteria1 required	The first condition to match. Text in quotes, numbers and cell references without.
criteria_range2, criteria2 optional	Additional condition pairs. All must be true for a row to be counted. Up to 127 pairs.

5 real examples

Example 1

Count female employees in London

=COUNTIFS(A2:A100, "Female", B2:B100, "London")

Both conditions must be true. A row is only counted if Gender = Female AND Office = London.

Example 2

Count sales above a threshold in one region

=COUNTIFS(A2:A100, "North", C2:C100, ">1000")

Example 3

Count entries in a date range

=COUNTIFS(A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,4,1))

Two conditions on the same date column create a date range bracket — counts entries in Q1 2026.

Example 4

Dynamic criteria from cells

=COUNTIFS(A2:A100, E1, B2:B100, E2)

Change E1 and E2 and the count updates. This is how interactive dashboards work.

Example 5

Count between two numbers

=COUNTIFS(B2:B100, ">=50", B2:B100, "<=100")

Two conditions on the same column count values that fall between 50 and 100 inclusive.

OR logic with COUNTIFS

COUNTIFS uses AND logic — all conditions must be true. For OR logic (condition 1 OR condition 2), add two COUNTIFS results. Be careful about double-counting rows that satisfy both.

Count rows in North OR South: =COUNTIFS(A2:A100,"North") + COUNTIFS(A2:A100,"South") Count rows matching ANY of three statuses: =SUMPRODUCT((COUNTIFS(A2:A100,{"Open","Pending","Escalated"})))

COUNTIFS vs COUNTIF

Formula	Conditions	Logic
COUNTIF	1 only	Single filter
COUNTIFS	1 to 127	All conditions AND

💡 Use COUNTIFS for everything

COUNTIFS works with just one condition too — making it a universal replacement for COUNTIF. Many professionals use only COUNTIFS and never need COUNTIF.

FAQ

What if all conditions match but COUNTIFS returns 0?

Check for extra spaces (use TRIM), different capitalisation (COUNTIFS is case insensitive so this shouldn't matter), or numbers stored as text. Test each condition separately with COUNTIF first to isolate the problem.

Can COUNTIFS use wildcards?

Yes. * matches any characters, ? matches one. =COUNTIFS(A:A,"*manager*") counts any cell containing the word "manager" anywhere in the text.

Is COUNTIFS the same as COUNTIF with multiple criteria?

Yes — COUNTIFS is the multi-criteria extension of COUNTIF. They use the same syntax for their condition arguments, just COUNTIFS accepts more of them.

Practise COUNTIFS with real data

ExcelPro has COUNTIF and COUNTIFS exercises in every track. Free to start.

Try exercises →

Related formulas

COUNTIF SUMIFS AVERAGEIFS MAXIFS SUMPRODUCT IF

The Excel COUNTIFS Functionexplained simply