How to Count All Types of Data With COUNTA in Excel

Close-up Of A Person's Hand Typing On Laptop Over Wooden Desk

 AndreyPopov / Getty Images

Excel has several Count Functions that you can use to count up the number of cells in a selected range that contain a specific type of data. COUNTA is one such function, and its job is to count the number of cells in a range that are not empty — that is to say that they contain data such as text, numbers, error values, dates, formulas, or Boolean values.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365.

COUNTA Function Syntax and Arguments

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:

Value1 (required) — the range of cells 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.

Counting Cells of Data With COUNTA

As shown in the image above, the cell references to seven cells are in the Value argument for the COUNTA function. Six different types of data and one blank cell make up the range and include 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 — Contains an incomplete formula, resulting in 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 Function Dialog Box (Formula Builder on Mac)

Entering the COUNTA Function

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

Using Excel's Function Dialog Box to add arguments for a COUNTA formula.
  1. Click cell A8 to make it the active cell — this is where the COUNTA function will go.

  2. Click the Formulas tab of the ribbon.

  3. Click on More Functions > Statistical to open the function drop-down menu.

  4. Select on COUNTA in the list to open the Function Dialog Box.

  5. Click 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

  8. The answer 6 should appear in cell A8 since only six of the seven cells in the range contain data.

  9. 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.

  1. Click 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.

Was this page helpful?