## COUNTIF Function in Excel

It is a built-in function in MS Excel. COUNTIF in Excel is used to COUNTIF the number of cells containing numbers within the given range. It returns an integer number. This is an advanced version of COUNT; a built-in Excel function which counts the numeric values present in the given range of cells.

### COUNTIF Formula in Excel

The COUNTIF Formula in Excel is as follows:

The COUNTIF Formula has two arguments out of which one is required.

Where,

**range**= It is a required parameter. It represents the range of the values on which the criteria will be applied. It could be a single value of the list of values.**criteria =**It is another required parameter that represents the condition that will be applied on the values presented by the range mentioned as the first parameter. Only the values satisfying the given criteria will be returned, as a result.

The return value of COUNTIF Formula in Excel is a positive number. The value can be zero or non-zero.

### How to Use COUNTIF Function in Excel?

The said function is a Worksheet (WS) function. As a WS function, it can be entered as a part of the formula in a cell of a worksheet. Refer to the examples given below to understand better.

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

#### COUNTIF Function in Excel Worksheet

Let’s look at the examples given below. Each example covers a different use case implemented using this.

#### Example #1 – Count values with the given value

* COUNTIF (A2:A7 ,33)*

As shown in COUNTIF function in excel, it is applied to the range A2:A7. The condition here is 33. The range contains only 1 such number which satisfies the condition of being equal to 50. Hence the result returned by the COUNTIF is 1 and the same is seen in the result cell A8. Refer to the screenshot given below for the above-explained example.

#### Example #2 – Count numbers with a value less than the given number.

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

As shown in the above COUNTIF function, it is applied to the range A12:A17. The condition here is <50. The range contains 4 such numbers which satisfy the condition of being less than 50. Hence the result returned by the COUNTIF is 4 and the same is seen in the result cell A18. Refer to the screenshot given below for the above-explained example.

#### Example #3 – Count values with the given text value

=COUNTIF(A22:A27,”john”)

As shown in the above COUNTIF formula in Excel, COUNTIF function is applied to the range of values A22:A27. The condition here is the text with value ‘John’. The given range has only one cell that satisfies the given criteria. So, the result is 1 and is mentioned in the result cell A28. Refer to the screenshot given below for the above-explained example.

#### Example #4 – Count negative numbers

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

As shown in the above COUNTIF formula in Excel, COUNTIF function is applied to the range of values A32:A37 and the condition is greater than zero. Meaning, to find out and count the numbers with negative values. which are negative another parameter is hard-coded with value. So, the result returned is 3 and there are three such numbers in the given range of values. The same is seen in the result of cell A38. Refer to the screenshot given below for the above-explained example.

#### Example #5 – Count zero values

=COUNTIF(A42:A47,0)

As shown in the above COUNTIF formula in Excel, COUNTIF function is applied to the range of values A42:A47 and the condition is equal to zero. Meaning, to find out and count the numbers with zero values. So, the result returned is 2 and there are two such numbers with value zero. The same is seen in the result of cell A48. Refer to the screenshot given below for the above-explained example.

**Things to Remember**

- Non-numeric criteria must be enclosed within double quotes. However, the numeric criteria need not to be enclosed within the quotes.
- Only the values satisfying the given criteria will be returned as a result.
- The wildcard characters such as ‘
**’*and ‘?’ can be used in the criteria. The question mark matches any one character and the asterisk matches any sequence of characters. - If the wildcard characters are to be used as-is in the criteria, then those need to be preceded by tilde operator i.e. ‘~?’,’~*’.

### Usage of COUNTIF function VBA

Usage of COUNTIF function VBA is similar to that of MS Excel.

### COUNTIF Function in Excel Video

### Recommended Articles

This has been a guide to COUNTIF Function in Excel. Here we discuss the COUNTIF Formula in excel and how to use Excel COUNTIF function along with downloadable excel templates. You may also look at these useful functions in excel –

- Find & Select in Excel
- Examples of COUNTIF
- How to Use Countif not Blank in Excel?
- Count Unique Values in Excel
- How to use FIND in Excel?
- INT Excel
- COUNT in Excel
- AVERAGE in Excel
- CORREL Excel
- EXACT in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion