COUNTIF Function. Get the number of cells matching the search criteria.(Microsoft Excel)


Japanese version.

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)
rangeRequired argument.Specify a range of cells to be evaluated for aggregation and search criteria.
criteriaRequired argument.Specify criteria for aggregation.


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.


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.


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.


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



Count of multiple values or cell ranges.

COUNTHighCount of multiple values or cell ranges.
COUNTAHighGet the number of non-blanks.
COUNTBLANKMiddleGet the number of blanks.
COUNTIFMiddleGet the number of cells matching the search criteria.
COUNTIFSHighGet the number of rows that match multiple criteria.

Microsoft Excel Functions Statistical