Excel COUNT() Function
MS Excel is currently the most popular spreadsheet program, with over 400 built-in functions. Excel is part of the MS Office suite and allows us to perform various operations on the data in an Excel sheet. The COUNT() function is one of the popular built-in Excel functions and is frequently used within the sheet.
This article discusses the step-by-step procedure of applying the Excel COUNT() function, including its syntax and examples.
What is the COUNT Function in Excel?
The COUNT function is one of the commonly used built-in functions in Excel. It can be used from a category of Statistical Functions on the ribbon in the MS Excel program. The COUNT function is primarily used to count the total numbers of cells that have numeric values as well as the number of arguments that have numeric values from the supplied range of cells. This function can accept a series of numeric values and determine the number of cells containing numerical values within the given range accordingly.
The COUNT function is typically used as a traditional Excel worksheet function. This means that we can use the Excel COUNT function as part of a formula to one or more desired cells within the Excel sheet. The COUNT function is found in all the versions of MS Excel, including the latest Office 365, Excel 2019, Excel 2016, Excel 2013, etc. The COUNT function was introduced in Excel 2000.
The COUNT function is useful in financial analysis and can help us determine or analyze the data if we want to count respective cells in a specified range. For example, we can count a range of cells that contain a date before any specific date.
Which values are counted as Numeric Values?
Numbers and dates are specific numeric values accepted by the Excel COUNT function. Furthermore, logical values and text representations are treated differently. The behaviour of the COUNT function depends on the method as to how they are supplied to the COUNT function. Specifically, the COUNT function counts these values based on whether they are used as Excel cells/range or used directly within the function.
The following table describes what values are and are not counted as numeric values by the COUNT function in Excel:
| Values Types | When the values are used within a range of cells | When the values are used directly in the function |
|---|---|---|
| Numbers | Counted by the COUNT function | Counted by the COUNT function |
| Dates | Counted by the COUNT function | Counted by the COUNT function |
| Logical | Not Counted by the COUNT function | Counted by the COUNT function |
| Text representations of numbers and dates | Not Counted by the COUNT function | Counted by the COUNT function |
| Other text | Not Counted by the COUNT function | Not Counted by the COUNT function |
| Errors | Not Counted by the COUNT function | Not Counted by the COUNT function |
Syntax
The following is the syntax of the COUNT function in Excel:
In the above syntax, the values are usually represented by the numeric data, arrays, named range, a reference to one or more desired cells, or a range with numbers.
Arguments or Parameters
The COUNT function must be used with at least one argument. That means that one argument is compulsory in the COUNT function. However, there can be more arguments depending on the requirements, but they are optional.
The COUNT function has the following arguments or parameters:
- Compulsory Argument: value1 is the required argument in the COUNT function. It is represented by the first item or cell reference or range for which we want to calculate numbers.
- Optional Argument: value2 and the rest of the subsequent arguments are optional. However, these arguments can also be represented the same way as the compulsory argument. The optional arguments can be numbers, cell references, or a range of values. These are the additional numbers or data for which we wish to count values.
In earlier versions of Excel, such as Excel 2003 and lower, the COUNT function accepts a total of up to 30 arguments. However, the limit was increased in later versions. Thus, later versions, such as Excel 2007 and higher, support a maximum of 255 individual arguments in the COUNT function. In addition, we can identify or represent each argument as an array of values or a range of cells, which may further contain other values.
Returns
The COUNT function in Excel returns the total count of numeric values from the selected/ supplied set of data cells/ ranges of numbers. In other words, it typically returns the count of cells that contain numeric values.
How to use the COUNT Function in Excel?
Counting the number of values in Excel is one of the most basic and straightforward tasks. The COUNT function effectively performs this task when counting the number of numeric values within the supplied range. We can apply or use the COUNT function as other typical worksheet functions of Excel. It means that the COUNT function can be used as a part of the formula in one or more desired cells within the Excel sheet.
To successfully apply the COUNT function in its simplest form, we can type it in any specific cell in an excel sheet and supply random numbers as arguments. For example:
In this example, the COUNT function returns the output value 4. It is clear that we have supplied four arguments in the COUNT function; thus, the total count of values is 4. Here, the arguments are directly provided to the function in the form of numeric values. However, it is quite rare to work directly with the values in the function as arguments. Generally, we work on the data stored in cells or ranges within the Excel sheet.
When working on values registered in Excel cells, we can perform the below steps to apply the COUNT function to count the total numeric values from the specified or selected range of cells or a range:
- First, we select a specific cell to store the calculated number of cells containing numerical values within the desired range. Next, we start typing the COUNT function ‘=COUNT(‘ in a selected cell without quotation marks.
- After we have typed the opening parenthesis, we select all the effective cells or range of cells for which we wish to count the resultant number of cells with numeric values. We can use the drag feature with the mouse and select the desired cells/ ranges.
- After selecting all the desired cells/ ranges, we must close the COUNT function by typing the ending parenthesis ‘)’ without quotation marks.
- Finally, we must press the Enter key to retrieve the counted cells.
For instance, suppose we have the following Excel sheet with some random numbers in cells of column A. We want to calculate or count the total number of values present in these specific cells, effectively lying in the range A1:A9. Thus, we need to use the COUNT function for this particular range only. If we consider a cell A10 a result cell, we apply the COUNT function in it similar to the image below:
Finally, we must press the Enter key to retrieve the corresponding result of the applied function.
In the above example, we have the zero value in cell A6, and the COUNT function has included this in its result. This means that the COUNT function counts the zero values. This way, we can use the COUNT function and get the total number of values for the specified range of data in an Excel sheet.
Let us now understand the different use cases of the COUNT function with the help of the following examples:
Excel COUNT Function Examples
The following examples discuss the process on how we can use the COUNT function in different ways:
Example 1: Counting the Numbers in the Specific Range
It is the most common scenario of using the COUNT function in Excel. According to this example, we can use the COUNT function in a specified range and count the numbers in the given range.
Let us consider the following Excel sheet with some students’ marks, names, and the corresponding subjects. We need to count the valid numbers in this Excel sheet.
We can perform the following steps to count the numbers in the given range:
- First, we need to select the cell to apply the COUNT function and get the results. We select cell B10 as the result cell.
- Next, we need to type the COUNT function in a selected cell. Since we want to count the numbers in the entire sheet, we select the entire range of data from cell A1 to E9 to supply the function. After that, we enter the ending parenthesis.
- After entering the COUNT function and selecting all the effective cells with data, we must press the Enter key to obtain the result. We will get the total numbers of cells with the valid numeric values in a result cell immediately, as shown in the following image:
In the above image, the COUNT function returns 32 because the specified range A1:E9 contains only 32 valid numbers. We can see that the valid numbers are only registered in a range B2:E9, and the rest of the cells have texts values that are not counted by the COUNT function.
Example 2: Counting the Numbers of Valid Dates
We can also use the COUNT function to count the valid dates registered within the Excel sheet. The valid dates are identified in the default date formats present in the Excel configuration.
Let us consider the following Excel sheet with some ordered products and their delivery dates. We want to count the number of valid dates from the range of delivery dates displayed in the sheet.
We can perform the following steps to count the numbers of valid dates (delivery dates) in the specified range of dates:
- First, we need to select a cell to record the number of valid dates. It is termed the result cell. For this, we select cell C9.
- Next, we type of COUNT function in a result cell for a range of cells C2:C8 (shown in the image below):
- After that, we must type the ending parenthesis and press the Enter key to record the results obtained by the applied COUNT function.
Although the selected range C2:C8 has dates in various formats, few of them are valid. Specifically, only three dates, written in cells C4, C5 and C8, are valid. Hence, the function returns 3, as shown in the result cell C9 of the following image:
Example 3: Counting the Numbers with Multiple Parameters
We can also apply the COUNT function to the range of Excel cells with multiple parameters, including the direct argument within the function.
Let us consider the following Excel sheet with multiple values (parameters or arguments) in cells. Here, we wish to count the valid numeric values when the additional parameter is used directly in the function, i.e., 6.
Since we have values in a range of cells A2:B8 (as shown in the above image), we need to apply the COUNT function to this entire range and another parameter 6 directly in the function. If we consider the result cell B9, then we apply the COUNT function like this:
=COUNT(A2:B8,6)
Next, we press the Enter key to get the results. In our example, the COUNT function returns 7.
Let us now understand why the function only returns 7 valid numeric values in the above example:
The selected range A2:B8 has 6 valid numeric values, written in cells A2, A3, A7, B2, B3, and B8. In addition to this, we have one numeric value (which is 6) in the function that we directly supplied as an argument. Thus, we have 7 valid numeric values in our example.
Apart from the valid numeric values, the rest of the values are not counted by the COUNT function. They are text representations (cell A5, A6, and B6), errors (cell A4), invalid date formats (cell B4 and B5), logical value (cell A8) and a blank cell (B7).
Some Important Points to Remember
To use the COUNT function appropriately, we must keep the following points or facts on our mind:
- The COUNT function can accept up to a total of 255 individual arguments.
- The desired arguments or inputs can be supplied directly into the COUNT function, cell references, or named ranges.
- The COUNT function counts the data values containing numbers, dates, or a text represented as numbers (for example- a number enclosed within the quotation marks, such as “5”).
- The COUNT function does not count the error values (such as #VALUE! and #DIV/0! Errors) or text which cannot be interpreted as numbers.
- The COUNT function does not understand the values other than numbers. That means it only counts those values from the supplied arguments, which are not in the form of text, text values, empty cells, or logical form.
- There are five variants of COUNT functions: COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS. Each one has its advantages and functions.
