COUNTA Excel Function

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.

The syntax of the function is given as follows:

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.

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

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)”

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.

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)”

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.

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”)”

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.

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)”

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)”

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.

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.