The Excel COUNTIFS function counts cells that meet several conditions at once, across one or more ranges — the multi-criteria extension of COUNTIF.
Syntax
| Argument | Description | |
|---|---|---|
criteria_range1 | Required | The first range to test. |
criteria1 | Required | The condition applied to the first range. |
criteria_range2, criteria2, ... | Optional | Additional range/criteria pairs (up to 127). All conditions must be true for a row to count. |
How to use it
COUNTIFS counts rows where every condition is satisfied (logical AND). Each criteria range must be the same size and shape, since the function checks them row by row.
The same operators and wildcards as COUNTIF apply, and you can concatenate cell references with &. To count a numeric range like 100–200, pass two conditions on the same range: ">=100" and "<=200".
Ranges must match in size. If the criteria ranges have different numbers of rows, COUNTIFS returns an error. Keep all ranges aligned to the same rows.
Try it: interactive demo
Pick a COUNTIFS example to see the formula and its result.
Practice workbook
Frequently asked questions
Do COUNTIFS conditions use AND or OR logic?
How do I count a numeric range, like between 100 and 200?
=COUNTIFS(B2:B20, ">=100", B2:B20, "<=200"). Both bounds must hold for a value to count.Why does COUNTIFS return an error about ranges?
#VALUE! error, so align every range to the same rows.Can I use wildcards and cell references in COUNTIFS?
* and ? wildcards work, and you can concatenate cell references with &, exactly as in COUNTIF.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class