COUNTA Excel Function

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Vandana Kataria

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What is the COUNTA Function in Excel?

The COUNTA function is an inbuild statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2.

The COUNTA function can count cells containing several types of data values. This includes text, numbers, Boolean values, date/time values, error values, and empty text strings (“”). It returns a numeric value.

Table of contents
COUNTA Excel Function

The Syntax of the COUNTA Function

The syntax of the function is given as follows:

COUNTA Formula

The function accepts the following arguments:

  • Value1: This represents the values that are to be counted.
  • Value2: This also represents the values that are to be counted.

The first argument is mandatory, while the second is optional. The argument can be a range, a cell, a value, an array of values, or a reference to cell ranges.

Note: A maximum of 255 arguments can be entered in MS Excel 2007 and the subsequent versions. The earlier versions of Excel can handle 30 arguments only.

How to Count Non-Blank Cells using COUNTA?

The COUNTA function is used when there is a need to count the number of cells in single or multiple non-blank ranges. The cell range can also be non-adjacent.

For example, for counting cells in the range B1:B50, the formula is “=COUNTA (B1:B50).”

The function also counts the number of value arguments provided. The value argument is a parameter that is neither a cell nor a range of cells.

The COUNTA function is used to count the following categories of data:

  • The number of customers in a list
  • The number of transactions in a given time period
  • The number of tests submitted by students
  • The number of employees having an e-mail address
  • The number of presentations delivered

Example #1 - Single Range

The data is given in the succeeding image. We want to determine the number of non-blank cells in the range A2:A7.

  1. We use the following formula

    “=COUNTA(A2:A7)”

    COUNTA Function Example 1

  2. The COUNTA function counts the number of data cells from A2 to A7. It returns 5 because cell A5 is blank. Hence, all the values are counted except the blank value of cell A5.

    COUNTA Function Example 1-1

Example #2 - Multiple Ranges

The data is given in the succeeding image. We want to determine the number of non-blank cells in two cell ranges A2:A7 and B2:B4. 

We use the following formula.

“=COUNTA(A2:A7,B2:B4)”

COUNTA Function Example 2

The COUNTA function counts the number of data cells from A2 to A7 and B2 to B4. It returns 7 because the cells A5 and B3 are blank. Hence, all values are counted except the blank cell values.

COUNTA Function Example 2-1

Example #3 - Multiple Columns Range

The grades in Maths, English, and Computer of 5 students are shown in the succeeding image. For each subject, we want to count the number of students who have been allotted a grade.

We use the following formulae.

“=COUNTA(B2:B6)”
“=COUNTA(C2:C6)”
“=COUNTA(D2:D6)”

COUNTA Function Example 3

The COUNTA function counts the number of grades in Maths from B2 to B6, in English from C2 to C6, and in Computer from D2 to D6. It returns the values 3, 2, and 3 respectively.

COUNTA Example 3-1

Example #4 - Value Arguments With Range

Let us supply direct values and a range to the COUNTA function.

Working on the previous example, let us assume that the missing students “Neha” and “Rahul” also appeared for the Maths test.

We use the following formula.

“=COUNTA(B2:B6,“Neha”,”Rahul”)”

COUNTA Example 4

The COUNTA function counts the number of non-empty cells in the range B2:B6. To this output, it adds “2” due to the two additional value arguments–“Neha” and “Rahul.” Hence, the total number of students who appeared for the Maths test is 5.

COUNTA Example 4-1

Example #5 - Multiple Value Arguments

Let us supply direct values to the COUNTA function.

We want to find the number of non-blank values within a set of direct values.

We use the following formula.

“=COUNTA(1,2,“”,text,TRUE)”

COUNTA Function Example 5

The COUNTA function returns the number of non-blank values from the total direct values. Hence, it returns 5.

Example #6 - Contiguous Rectangle Range

We want to find the number of non-blank cells in the contiguous rectangle A2 to B6, shown in the succeeding image.

In the formula, we specify the entire range beginning from the upper-left cell to the lower-right cell.

“=COUNTA(A2:B6)”

COUNTA Example 6

The COUNTA function counts the number of cells containing data in the range A2:B6. It returns 7 because cells A5, B3, and B5 are blank. Hence, all values are counted except those in the blank cells.

COUNTA Example 6-1

The Characteristics of COUNTA Function

The features of the function are listed as follows:

  • It counts all non-blank cells, unlike the COUNT function, which counts only numeric values.
  • It only counts the existing values without summing them.
  • It returns an error if incorrect arguments are provided to it.
  • It counts cells that contain invisible characters. For instance, an empty string (“”) returned by a formula is also counted.
  • It counts the hard-coded values. For example, “=COUNTA(“c”,2,4,””)” returns 4.
  • It counts every non-empty cell, cell range, and value argument as 1.
  • It counts the cell in which space is entered, implying that only the absolutely empty cells are excluded from the count.

Note: To count the cells that meet certain conditions, the COUNTIF or COUNTIFS functions are used.

Frequently Asked Questions

Define the COUNTA function in Excel.

The COUNTA function counts the non-empty cells in a range or list of items. In case multiple ranges are supplied to the function, it is not necessary that every range is of the same size.

The COUNTA function belongs to the family of COUNT functions. The syntax of the function is given as follows:
“COUNTA(value1,,…)”

The “value 1” (mandatory) and “value 2” arguments represent the values to be counted.

What is the purpose of using the COUNTA function in Excel?

The objective of the COUNTA function is to provide the exact number of cells containing data. The function is used when a quick count of the listed items is required. For example, the accountant uses the COUNTA function while reviewing the inventory numbers.

In simple words, COUNTA stands for “count all” with the only exception being the blank cells.

What is the difference between the COUNTA and the COUNT functions of Excel?

The differences between the two functions are listed as follows:

• The COUNTA function counts the non-empty cells within a specified range. On the other hand, the COUNT function counts the number of cells containing numeric values.
• The COUNTA function is more inclusive than the COUNT function because the former counts all kinds of data values. In contrast, the latter ignores the non-numeric values when provided as a cell reference.

Note: While deciding which of the two functions to use, the data to be counted must be analyzed.

  • The COUNTA function counts the number of non-blank cells in a range.
  • The COUNTA function counts the cells containing text, numbers, Boolean values, date/time values, error values, and empty text strings (“”).
  • The syntax of the COUNTA function is–“COUNTA(value1,,…),” where “value 1” (mandatory) and “value 2” are the values to be counted.
  • The argument of the COUNTA function can be a range, a cell, a value, an array of values, or a reference to cell ranges.
  • The COUNTA function returns a numeric value and excludes only the absolutely empty cells from the count.
  • In Excel 2007 and the subsequent versions, a maximum of 255 arguments to the COUNTA function can be entered.