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, there are some multiple formatted values such as ‘Date values’, ‘% values, text character or so error values are stored in the range “B2” to “B8”.
Now we will apply the Excel COUNTA Function at the bottom of this dataset.
See image below with the applied COUNTA Function.
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:-
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 considers it as a value and counts it.
I Hope you find this tutorial helpful.
Please feel free to put your comments or suggestion in the comment box below.
Furthermore, details for the Excel COUNTA Function are provided on the Microsoft Office website.
Thanks
Related Useful Post