Excel COUNTA Function – How to Use (with example)

In this blog post, we will learn about Excel COUNTA function. This function is a member of COUNT family of Microsoft Excel.

Explanation

In simple words, Excel COUNTA function counts every cell value which is not blank. This function counts each non-blank cell value as “1” and skips blanks.

In the previous blog post, we learned about Excel COUNT Function which counts only numeric values in a cell.

Let’s learn this function from the beginning.

It Returns a Value

Excel COUNTA Function returns the number of all non-blank cells

Syntax

=COUNTA (value1, [value2]… [Value 255])

Arguments

value1

[this is required argument] this is a cell, or range of cells, where we will apply the COUNTA function.

[value2]

(optional argument) enter up to value 255] more items, cell reference or a range where we will apply the COUNTA function.

Things to Remember

Excel COUNTA function counts all non-blank cells and skips blank.

This function will count all type of data like- Numeric values, text, #N/A error, TRUE, FALSE, #value! Error or any kind of error values.

This function will count even space, as space is also a character.

Excel COUNTA function works with integers, decimal values, DATE values too.

If the result of a formula is empty, this function will count that also. See example:- =IF(A5=”4″, “Yes”, “”).

As per the above function, if the cell “A5” value is “4”, the function displays the result as “Yes”. But if cell “A5” has any other value this function will show the result as a blank cell as described in the function.

Excel COUNTA Function consider “1” value in both cases whether is a blank value or not.

Example

In the below image, some different values are stored within a range.

Now apply COUNTA function here at the bottom of this dataset.

Excel COUNTA Function
Image _01

In the given range, there are only 5 values and two blank cells are appearing but the result of the total count is showing “6”.

Which means there is another value is existing within the blank cell but not visible.

Now we will check the length of each of the blank cell if there is any character is existing.

So, for that apply the Excel LEN Function, see below image:-

Excel COUNTA Function
Image_02

We have applied Excel LEN Function in the two blank cells as we can see in the above image.

LEN function has “0” result in cell “A3”, it means this cell has not any value or character existing here.

But in cell “A6” Excel LEN function has found the result as “1”, which means this cell has a single character.

So if even a blank space is existing within a cell, Excel COUNTA Function consider it as a value and count it.

Regards

Related Post

Excel COUNT Function

Leave a Comment

Your email address will not be published. Required fields are marked *