Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
COUNTIF 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. COUNTIF function in excel returns an integer number. COUNTIF in Excel 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 in Excel 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 COUNTIF function in Excel.
COUNTIF in Excel 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 in excel is 1 and the same is seen in the result cell A8. Refer to the screenshot given below for the above-explained example.
COUNTIF in Excel Example #2 – Count numbers with a value less than the given number.
As shown in the above COUNTIF function in excel, 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 in excel is 4 and the same is seen in the result cell A18. Refer to the screenshot given below for the above-explained example.
COUNTIF in Excel Example #3 – Count values with the given text value
As shown in the above COUNTIF formula in Excel, COUNTIF function is applied on 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.
COUNTIF in Excel Example #4 – Count negative numbers
As shown in the above COUNTIF formula in Excel, COUNTIF function is applied on 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 cell A38. Refer to the screenshot given below for the above explained example.
COUNTIF in Excel Example #5 – Count zero values
As shown in the above COUNTIF formula in Excel, COUNTIF function is applied on 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 cell A48. Refer to the screenshot given below for the above explained example.
Things to Remember About the COUNTIF Function in Excel
- Non-numeric criteria must be enclosed within double quotes. However, the numeric criteria need not 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 in Excel VBA
Usage of COUNTIF in Excel VBA is similar to that of MS Excel.
You can download this COUNTIF Function in Excel template here – COUNTIF Function Excel Template
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 –