COUNTIFS

Count cells that meet two or more conditions at once.

Statistical
|
Excel 2007+
|
Google Sheets Supported

Spreadsheet editor

Syntax

=COUNTIFS(range1, criteria1, [range2, criteria2, ...]) Returns: Number

Arguments

Argument Required Description
range1 Yes The first range of cells to test against criteria1.
criteria1 Yes The condition a cell in range1 must meet, such as 25, ">100", "West", or "A*".
range2, criteria2 No Additional range and criterion pairs. Each range must be the same size as range1, and you can add up to 127 pairs in total.

About

COUNTIFS counts how many rows meet every condition you set. You pair each range with a criterion, and a row is counted only when all of its cells pass their matching test. Use it whenever a single rule is not enough, like counting orders that are both shipped and over $500, or employees in a given department who are also full time.

Criteria can be plain values ("West"), comparisons (">1000", "<>0"), or text with wildcards ("A*" for anything starting with A). The ranges work as an AND filter, so adding more pairs narrows the count rather than widening it. Every range you list must cover the same number of rows and columns, since COUNTIFS lines them up cell by cell.

For a single condition, COUNTIF is simpler. To total or average the matching rows instead of counting them, use SUMIFS and AVERAGEIFS.

Examples

Count between two numbers

Point two criteria at the same Score column to count everything between two bounds. See how this counts the scores from 70 to 89, then edit a score and watch the count update.

Spreadsheet editor

AND logic, not OR

Watch how COUNTIFS only counts rows that match every rule, so High and Open is far stricter than High or Open. Change a priority or status to see why you add separate counts when you really need OR.

Spreadsheet editor

Threshold from a cell

Build a criterion from a cell by joining the operator and the reference with &, like ">"&F2. Change the minimum amount and watch the count of shipped orders above it update instantly.

Spreadsheet editor

Watch out for

⚠

Ranges that are different sizes

If your criteria ranges do not cover the same number of rows and columns, COUNTIFS returns a #VALUE! error because it cannot line the cells up.

→ Make every range the same shape, for example A2:A100 paired with B2:B100, not B2:B120.

⚠

Operators not wrapped in quotes

Writing the criterion as >500 without quotes causes an error. Comparison operators have to be inside text, like ">500".

→ Put the whole condition in quotes: ">500" or "<>Closed". To compare against a cell value, join the operator and the reference with &, like ">"&G1.

⚠

Expecting OR logic

COUNTIFS combines criteria with AND, so adding more pairs only narrows the count. It will never count a row that matches one rule but not another.

→ To count rows matching any of several rules, add separate COUNTIF or COUNTIFS results together, one per condition.

⚠

Trailing spaces and hidden text

A criterion like "Spreadsheet Center" will not match a cell holding "Spreadsheet Center " with a stray space, so your count comes up short.

→ Clean the source data with TRIM, or use a wildcard such as "Spreadsheet Center*" to allow for extra characters.

Tips & notes

Criteria are not case sensitive, so "sales" and "Sales" match the same cells. You can use the wildcards ? (one character) and * (any run of characters) in text criteria; to match a literal question mark or asterisk, put a tilde in front of it, like "~?".

Common questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF tests a single range against one condition. COUNTIFS handles two or more range and criterion pairs at once, counting only the rows that satisfy every condition.

How do I count between two numbers or dates with COUNTIFS?

Point two criteria at the same range, one for each bound. For example =COUNTIFS(A2:A100, ">=100", A2:A100, "<=200") counts values from 100 to 200.

How many criteria can COUNTIFS take?

Up to 127 range and criteria pairs. Note that each extra pair narrows the count further, since all conditions are combined with AND logic.

Can COUNTIFS use OR logic instead of AND?

Not directly. COUNTIFS always requires every condition to be true. To count rows matching any of several rules, add separate COUNTIFS or COUNTIF results together.