COUNT Function in Excel

COUNT in Excel (Table of Contents)

• COUNT Function in Excel
• COUNT Formula in Excel
• How to Use COUNT Function in Excel?

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?

The COUNT function in Excel 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 in Excel.

 

COUNT in Excel Example #1 – Count numbers in the given range

COUNT (B3:B8)

As shown in the above COUNT formula, 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.

COUNT in Excel Example #2 – Numbers in double quotes

 COUNTA (“1”,”2”)

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.

COUNT in Excel Example #3 – Count the number of valid dates

COUNT (C3:C8)

As shown in the above Excel COUNT formula, Excel 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 in Excel is 2. The same is in the results cell i.e. C10. Refer to the screenshot given below for the above-explained example.

COUNT in Excel Example #4 – Multiple parameters

COUNT (C3:C8,5)

As shown in the above Excel COUNT formula, Excel 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.

COUNT in Excel Example #5 – Zero valid numbers

COUNT (C6:C8)

As shown in the above COUNT Formula in Excel, 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.

COUNT in Excel Example #6 – Empty range

COUNT (D3:D5)

As shown in the above excel COUNT formula, Excel 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 in excel is 0. The results of cell D10 hence contains number 0.

Things to Remember About the COUNT Function in Excel

1. 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.
2. Logical values and text representations of numbers that you type directly into the list of parameters are counted.
3. Error values or text which cannot be converted into numbers is not counted in Excel COUNT Formula.
4. 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.
5. A further extension to the function is COUNTA which is to count logical values, text, or error values.
6. 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.

You can download this Search Function in Excel template here – COUNT Function Excel Template

Recommended Articles

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 –

• 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