Count Function in Excel

COUNT in Excel

The COUNT function in excel counts the number of cells containing numerical values within the given range.  It is a statistical function and returns an integer value. The syntax of the COUNT formula is “=COUNT(value 1, [value 2],…)

Count Excel Formula Syntax

COUNT Formula

Where,

  • Value1: It is a required parameter. For example, to count a range of cells that contain a date before April 1, 2021, the formula used: “=COUNT(“Cell Range”, “<”&DATE (2021,4,1))”. The date is entered by using the DATE function.

The COUNTA function is a further extension of the COUNT function. It counts logical values, text, or error values. The  COUNTIF function (another extension of the COUNT function) counts the numbers that meet a specified criterion.

How to Use COUNT Function in Excel?

Let us look at the examples of the COUNT function. Each example covers a different case, implemented using the COUNT function..

You can download this COUNT Formula Excel Template here – COUNT Formula Excel Template

Example #1 – Count Numbers in the Given Range

Let us apply the COUNT function to the range of cells B3:B8 to count the numbers in the given range (shown in the table below).

The COUNT formula in excel is stated as follows.

“=COUNT(B3:B8)”

The range B3:B8 contains only three numbers.  Hence the COUNT function returns 3.  The result is displayed in cell B10. The succeeding image shows the result of the COUNT formula applied in this example.

COUNT Example 1

Example #2 – Count Non-empty Cells

Let us apply the COUNTA function to the range of cells A1:A5 provided in the below table to calculate the number of cells that are not empty.

The COUNTA formula in excel is stated as follows:

 “=COUNTA(A1:A5)”

The COUNTA function counts the number of cells in A1 through A5 that contain some data. It returns a value of 4, as cells A1, A2, A3, A4 are not empty and only cell A5 is empty. The result is displayed in cell B1.

The output of the COUNTA function is indicated in the image given below.

Example #3Count the Number of Valid Dates

Let us apply the COUNT function to count the number of valid dates to the range of cells C3:C8 (shown in the table below).

The COUNT formula is stated as follows:

“=COUNT(C3:C8)”

The range contains dates in different formats. Out of this, only two dates are valid. Hence, the formula returns 2, as shown in cell C10 of the following image.

COUNT Example 3

Example #4 – Multiple Parameters

Let us apply the COUNT formula to the range of excel cells C3:C7 (provided in the table below) along with another parameter that is hard-coded with a value of 5.

The COUNT formula is stated as follows:

 “=COUNT(C3:C7,5)”

The number of cells (C3 through C7) with valid numeric values or dates is 2, plus 1 for the number 5. Hence the COUNT formula returns the result as 3 (cells C5, C6, and the number 5).

Note that the date in cell C7 is invalid and so is not taken into account in the given results. The result is displayed in cell C11 (indicated in the below image).

COUNT Example 4

Example #5 – Invalid Numbers

Let us apply the COUNT formula to the range of values C6:C8 (shown in the table below) containing invalid numbers. 

The COUNT formula is stated as follows:

“=COUNT(C6:C8)”

The range does not have any valid number.

Hence, the result returned by the formula is 0, indicated in cell B12 (shown in the below image).

COUNT Function Example 5

Example #6 – Empty Range

Let us apply the COUNT function to the range of values in cells D3:D5, which is an empty range.

The COUNT formula is stated as follows:

“=COUNT(D3:D5)”

The given range does not have any numbers, and it is empty. Hence, the result returned by the formula is 0, indicated in cell D10 (shown in the below image).

COUNT Function Example 6

Note: The usage of the COUNT and VBA (VBA Excel COUNT) functions is the same in Excel.

The Characteristics of the COUNT Function

The features of the COUNT excel function are listed as follows:

  1. It counts the data values containing numbers, dates, or a text represented as numbers (e.g., a number enclosed in quotation marks, such as “3”).
  2. It counts the list of parameters containing the logical values and text representations.
  3. It does not count the error values or text which cannot be converted into numbers.
  4. It counts only the numbers and not the logical values, empty cells, text, or error values when the argument seems to be an array or reference.
  5. A further extension to the COUNT function is the COUNTA function. It counts logical values, text, or error values.
  6. Another extension of the COUNT function is the COUNTIF function, which counts the numbers that meet a specified criterion.

Frequently Asked Questions (FAQs)

How to use the Excel COUNT function?

The COUNT function provided the count of cells containing numbers within the given range of cells. It also counts numeric values within the list of arguments.
The formula of the COUNT in excel is,
“=COUNT(value 1, [value 2],…)”
Here the “value 1” is the required argument, which can be an item, cell reference, or range, “value” 2 is an optional argument.
Example: To count the numbers in the range A1:A10, the formula “=COUNT(A1:A10)” is used. Here, if seven of the cells in the given range contain numbers, then the result is 7.

What is the COUNTIF formula?

COUNTIF function counts cells in a range that meets a single criterion. It counts cells that contain dates, numbers, and text.
The COUNTIF formula in excel is stated as follows:
“ =COUNTIF(range, condition)”
Here “range” is series of cells to count.

What is the difference between the COUNT and COUNTA functions in Excel?

The COUNT function is generally used to count a range of cells containing the numbers or dates. It excludes blank cells. Whereas, COUNTA function will count the numbers, dates, text, or a range containing a mixture of all these items. It does not count blank cells.
Note: COUNTA stands for “count all.”

COUNT Function in Excel Video

 

Recommended Articles

This is a guide to the COUNT function in Excel. In this article, we have discussed how to use this formula along with step-by-step examples and a downloadable template. You may also look at the below useful functions in Excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *