COUNTIF Function. Get the number of cells matching the search criteria.(Microsoft Excel)
The COUNTIF function is an extended version of the COUNT function that displays the number of cells in a specified range of cells that satisfy a specified condition.
There is also a COUNTIFS function that specifies multiple conditions as a higher-level version of the COUNTIF function.
How it works
=COUNTIF(range, criteria)
| Name | Omission | Description |
|---|---|---|
| range | Required argument. | Specify a range of cells to be evaluated for aggregation and search criteria. |
| criteria | Required argument. | Specify criteria for aggregation. |
Demonstrate
For example, it can be used to count only the number of items in a specific category of products.
It can also be used in cases where large and small comparisons are used as criteria. (e.g., the number of items over a certain amount)
Example: Retrieving the number of matches for a specific string
The following is an example of retrieving and obtaining the number of cases that match the type of I-string.
In J4, specify the COUNTIF function as follows.
=COUNTIF($D$4:$D$9,I4)
The "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
Here is an example of retrieving the number of cases above a specific amount.
=COUNTIF($E$4:$E$9,">="&I4)
Comparison operators can be specified for search conditions, such as above or below a specific number.
In this example, the first line shows the number of cases over 300, and the second line shows the number of cases over 1,000.
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,COUNTIF(r,">=5")))
Row direction
=BYCOL(A1:C3,LAMBDA(c,COUNTIF(c,">=5")))
---
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. |