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 is one such function that'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.

of 03

COUNTA Function Syntax and Arguments

Screenshot of Excel showing COUNTA Formula

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 to Value255: (optional) Additional cells to be included in the count. The maximum number of entries allowed is 255.

The value arguments can contain individual cell references to the location of data in the worksheet, a range of cell references, and named ranges.

of 03

Counting Cells of Data with COUNTA

Screenshot of Excel showing a data list to be counted

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.

Options for entering the function and its arguments include:

  • Typing the complete function, as shown above, into a worksheet cell.
  • Selecting the function and its arguments using the Formula Builder.
of 03

Entering the COUNTA Function

Screenshot of Excel showing the Formula Builder with COUNTA

Although it is possible to type the complete function in by hand, many people find it easier to use the Formula Builder to enter a function's arguments. The steps below cover entering the function using the builder.

  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.
  4. Click on COUNTA in the list to open the Formula Builder.
  5. Click on the Value1 line.
  6. Highlight cells A1 to A7 to include this range of cell references as the function's argument.
  7. Click Done to complete the function

The answer 6 should appear in cell A8 since only six of the seven cells in the range contain data. When you click on cell A8 the completed formula appears in the formula bar above the worksheet.

Modifying the Example's Results:

The COUNTA function works in real-time, so if you alter a piece of data in your spreadsheet, it will automatically update. Follow the steps below to modify our example function and have its results update:

  1. Click on cell A4.
  2. Type a comma.
  3. Press the Enter key on the keyboard.

The answer in cell A8 should change to 7 since cell A4 is no longer empty.