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:
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
You can change tblStock[Discount] table reference to
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”)
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 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.