COUNT in Excel

Count Excel Formula Syntax

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 functionDATE FunctionThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more.

  • Value n: It is an optional parameter. It can range up to 255 values. The value can be a cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more or a range of values. It is a collection of worksheet cells containing a variety of data, out of which only the cells containing numbers are counted.

The COUNTA functionCOUNTA FunctionThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more is a further extension of the COUNT function. It counts logical values, text, or error values. The  COUNTIF functionCOUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more (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..

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.

Example #2 – Count Non-empty Cells

Let us apply the COUNTA functionCOUNTA FunctionThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more 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 #3 – Count 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.

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.

 “=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).

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. 

“=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).

Example #6 – Empty Range

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

“=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).

Note: The usage of the COUNT and VBA (VBA Excel COUNTVBA Excel COUNTThe count function in VBA counts how many cells have values in them. Cells with numbers or text enclosed in double quotes are counted, as are those whose values are typed directly. However, cells that contain random data that Excel is unable to translate are not counted.read more) functions is the same in Excel.

The Characteristics of the COUNT Function

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

  • 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”).It counts the list of parameters containing the logical values and text representations.It does not count the error values or text which cannot be converted into numbers.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.A further extension to the COUNT function is the COUNTA function. It counts logical values, text, or error values.Another extension of the COUNT function is the COUNTIF function, which counts the numbers that meet a specified criterion.

COUNT Function in Excel Video

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 –

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.

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.

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.

  • Example of COUNTIF with Multiple CriteriaINT Function in Excel (Integer)INT Function In Excel (Integer)INT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.read moreAVERAGE Function in Excel