Count values in a range that meet multiple specific criteria.
COUNTIFS(range1, criteria1, [range2], [criteria2], ...)
COUNTIFS (with the 's'!) allows you to count values associated with multiple sets of criteria in a range or table. The COUNTIF function by contrast can only search for a singular criterion.
The COUNTIFS syntax requires the range(s) to be evaluated and the criteria to be applied.
COUNTIFS can be used with dates, numbers, and text and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
COUNTIFS with text and numbers.
In the examples below, the data range shows a list of countries, the region they belong to and their respective population. COUNTIFS is used to count the number of countries using multiple criteria such as region name and population size.
Use the COUNTIFS function to count values in a range that meet multiple criteria.
COUNTIFS and dates.
In the examples below, COUNTIFS is used to determine total sales for given dates or date ranges. Dates can be expressed as stated values ("1-Mar-22") or as cell references or using the DATE function (=DATE(2022,3,1)).
Use the COUNTIFS function to count values in a range that meet multiple criteria using date references.
Syntax
The COUNTIFS function consists of at least 2 arguments: =COUNTIFS(range1, criteria1, [range2], [criteria2], ...)
- range1. Required. The first range of cells to be evaluated.
- criteria1. Required. The first criteria to apply.
- range2. Optional. The second (and subsequent) range of cells to be evaluated.
- criteria2. Optional. The second (and subsequent) criteria to apply.
Try it now!
- Enter your formulas into the grey cells.
Scope
COUNTIFS can be applied to ranges, named ranges and tables. It can also be used with logical conditions, wildcards and cell references.
|
=COUNTIFS(C3:C12,"*Asia",B3:B12,"<>China") // region is anything with "Asia" in the name (* is a wildcard) and country must exclude "China" |
|
=COUNTIFS(Region,"*Asia",Country,"<>China") // range1 and range2 are defined by Named Ranges. |
|
=COUNTIFS(Table1[Region],"*Asia",Table1[Country],"<>China") // range1 and range2 are defined by table columns. |
|
=COUNTIFS(B3:B12,">="&C14,B3:B12,"<="&C15) // greater than or equal to the contents of cell C14 and less than or equal to the contents of cell C15 |
|
=COUNTIFS(Region,"*Asia",Country,"?????") // region can be anything with "Asia" in it (e.g. South Asia, East Asia) and country name must contain 5 characters (e.g. China, India, Japan). |
Caveats
COUNTIFS function:
- can handle up to 127 range / criteria pairs.
- ranges need to have the same number of rows and columns but do not need to be adjacent to each other.
- multiple conditions are applied using AND logic; i.e. criteria1 AND criteria2.
- text strings in criteria need to be in quotations. Cell references do not need to be in quotations. Empty cells are treated as zero.
- TRUE or FALSE values in range are evaluated to 1 and 0 respectively.
- use the ? and * characters for wildcards. The asterisk * matches any sequence of characters. The question mark ? matches any single character. To find an actual question mark or asterisk, type a tilde (~) preceding the character.
- logical operators (<,>,<>,=) can be used for partial matches and should be enclosed in double quotation marks "" along with non-numeric criteria being evaluated (quotation marks are not required for numeric values).
- cell references in criteria are not enclosed in quotes, i.e. "<"&A1
- is not case sensitive.
Related Functions
- COUNTIF: count values within a range that meet one specific criteria.
- SUMIF: sum (add) values in a range that meet a singular criteria.
- SUMIFS: sum (add) values in a range that meet multiple criteria.
Functions Category