COUNTA in Excel

When you enter data in Excel, you sometime do it with a lot of cells left blank to fill them at later stage when data is available. When you want to count the number of entries you made in cells range then you need a specific function in Excel to count only cells that are filled or Non-Blank. For this purpose you need to use a special built-in function that is called COUNTA Excel function.

COUNTA Excel function returns count of cells that are not empty or Non Blank. It returns a number representing count of values. It counts cells that contain numbers, text, date values, time values, logical values (TRUE and FALSE), error values, and empty text (“”), but it does not counts empty cells.

Syntax of COUNTA Excel function is;

=COUNTA (value1, [value2], ...)

It supports up to 255 value arguments. A value argument could be a hard coded value, cell reference or a range.  

 

In this article you will learn how to use COUNTA in Excel. Syntax of this function is very easy to follow. If you have all the data in one range then you need to enter only one range in COUNTA function to count non empty cells. Like, if you have a range A1:A100 from which you want to count cells containing values, so you will use this range as follows;

=COUNTA(A1:A100)

 

But if you have data in various ranges, and you want to count values from multiple ranges in one go, then you will enter each range as value argument in COUNTA Excel function, such as;

=COUNTA(B1:B100,J2:K50,M1:N100)

 

COUNTA function may count those cells that are visually empty, but actually they are not as these cells may contain empty text (“”) or space and COUNTA function consider such entries as value. So it only ignores those cells that are actually empty.

Suppose you have various types of values in a range of cells A2:A10 and you want to count cells containing values, excluding only those cells that you are actually empty, not visually empty.

So visually there are 7 cells that have values in range A2:A10, but COUNTA function returns the result as 8 cells that are non-empty. This is because of cell A7 that contains empty text string (“”) and it is counted in COUNTA function as value.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar