What is COUNTIF Function in Excel

Syntax

The syntax of COUNTIF formula in Excel is stated as follows:

It accepts the following required arguments:

  • Range: It represents the range of values on which the criteria will be applied.Criteria: It represents the condition that is applied to the range of values.  The values that meet the criteria are returned as a result.

The output of the COUNTIF formula is a positive number which can be zero or non-zero.

How to Use COUNTIF Function in Excel?

Being a worksheet (WS) function, the COUNTIF function can be entered as a part of the cell formula. The usage of the COUNTIF function is the same in Excel and VBA (COUNTIF function VBACOUNTIF Function VBAVBA COUNTIF is a worksheet function used to count the number of times the criteria are fulfilled in the worksheet range. The VBA code for this function is written as WorksheetFunction.CountIf.read more).

Let us consider some uses to understand the working of the COUNTIF excel function.

#1 – Count Values with the Given Value

The following table shows a list containing numerical values in the cells A2:A7. Count the given range of cells for the values matching the number “33”.

The COUNTIF function is applied to the given range of cells, and the formula is stated as follows:

“=COUNTIF (A2:A7,33)”

Here the condition applied to the formula is the number “33”.The formula checks the range of cells A2:A7 for the values matching number “33”. The range contains only one such number, which satisfies the condition. Hence the result returned by the COUNTIF function is “1”. The result is displayed in cell A8.

#2 – Count Numbers with a Value Less Than the Given Number

A list of data in the cells A12:A17 is provided in the succeeding table. Count the given range of cells for the values less than “50”.

“=COUNTIF(A12:A17,“<50”)”

Here the condition applied to the formula is “<50”. The COUNTIF formula checks the range of cells matching the condition, less than 50. There are only four values that are less than 50 in the range. Hence the result returned by this function is “4”. The result is displayed in cell A18.

#3 – Count Values with the Given Text Value

A list of data in the cells A22:A27 is provided in the below table. Count the range of cells for the text value “john”.

“=COUNTIF(A22:A27, “john”)”

Here the condition applied to the formula is the value “john.” The COUNTIF formula checks the range of cells matching the given condition. The given range has only one cell that satisfies the text value “john”. Hence, the result is “1” which is displayed in cell A28.

#4 – Count Negative Numbers

A list of data in the range of cells A32:A37 is provided in the below table. Count the range of cells for negative values (that is, less than zero).

“=COUNTIF(A32:A37, “<0”)”

Here the condition applied to the formula is less than zero. Now, the COUNTIF formula will identify and count the numbers with negative values in the given range. There are three such numbers in the given range. Hence, the result is “3” which is displayed in cell A38.

#5 – Count Zero Values

A list of data in the range of cells A42:A47 is provided in the below table. Count the range of cells for the value “0”.

“=COUNTIF(A42:A47,0)”

Here the condition applied to the formula is equal to “0”. Now the COUNTIF formula will identify and count the numbers with zero values. There are two zeros in the range. Hence, the result returned is “2” which is displayed in cell A48.

The Criteria of the COUNTIF Formula

It is enclosed within double quotes if non-numeric and without quotes if numeric.

It returns the result only if the values in the cell range satisfy the given criteria.

It can be supplied with wildcard characters like “*” and “?”. The question mark matches any one of the characters, and the asterisk matches any sequence of characters.

It uses the tilde operators followed by the wildcard characters such as “~?” and “~*”.

Video

This has been a guide to the COUNTIF function in Excel. In this article, we have discussed how to use COUNTIF formula along with case studies and downloadable templates. You may also look at the below useful functions in Excel –

COUNTIF function allows counting of the range of cells that meets the specified criteria. It is used to count cells that contain dates, numbers, and text.The formula is stated as follows:“=COUNTIF(range,criteria)”Where,• “Range” is a required parameter that refers to the range on which the criteria will be applied.• “Criteria” is another required parameter that represents the condition applied to the values of the range.

The syntax of COUNTIF function for the same range of cells (“range 1”) with multiple criteria (“criteria 1”, “criteria 2”) is as follows:“=COUNTIF(range 1,criteria 1)+COUNTIF(range 1,criteria 2)”In the case of counting multiple ranges of cells with multiple criteria, the COUNTIFS function can be used.

COUNTIFS function is used to evaluate cells across multiple ranges, based on single or multiple conditions. It is similar to the COUNTIF, but multiple criteria are used in the formula. The formula of the COUNTIFS is stated as follows: “=COUNTIFS(range 1, criteria1, range 2, criteria 2… )” Where, • “Range” refers to the given range of cells. • “Criteria” refers to the conditions applied to the range.

  • How to Use Countif not Blank in Excel?How To Use Countif Not Blank In Excel?The COUNTIF not blank function counts non-blank cells within a range. read moreCount Unique Values in ExcelCount Unique Values In ExcelIn Excel, there are two ways to count values: 1) using the Sum and Countif function, and 2) using the SUMPRODUCT and Countif function.read moreHow to use FIND in Excel?How To Use FIND In Excel?Find function in excel finds the location of a character or a substring in a text string. In other words it finds the occurrence of a text in another text, as it gives us the position, the output returned by this function is an integer.read moreCOUNT in Excel