This tutorial describes the formula syntax and usage of the COUNTA function and its usages.
Excel COUNTA Function – How to Define
Excel COUNTA function is a part of COUNT family in Microsoft Excel. We apply this function to retrieve the total count of cells. Which has any kind of data or values in a cell or a range of cells. Excel COUNTA function counts all non-blank cell or in a range.
It Returns a Value
Excel COUNTA Function returns the number of all non-blank cells.
=COUNTA (value1, [value2]… [Value 255])
Arguments of COUNTA Function
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
This function counts all the cells which are not empty.
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. Excel COUNTA function will count a space also because space is also a character.
Excel COUNTA function works with integers, decimal values, DATE values. It will also not count any blank cell in the range.
If result of formula is empty, Excel COUNTA function will count that. See example:- =IF(A15=”4″, “value”, “”)
Excel COUNTA Function in a single range
See in the above image, range (A2: A8) has multiple of the data type. Such as Date, text, blank, % value, blank, non-blank, decimal point.
We apply the formula COUNTA in cell “A9” and result is 6. As we learned in this tutorial that Excel COUNTA function counts everything besides blank cells. So total visible values are 5 and two blank cell are appearing.
But the result is 6, which means there is a value in the blank cell. So we apply the (LEN) formula to check the value or length of the cell if there is any value. Cell A3 has 0 length which means it is completely blank, has no value. Cell A6 has a length as “1” which means it has one character. This is the only reason Excel COUNTA function considering it as a non-empty cell.
Excel COUNTA Function in a multiple ranges
Now come to the second example where we have to apply the COUNTA function in two ways. In the first formula, we took the entire range with one go.
And in the second formula, we apply the formula in a different method as (=COUNTA (value1, value2… and so on up to 255).
Here the result of both the formula is “13” but counting the cell values manually. There is only “12” cell has values.
In this entire range, there is two cell are appearing empty. Cell A17 has one space character in it (manually entered) and cell B19 is a blank.
Apply the same rule as we applied in the previous example. Use LEN formula to check, if the cell has a character.