Excel Functions Tutorials
- Statistical Function in Excel
- AVERAGE Excel Function
- Average Formula in Excel
- AverageIF in Excel
- AVERAGEIFS Function in Excel
- CORREL Excel Function
- Correlation Matrix in Excel
- Correlation vs Covariance
- COUNT Excel Function
- Count Formula in Excel
- COUNTA Excel Function
- COUNTIF Excel Function
- COUNTIF Formula in Excel
- COUNTIFS Function in Excel
- COUNTIF with Multiple Criteria
- COUNTIF Examples
- FORECAST Excel Function
- Forecast Formula in Excel
- FREQUENCY Excel Function
- Frequency Formula in Excel
- GROWTH Excel Function
- Growth Formula in Excel
- LARGE Excel Function
- LINEST Excel Function
- Linear Regression in Excel
- Lognormal Distribution in Excel
- MAX Excel Function
- Max Excel Formula
- Mean vs Median
- MEDIAN Excel Function
- MEDIAN Formula in Excel
- MIN in Excel
- MODE Excel Function
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- PERCENTILE Excel Function
- Percentile Formula in Excel
- Percentile Rank Formula
- Poisson Distribution in Excel
- P-Value in Excel
- QUARTILE Excel Function
- RANK Function in Excel
- SLOPE Function in Excel
- SMALL Function in Excel
- Standard Deviation in Excel
- Standard Deviation Formula in Excel
- TREND Function in Excel
- T-TEST in Excel
- Chi Square Test in Excel
- Variance vs Standard Deviation
- Weibull Distribution in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
COUNTIF Function in Excel (Table of Contents)
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.
- 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 which represents the condition which 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.
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.
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
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
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
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
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 excel example and 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