Excel COUNT Function

Count in Excel With the COUNT Function and Counting Numbers Shortcut

Count Numbers in Excel with the COUNT Function and Counting Numbers Shortcut
Count Numbers with the COUNT Function and Counting Numbers Shortcut. © Ted French

Excel's COUNT function is one of a group of Count Functions that can be used to total the number of cells in a selected range that contain a specific type of data.

Each member of this group does a slightly different job and the COUNT function's job is to count only numbers. It can do this two ways:

  1. it will total up those cells within a selected range that contain numbers;
  2. it will total up all of the numbers listed as arguments for the function. 

So, What is a Number in Excel?

In addition to any rational number - such as 10, 11.547, -15, or 0 - there are other types of data that are stored as numbers in Excel and they will, therefore, be counted by the COUNT function if included with the function's arguments. This data includes:

If a number is added to a cell within the selected range, the function will be automatically updated to include this new data.

Counting Numbers Shortcut

Like most other Excel functions, COUNT can be entered a number of ways. Usually, these options include:

  1. Typing the complete function: =COUNT (A1:A9) into a worksheet cell
  2. Selecting the function and its arguments using the COUNT function dialog box - outlined below

But since the COUNT function is so well used, a third option - the Counting Numbers feature - has been included as well.

Counting Numbers is accessed from the Home tab of the ribbon and is located in the drop down list linked to the AutoSum icon - (Σ AutoSum) as shown in the image above.

It provides a shortcut method for entering the COUNT function and it works best when the data to be counted is located in a contiguous range as shown in the image above.

Count with Counting Numbers

The steps to using this shortcut for entering the COUNT function in cell A10 as seen in the image above are:

  1. Highlight cells A1 to A9 in the worksheet
  2. Click on the Home tab
  3. Click on the down arrow beside Σ AutoSum on the ribbon to open the drop down menu
  4. Click on Count Numbers in the menu to enter the COUNT function into cell A10 - the shortcut always places the COUNT function in the first empty cell below the selected range
  5. The answer 5 should appear in cell A10, since only five of the nine cells selected contain what Excel considers to be numbers
  6. When you click on cell A10 the completed formula =COUNT(A1:A9) appears in the formula bar above the worksheet

What Gets Counted and Why

Seven different types of data and one blank cell make up the range to show the types of data that do and do not work with the COUNT function.

The values in five of the first six cells (A1 to A6) are interpreted as number data by the COUNT function and result in the answer of 5 in cell A10.

 These first six cells contain:

  • a number - cell A1;
  • the SUM function - cell A2;
  • an addition formula - cell A3;
  • a date - cell A4;
  • a time - cell A6;
  • a blank cell - cell A5 which is ignored by the function.

The next three cells contain data that is not interpreted as number data by the COUNT function and is, therefore, ignored by the function.

  • text data - cell A7
  • a formula that generates the error value #Ref! - cell A8
  • the Boolean value TRUE - cell A9

The COUNT Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the COUNT function is:

= COUNT ( Value1, Value2, ... Value255 )

Value1 - (required) data values or cell references that are to be included in the count.

Value2 : Value255 - (optional) additional data values or cell references to be included in the count. The maximum number of entries allowed is 255.

Each Value argument can contain:

  • numbers or Boolean values entered directly as one of the function's arguments
  • individual cell references to the location of the data in the worksheet
  • a range of cell references
  • named range

Entering COUNT using the Function Dialog Box

The steps below detail the steps used to enter the COUNT function and arguments into cell A10 using the function's dialog box.

  1. Click on cell A10 to make it the active cell - this is where the COUNT function will be located
  2. Click on the Formulas tab of the ribbon
  3. Click on More Functions > Statistical to open the function drop down list
  4. Click on COUNT in the list to open the function's dialog box

Entering the Function's Argument

  1. In the dialog box, click on the Value1 line
  2. Highlight cells A1 to A9 to include this range of cell references as the function's argument
  3. Click OK to complete the function and close the dialog box
  4. The answer 5 should appear in cell A10 since only five of the nine cells in the range contain numbers as outlined above

Reasons for using the dialog box method include:

  1. The dialog box takes care of the function's syntax - making it easier to enter the function's arguments one at a time without having to enter the brackets or the commas that act as separators between the arguments.
  2. Cell references, such A2, A3, and A4 can easily be entered into the formula using pointing, which involve clicking on selected cells with the mouse rather than typing them in. Pointing is especially useful if the range to be counted consists of non-contiguous cells of data. It also helps to reduce errors in formulas caused by typing cell references incorrectly.