How to Count All Types of Data with COUNTA in Excel

Businessman analyzing data
Marnie Burkhart / Getty Images

Excel has several Count Functions that can be used to count up the number of cells in a selected range that contain a specific type of data.

The COUNTA function's job is to count the number of cells in a range that are not empty — that is to say that they contain some type of data such as text, numbers, error values, dates, formulas, or Boolean values.

The function ignores blank or empty cells. If data is later added to an empty cell the function automatically updates the total to include the addition.

of 07

The COUNTA Function's Syntax and Arguments

Counting All Types of Data with COUNTA in Excel

Ted French

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 COUNTA function is:

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

Value1 - (required) cells with or without data that are to be included in the count.

Value2 : Value255 - (optional) additional cells to be included in the count. The maximum number of entries allowed is 255.

The value arguments can contain:

of 07

Example: Counting Cells of Data with COUNTA

As shown in the image above, the cell references to seven cells are included in the Value argument for the COUNTA function.

Six different types of data and one blank cell make up the range to show the types of data that will work with COUNTA.

Several cells contain formulas that are used to generate different data types, such as:

  • cell A5 - uses a formula to generate the error value #DIV/0!
  • cell A7 - uses a comparison formula to generate the Boolean value TRUE.
of 07

Entering the COUNTA Function

Options for entering the function and its arguments include:

  1. Typing the complete function: =COUNTA (A1:A7) into a worksheet cell
  2. Selecting the function and its arguments using the COUNTA function dialog box

Although it is possible to just type the complete function in by hand, many people find it easier to use the dialog box to enter a function's arguments.

The steps below cover entering the function using the dialog box.

of 07

Opening the Dialog Box

To open the COUNTA function dialog box:

  1. Click on cell A8 to make it the active cell — this is where the COUNTA 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 COUNTA in the list to open the function's dialog box.
of 07

Entering the Function's Argument

  1. In the dialog box, click on the Value1 line.
  2. Highlight cells A1 to A7 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 6 should appear in cell A8 since only six of the seven cells in the range contain data.
  5. When you click on cell A8 the completed formula =COUNTA(A1:A7) appears in the formula bar above the worksheet.
of 07

Modifying the Example's Results

  1. Click on cell A4.
  2. Type a comma.
  3. Press the Enter key on the keyboard.
  4. The answer in cell A8 should change to 7 since cell A4 is no longer empty.
  5. Delete the contents of cell A4 and the answer in cell A8 should change back to 6.
of 07

Reasons for Using the Dialog Box Method

  • 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.
  • Cell references, such as​ A2, A3, and A4 can be entered into the formula using pointing, which involves clicking on selected cells with the mouse rather than typing them in. Not only is pointing easier, it also helps to reduce errors in formulas caused by incorrect cell references.