COUNT Function in Excel (Table of Contents)
COUNT Function in Excel
The COUNT function in Excel is a statistical function which is used to count the number of cells containing numbers within the given range. COUNT in Excel returns an integer number.
COUNT Formula in Excel
The COUNT formula in excel is as follows:
The COUNT Formula in Excel has two arguments out of which one is required. Where,
- Value1 = It is a required parameter. This represents the starting date. The date must be entered by using the DATE Excel function. E.g.: DATE (2018,5,15)
- Value n = It is an optional parameter and can range up to 255 values. The value can be a cell reference or a range of values i.e. a collection of worksheet cells containing a variety of data out of which only the cells containing numbers would be counted.
The return value of the COUNT formula in excel is a positive number. The value can be zero or non-zero.
How to Use COUNT Function in Excel?
This is a Worksheet (WS) function. As a WS function, COUNT in Excel can be entered as a part of the formula in a cell of a worksheet. Refer to the COUNT Function examples given below to understand better.
COUNT Function in Worksheet
Let’s look at the COUNT Function examples given below. Each example covers a different use case implemented using COUNT function.
Example #1 – Count Numbers in the Given Range
As shown in the above COUNT formula, the COUNT function is applied on the range B3:B8. The range contains only 3 numbers and hence the result returned by the COUNT function is 3 and the same is displayed in the resulting cell i.e. B10. Refer to the screenshot given below for the above-explained example.
Example #2 – Numbers in Double Quotes
As shown in the above COUNT formula in excel, COUNTA function is applied to the value list separated by a comma. The values are “1”,”2”. COUNTA function is used capable of processing such values and hence the results returned and contained by the result cell B11 is 2. Refer to the screenshot given below for the above-explained scenario.
Example #3 – Count the Number of Valid Dates
As shown in the above Excel COUNT formula, the COUNT function is applied to the range of values C3:C8. The range contains dates represented using different formats out of which only 2 are written in a valid format. Hence, the result returned by the COUNT function is 2. The same is in the results cell i.e. C10. Refer to the screenshot given below for the above-explained example.
Example #4 – Multiple Parameters
As shown in the above Excel COUNT formula, COUNT function is applied on the range of values C3:C8 and another parameter is hard-coded with value 5. So, the result returned is the total number of valid numbers present in the range and the number 5. Refer to the screenshot given below for the above explained example.
Example #5 – Zero Valid Numbers
As shown in the above COUNT Formula in Excel, the COUNT function is applied on the range of values C6:C8. As shown in the screenshot below, the range does not have any valid number. So, the result returned by the COUNT function in excel is 0. The results cell B12 hence, contains number 0.
Example #6 – Empty Range
As shown in the above excel COUNT formula, the COUNT function is applied to the range of values D3:D5. As shown in the screenshot below, the range does not have any numbers i.e. it is empty. So, the result returned by the COUNT function is 0. The results of cell D10 hence contains number 0.
Things to Remember About COUNT Function in Excel
- The data values containing numbers, dates, or a text representation of numbers (for example, a number enclosed in quotation marks, such as “1”) are counted.
- Logical values and text representations of numbers that you type directly into the list of parameters are counted.
- Error values or text which cannot be converted into numbers is not counted in Excel COUNT Formula.
- If the argument is an array or reference, only the numbers in that array or reference are counted in the Excel COUNT Formula. Empty cells, logical values, text, or error values in the array or reference are not counted.
- A further extension to the function is COUNTA which is to count logical values, text, or error values.
- Another extension is COUNTIF function which is to count the numbers that meet a specified criterion.
Usage of COUNT function in Excel VBA
VBA Excel COUNT function is used in the same way as that of Excel.
COUNT Function in Excel Video
This has been a guide to COUNT Function in Excel. Here we discuss the COUNT Formula in excel and how to use COUNT function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Excel VBA IsEmpty
- COUNTIF Examples of Excel
- How to Create a Correlation Matrix in Excel?
- Excel Cell Reference | Top 3 Types
- Example of COUNTIF with Multiple Criteria
- INT Function in Excel (Integer)
- AVERAGE Function in Excel
- CORREL Excel Function (Correlation)
- CEILING Excel Function
- TIME Excel Function