# COUNTIFS Function. Get the number of rows that match multiple criteria.(Microsoft Excel)

08/03/2024

Japanese version.

This function displays the number of matches for multiple criteria.
The COUNTIF function has a single criteria and range, while this one has multiple criteria and ranges.
The MIN and MAX functions have only IFS versions with multiple specifications and the structure is similar.
Therefore, the COUNTIFS function is recommended over the COUNTIF function.

Also, the XLOOKUP function (VLOOKUP function) cannot produce the number of matches, but the COUNTIFS function can produce the number of matches.

## How it works

`=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2 ... criteria_range127, criteria127)`

## Demonstrate

For example, it can be used to obtain only the number of items in a specific category of products that are eligible for a discount.

It can also be used in cases where a comparison of large and small items is required, such as the number of items that are eligible for a discount and have a specific unit price or higher.

### Example of obtaining the number of cases matching a specific string.

The following is an example of searching and retrieving the number of items that match a category and are subject to a discount.

In K4, specify the COUNTIFS function as follows.

`=COUNTIFS(\$D\$4:\$D\$11,J4,\$H\$4:\$H\$11,TRUE)`

The criteria_range is specified by absolute cell reference, because the reference point does not change even if the position is changed by copying.

### Example of using wildcards

Forward, partial, and backward matching can also be achieved using wildcards.

### Example of retrieval by setting a numerical threshold

The following example retrieves the number of cases where the Price is greater than 150 and less than 300.

`=COUNTIFS(E4:E11,">="&N4,E4:E11,"<"&O4)`
`=COUNTIFS(E4:E11,">=150",E4:E11,"<300")`

Comparison operators can be specified for search conditions, such as above or below a specific number.

Comparison operators must be enclosed in double-cotation marks and treated as character strings.

## Spill

If you specify the criteria as a range of cells, it will be a Spill.

Also, Spill when using the BYROW or BYCOL function.

`=BYROW(A1:C3,LAMBDA(r,COUNTIFS(r,">=2",r,"<=8")))`
`=BYCOL(A1:C3,LAMBDA(c,COUNTIFS(c,">=2",c,"<=8")))`

---