How to use COUNTIF function in Excel 2019

COUNTIF function in Excel

COUNTIF function is used to count cells in a range that meets a given criterion. Data Range can contain numbers, text or date for matching. Criterion can be any of Logical operators “>” (Greater than), “>” (Less than), “<>” (Not equal to) , “=” and “*,?” (Wildcard partial match). This Function returns Number of cell matching criteria. COUNTIF is supported from Excel 2003 onwards. Below is syntax for use COUNTIF function in Excel.

Syntax

=COUNTIF (range, criteria)

range – Range of cells are cells which contain data and are to be counted.

criteria – This criterion is matched to data in each cell and returns count of cells matching.

Note: COUNTIF can match only single criterion, for matching multiple criteria use COUNTIFS function. Also note COUNTIF function is not case sensitive i.e., while matching small case and large case text will be treated as same (e.g. apple and APPLE will be treated as same).

Example use of COUNTIF in Excel 2019

Here COUNTIF compares data in Range of cells and compares to criteria like text, comparison to number etc.

COUNTIF – Count if number is equal to from cell range.

If cell range is A2:A66 and we have to check for equal to 45 in this range.

Syntax

=COUNTIF(A2:A66,”=45”)

COUNTIF – Count if number is greater than from cell range.

If cell range is A2:A66 and to check for greater than 45 in this range.

Syntax

=COUNTIF(A2:A66,”>45”)

COUNTIF – Count if number is less than from cell range.

If cell range is A2:A66 and to check for less than 45 in this range.

Syntax

=COUNTIF(A2:A66,”<45”)

COUNTIF – Count if number is not equal to from cell range.

If cell range is A2:A66 and to check for criteria not equal to 45 in this range.

Syntax

=COUNTIF(A2:A66,”<>45”)

COUNTIF – Count if text exists cell range.

If cell range is A2:A66 and for checking text “local” in this range.

Syntax

=COUNTIF(A2:A66,”local”)

COUNTIF – count for criteria existing in another cell.

If cell range is A2:A66 and for checking criteria which exists in another cell d4. Below we will check for creatria if value in range is <= criteria in cell D4.

Syntax

=COUNTIF(A2:A66,”<=”&D4)

Note: Here Operator like <, >, = etc will be enclosed in Double quotes.

COUNTIF – count using wildcard character for text criteria.

If cell range is A2:A66 and for checking wildcard character criteria use below syntax.

Syntax

=COUNTIF(A2:A66,”*red*)

This  will check for text red in range of cells

=COUNTIF(A2:A66,”a???”)

This will check for four letter words starting with a.

Note: Here Operator like <, >, = etc will be enclosed in Double quotes.