COUNTA Function in Excel

COUNTA Main Image

There are several COUNT functions in Microsoft Excel to count the cells that contains specific type of data in the given range or list of arguments.

The COUNTA function counts all the cells that contains any type of data. In other words, function counts non-blank cells within the supplied range.

Uses

In Excel COUNTA function is used to count if the cells are not blank  or we can say it counts of non-blank cells.

Syntax & Arguments:

=COUNTA(value1, [value2], [number3], …)

value1 – It is required and it is an item, cell references to the cell ranges, or array of values.

[value2], [value3], … [value255] – (Optional) and it can be supplied up to 255 additional values, cell references, or array of values.

Notes

  • Any cell or ranges that contains numbers, text, logical values, error values, dates, times, functions/formulas or Boolean values, are counted by the function.
  • Empty text string (“ “) or formula that returns an empty text string in the range or supplied within the list of arguments, is also counted by the function
  • Maximum 255 value arguments can be supplied in Excel 2007 and later versions. However, in Excel 2003 and earlier versions, up to 30 value arguments are handed by the function.

Example

In the following example, COUNTA function is used to count the cells that have any values in the supplied range.

COUNTA Function

COUNTA looked for non-blank cells in the above range and returned 17 as the number of cells that contains any type of values.

If you notice, in the above example, cell B10 which is an empty text string is also counted by the function.

At first look, the above range looks like having 16 non-blank cells however when the function returned the final result, it is 17. That is because, cell B10 contains empty text string.

This is a very common problem while fetching or extracting data from any software or tool, the empty text strings and unexpected characters also gets extracted along with the data. And when using COUNTA function, all these cells are also counted along with other cells. Also, most of the times it makes the file heavy which may result in slowing down the work.

You can use COUNTA function anywhere in the worksheet with any type of data. In case you want to count the cells that are blanks, you can use COUNTBLANK function.

Leave a Reply