How to Use COUNTA in Excel

Learn How to Use the COUNTA Function in Excel

When you enter data in Excel, you sometimes do it with a lot of cells left blank to fill them at a later stage when data is available. When you want to count the number of entries you made in a cell 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 the COUNTA function.

Using the COUNTA function in Excel

COUNTA Excel function returns the count of cells that are not empty or Non-Blank. It returns a number representing a 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 count empty cells.

The syntax of COUNTA Excel function is;

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

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

In this article, you will learn how to use the COUNTA in Excel. The 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 nonempty 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 a 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 a value.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

I'm still new to Excel, and I haven't taken any classes. This is what I have learned just from trial an error. I'm stuck with this, and I can't move forward until I figured this out, and need some assistance please. *I have figured out that If an employee is working 6hr shift and extended to 7 hrs the values change for the next cell to (Having an extra person, so its plus 1). I want it to go (Plus 1) on an 8 hr shift as well, but Im having difficulties. Can you assist if possible, let me know what you would need? =IF(AND($C$29<=13.5,$C$30<=13.5),COUNTA($B$31:$B$61)+-COUNTA(BZ31:BZ61),COUNTA($B30:$B61)+-COUNTA(BZ29:BZ61)+1*(AND($D$29>=7,$D$30>=7,+-COUNTA(BZ29:BZ61)+-(COUNTA($B30)*COUNTA($B29)*(AND($D$29<7)+-COUNTA($B30:$B53)*COUNTA(D29=8,(B29)+-(BZ29:BZ29)))))))

An Excelchat Expert solved this problem in 11 mins!