COUNTA function in excel

COUNTA() function is similar to the COUNT() function in excel, which counts the number of data cells in a specific row or column. COUNT() function can only count the data cells that contain numbers, while COUNTA() can count both the numbers, symbols and characters in the data cells in excel.

Let’s consider an example.

Consider the below data:

The number of cells in the above row that contains the names is nine. When we apply COUNT() function to count the cells, excel will return 0. It is because COUNT() does not found any cell that contains numbers. But, it will not happen in the case of the COUNTA() function. Here, Excel will return nine as a result.

Note: COUNTA() and COUNT() function does not work on the blank cells. It ignores the blank spaces in between the rows or columns.

Syntax

It is given by:

Where,

Number1: specifies the first number specified in the function

Number2: It specified the values that we want to count. We can specify as many values in the function, but maximum upto 255 arguments.

Note: An equal sign always represents the function at the beginning. It means that it will be considered a function if we insert an equal sign at the beginning of the function. Otherwise, it will be considered as a text of a respective cell.

In the case of the range of cells, it is given by:

For example,

COUNTA(A3:A10)

Here, excel will count the data cells ranging from A3 to A10, i.e., A3, A4, A5, A6, A7, A8, A9, and A10.

We can also count data cells of one or more row and columns in excel.

To count the data cells of two columns, we can declare the function as:

COUNTA(A2:10, B2:B10)

Or

COUNTA(A2:B10)

It will count the data cells from A2 to A10 and B2 to B10. Thus, total cells will be 9 + 9 = 18.

To count the data cells of two rows, we can declare the function as:

COUNTA(C9:J9, C10:J10)

Or

COUNTA(C9:J10)

It will count the data cells from C9 to J9 (C9, D9, E9, F9, G9, H9, I9, and J9) and C10 to J10 (C10, D10, E10, F10, G10, H10, I10, and J10). Thus, total cells will be 8 + 8 = 16. If any cell in empty, excel will skip that cell.

COUNTA() vs. COUNT()

Lt’s discuss some differences between COUNT() and COUNTA().

Consider the table that clearly depicts the counting ability on different values of both the COUNT() and COUNTA() functions.

From the above table, we can see that the COUNTA() function is able to count all types of values in the data cells, while COUNT() can only count data cells with numbers.

Remarks

Consider the below remarks before beginning with COUNTA() function.

• We can use the COUNT() function if we require to count the data cells containing data in the form of digits.
• To use any criteria with the counting, we can use the COUNTIF() function in excel.
• If a formula returns an empty string, the COUNTA() function will also count that data cell.
• COUNTA() function call count the data cells containing error values.
• If any type of argument is present in the cell, except the blank cell, it will be counted as 1.

Examples

Let’s consider some examples.

Example 1: To count the number of students in the given data.

Solution: Consider the below steps:

1. Click on any cell near the given table where we want to find the total number of students.
2. Type ‘=COUNTA(B3:B10),‘ as shown below:
   
   The COUNTA() function will count the cells containing names starting from B3 to B10.
3. Press Enter.
4. The desired number of students will appear on the specified column as ‘8,’ as shown below:
   

Example 2: To count the non-empty cells in excel.

Solution: Consider the below steps:

1. Click on any cell near the given table where we want to find the total number of students.
2. Type ‘=COUNTA(C2:C10),’ as shown below:
   
   The COUNTA() function will count the cells containing data starting from C2 to C10. There are three empty cells present in the specified data. Hence, excel will skip such empty cells and counts only the cells that contain any type of data.
3. Press Enter.
4. The desired number of non-empty cells will appear on the specified column as ‘6,’ as shown below:
   

Example 3: To count data cells of more than two columns in excel.

The data given is the attendance of five students of three days. The students absent on a particular day is marked black on the excel sheet. Here, we will count the data cells of three columns using the COUNTA() function. The result will be the number of students present on three days.

The data is given as:

Total students = 5

Total days of attendance = 3

Total data cells = 15

Total attendance of the three days will be calculated. We can also say that we will count the non-empty cells of the three columns.

Solution: Consider the below steps:

1. Click on any cell near the given table where we want to find the total number of students.
2. Type ‘=COUNTA(B4:B8,C4:C8,D4:D8),‘ as shown below:
   
   We can also declare the function as ‘=COUNTA(B4:D8).’ It directly specifies the limit from B4 to D8. Both the statements will work as the same. The COUNTA() function will count the cells containing data starting from B4 to D8.
3. Press Enter.
4. The desired number of total attendance of the three days will appear on the specified column as ’11,’ as shown below: