This page isn't translated yet, so its text is in English. Function names and formulas are shown in your Excel language.
Excel function · Counting
NB.SI.ENS(COUNTIFS)
Counts the rows that meet several conditions.
What it does
COUNTIFS counts the rows where every condition is true. Each condition is a pair: a range and what it must match. All the ranges must be the same size.
Syntax
=NB.SI.ENS(criteria_range1; criteria1; [criteria_range2; criteria2]; …)Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;
Arguments
- criteria_range
- A range to check.
- criteria
- What that range must match: text in quotes, a comparison such as
">10", or a cell.
Example
Each order has a colour and a size. How many are red and large?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Colour | Size | Red and large | |
| 2 | Red | Small | 2 | |
| 3 | Blue | Large | ||
| 4 | Red | Large | ||
| 5 | Red | Large | ||
| 6 | Green | Small |
The formula in D2:
=NB.SI.ENS(A2:A6;"Red";B2:B6;"Large")Result: 2
Rows 4 and 5 are both red and large, so the count is 2. Row 2 is red but small, and row 3 is large but blue.
Common mistakes
- Ranges of different sizes give a #VALUE! error.
- Using it for either/or. All the conditions must be true; for red or blue, add two
COUNTIFresults.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | COUNTIFS | , |
| Deutsch | ZÄHLENWENNS | ; |
| Français | NB.SI.ENS | ; |
| Español (España) | CONTAR.SI.CONJUNTO | ; |
| Español (México) | CONTAR.SI.CONJUNTO | , |
| 日本語 | COUNTIFS | , |
| 한국어 | COUNTIFS | , |
| 繁體中文 | COUNTIFS | , |
| ไทย | COUNTIFS | , |
| ខ្មែរ | COUNTIFS | , |
| Tiếng Việt | COUNTIFS | ; |