Excel COUNTA Function – How to Use (with example)

 

This tutorial describes the formula syntax and usage of the COUNTA function and its usages.

Excel COUNTA Function

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.

Syntax

=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”, “”)

 

Example -01

Excel COUNTA Function in a single range

Excel COUNTA Function

 

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.

 

Example -02

Excel COUNTA Function in a multiple ranges

Excel COUNTA Function

 

 

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.

 

 

Related Post

 

Excel COUNT Function, Excel COUNTIF Function

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.