Count All Types of Data with Google Spreadsheets COUNTA

Counting All Types of Data with COUNTA in Google Spreadsheets
Counting All Types of Data with COUNTA in Google Spreadsheets.

Ted French

You can use Google Spreadsheets' COUNTA function to count text, numbers, error values, and more in a selected range of cells. Learn how with the step-by-step instructions below.

of 04

COUNTA Function Overview

While Google Spreadsheets' Count functions count up the number of cells in a selected range that contain only a specific type of data, the COUNTA function can be used to count the number of cells in a range containing all types of data such as: 

  • numbers;
  • error values - such as #DIV/0! in cell A3;
  • dates;
  • formulas;
  • text data;
  • Boolean values (TRUE/FALSE).

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 04

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

=COUNTA (value_1, value_2, ...value_30)

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

value_2 : value_30 - (optional) additional cells to be included in the count. The maximum number of entries allowed is 30.

The value arguments can contain:

Example: Counting Cells with COUNTA

In the example shown in the image above, the range of cells from A2 to B6 contain data formatted in a variety of ways plus one blank cell to show the types of data that can be counted with COUNTA.

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

  • cell A3 - uses a formula (= B2/B3) to generate the error value #DIV/0!
  • cell A4 - uses a comparison formula (=B2 > A6) to generate the Boolean value TRUE.
of 04

Entering COUNTA with Auto-Suggest

Google Spreadsheets does not use dialog boxes to enter functions and their arguments as can be found in Excel. 

Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell. The steps below cover entering the COUNTA function into cell C2 shown in the image above.

  1. Click on cell C2 to make it the active cell - the location where the function's results will be displayed;
  2. Type the equal sign ( = ) followed by the name of the function counta;
  3. As you type, an auto-suggest box appears with the names and syntax of functions that begin with the letter C;
  4. When the name COUNTA appears in at the top of the box, press the Enter key on the keyboard to enter the function name and open parenthesis (round bracket) into cell C2;
  5. Highlight cells A2 to B6 to include them as the function's arguments;
  6. Press the Enter key on the keyboard to add the closing parenthesis and to complete the function;
  7. The answer 9 should appear in cell C2 since only nine of the ten cells in the range contain data - cell B3 being empty;
  8. Deleting the data in some cells and adding it to others in the range A2:B6 should cause the function's results to update to reflect the changes;
  9. When you click on cell C3 the completed formula =COUNTA(A2:B6) appears in the formula bar above the worksheet.
of 04


To show the difference between the two functions, the example in the image above compares the results for both COUNTA (cell C2) and the better-known COUNT function (cell C3).

Since the COUNT function only counts cells containing number data, it returns a result of five as opposed to COUNTA, which counts all types of data in the range and returns a result of nine.

Dates and times are considered as numbers in Google Spreadsheets, which is why the data in cells B4 and B5 is counted by both functions;

The number 27 in cell A5 has been entered as text - as indicated by the default alignment of the data on the left side of the cell - and, as a result, has only been counted by COUNTA.