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:
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:
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.
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:
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:
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:
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:
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: