COUNTIF() in Excel counts the number of cells matching a criteria. You can use it to find cells that equal to certain text, date or values. Like SUMIF(), COUNTIF() function allows wildcard characters (? and *).

Countif Function in Excel

COUNTIF() function has the following syntax:

=COUNTIF(range, criteria)

range – the range of cells where criteria should be applied

criteria – an expression that specifies the logic to apply

How to Do Countif in Excel

Below are some examples of COUNTIF() function that include an example where criteria is in a separate cell.

COUNTIF Range Equal to a Text

This one counts the number of “No”s in Discount column.

In cell H5, the formula is

=COUNTIF(tblStock[Discount], “No”)

You can change tblStock[Discount] table reference to

=COUNTIF($B2:$B8, “No”)

COUNTIF Range Less Than

To count the number of vegetables where Quantity in Stock is less than 10

=COUNTIF(tblStock[Quantity in Stock], “<10”)

COUNTIF Range Greater Than

To count the number of Vegetables where Quantity in Stock is greater than 12

=COUNTIF(tblStock[Quantity in Stock], “>12”)

COUNTIF Date

To count the number of Vegetables imported on or before Jan 3rd 2020

=COUNTIF(tblStock[Import Date], “<=3/1/2020”)

Suppose the date is in a separate cell AJ6, you need to separate the logical operators “<“, “>”, “=” from the cell address.

=COUNTIF(tblStock[Import Date], “<=”&AJ6)

COUNTIF Not Blank

The formula in cell I3 is

=COUNTIF(E2:E13, “?*”)

COUNTIF VS COUNTA

Are you seeing an unexpected result when you use COUNTA() to count cells with text?

COUNTA() will not work the way you want if the cells contain formulas.

In this case, you need to use COUNTIF(range, “?*”), where “?*” asks Excel to return only those cells with at least one character.


RELATED Post

Pin It on Pinterest

Share This