COUNTIFS Function. Get the number of rows that match multiple criteria.(Microsoft Excel)
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)
| Name | Omission | Description |
|---|---|---|
| criteria_range1 - 127 | Only 1 is required. | Specify the range of cells to be covered by the criteria. |
| criteria1 - 127 | Only 1 is required. | Specify the criteria to be included in the tally. |
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")))
---
Links
Count of multiple values or cell ranges.
| Function | Importance | Description |
|---|---|---|
| COUNT | High | Count of multiple values or cell ranges. |
| COUNTA | High | Get the number of non-blanks. |
| COUNTBLANK | Middle | Get the number of blanks. |
| COUNTIF | Middle | Get the number of cells matching the search criteria. |
| COUNTIFS | High | Get the number of rows that match multiple criteria. |