COUNTA function in Excel, function to count cells containing data with specific usage and examples

The COUNTA function in Excel is a useful data statistics function. Below are details on how to use the function to count cells with data in Excel .

Microsoft Excel has long been famous as a smart, professional and useful spreadsheet application in managing and analyzing data. It offers a rich, useful set of features including intelligent functions.

You probably already know that Excel can perform calculations with numbers. But did you know that it can also count other types of information? One of the simplest examples of that is the COUNTA function .

COUNTA looks at a range of cells and tells you how many cells contain data. In other words, it finds cells that are not empty. This can be useful in many different situations.

If you have never used Excel functions, you can refer to the lessons on TipsMake. Essentially, COUNTA works the same on all versions of Excel, similar to other spreadsheet applications.

Video tutorial on using the COUNTA function in Excel

How to count the number of cells containing data in Excel

The COUNTA function in Excel has the function formula COUNTA(value1, [value2], .) .

In which value 1, value 2 are the list of values, cell ranges or references you want to count. Value 1 is a required parameter and the remaining values ​​are optional, containing a maximum of 255 values.

Attention:

1. The function counts cells containing any data type except blank cells.
2. If you do not want to count logical and text values ​​or error values, use the COUNT counting function.
3. If you want to count values ​​that satisfy certain conditions, use the COUNTIF or COUNTIFS function.

For example, the COUNTA function counts Excel cells in a range

For example, with a data table with different values, use the COUNTA function to count the number of cells containing data in the table.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 1

In the result of the Value box, enter the formula =COUNTA(C2:C7) and press Enter.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 2

The result showing 6 means there are 6 cells containing data in the Value column, regardless of the values ​​in the cells including text and dates.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 3

If you delete data in any cell, the number of resulting cells automatically decreases. Then the COUNTA function will automatically ignore those blank cells.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 4

For example, the COUNTA function counts Excel cells in multiple ranges

With exercises to count cells in multiple Excel ranges, COUNTA still counts exactly the same as when counting cells in a range. We have an Excel file with 2 data tables as below. Counts the number of cells containing data in the Value column.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 5

In the input box to calculate the number of non-empty cells, enter the formula =COUNTA(C2:C5,C8:C12) and press Enter. In which C2:C5 is the first data area that wants to count the number of cells and C8:C12 is the second data area that wants to count the number of cells.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 6

The result displaying 6 means that the total number of cells containing data on both data tables in the Value column range is 6. Even if you count the number of cells across multiple ranges, the COUNTA function will automatically ignore empty cells and only count cells that contain data regardless of data type.

COUNTA function in Excel, function to count cells containing data with specific usage and examples Picture 7

So using the COUNTA function to count the number of cells containing data in Excel is very simple and quick. You can use the function to count non-empty cells in a range or many different ranges. The numbers are counted without distinction except for empty cells.

Wishing you success!

Lesley Montoya

Update 16 July 2024