Table of Contents
What is COUNTA Function in Excel?
The COUNTA function in MS Excel counts the number of cells that are not empty (non-blank cells) in a range. It returns the count of cells that contain text, numbers, logical values, error values, date/time and empty text (““). It returns a numeric value.
COUNTA Formula in Excel
The general excel COUNTA formula is as follows:
The COUNTA function syntax has the following arguments:
- value1: Required, represents the values that are desired to be counted
- value2: Optional, represents the values that are desired to be counted
Each argument can be a range, a cell, a value, array of values, or references to cell ranges. There can be up to a maximum of 255 arguments in MS Excel 2007 or later. Earlier versions of Excel could handle 30 arguments only.
How to Use COUNTA Function in Excel?
If we need to count the number of cells in a range or several non-adjacent ranges that are not empty, then COUNTA function is used.
The simple example would be to count cells with value in the range: B1:B50, then we use the formula: =COUNTA (B1:B50).
We can think of using Excel COUNTA function in many potential cases like:
- Count the number of customers in a list
- Count the number of transactions in a given time period
- Count number of tests submitted by students
- Count number of employees with an e-mail address
- Count number of presentations by employees, etc.
If we wish to return the number of non-blank cells in one cell range, say A2:A7: We can see that the formula returns the number of non-blank cells in the range: A2:A7.
It counts the number of cells in A2 through A7 that contain some data and returns a value of 5 because cell A5 is blank. So, all values are counted except value in cell ‘A5’ which is blank.
Now, let’s say we wish to return the number of non-blank cells in more than one supplied cell range, say A2:A7 and B2:B4: We can see that the formula returns the number of non-blank cells in two ranges: A2:A7, & B2:B4.
It counts the number of cells containing data in cells A2 through A7 and cells B2 through B4 and returns a value of 7 because cells A5 and B3 are blank. So, all values are counted except values in blank cells.
In the below example, the COUNTA function in excel returns the number of students with a grade in Maths, English & Computer: tests of IF functions as follows:
It counts the number of grades for mathematical students containing data in cells B2 to B6 and returns a value of 3.
- When values are supplied directly to COUNTA function
- Combining Range & Value Arguments
The Excel COUNTA function not only counts cells that are non-blank, but it also counts the number of value arguments provided. The value argument is a parameter that is not a cell or range of cells.
For instance, in Example 3, let’s say that the spreadsheet was missing two students named “Neha” and “Rahul”, and these students were also taking the Maths test, then the excel COUNTA function would work as follows:
We can see that the excel COUNTA formula above counts the number of cells that are not empty in the range B2:B6, and then it adds two more because of the two value arguments provided: “Neha” and “Rahul”, which makes a total count of 5.
- When values are supplied directly to COUNTA function
If we wish to return a number of non-blank values within a set of values that are supplied directly to function (like in the above example), as below:
=COUNTA(1,2,””, text, TRUE)
We can see that the formula returns the number of non-blank values out of the values that are supplied to it.
Now, let’s say we wish to return the number of non-blank cells in a contiguous rectangle, say A2 through B6, then we can specify the entire range using upper left cell address and lower right cell address to save time:
We can see that the formula counts the number of cells containing data in cells A2 through B6 and returns a value of 7 because cells A5, B3, and B5 are blank. So, all values are counted except values in blank cells.
Things to Remember about Excel COUNTA Function
- COUNTA function in excel works just like COUNT function, except that it includes all non-blank cells and not just those with numerical values.
- COUNTA function does not sum the values of cells, it only counts that they exist.
- If the arguments provided to COUNTA function are not correct, then it will return an error during run time.
- COUNTA will also count cells that visually look empty/blank, but actually, they are not and contain invisible characters or an empty string (“”) returned by a formula.
- COUNTA can also count hard-coded values. Eg: =COUNTA (“c”, 2, 4,””) returns 4.
- The only cells that COUNTA function in excel does not count are absolutely empty cells.
- COUNTA function is a built-in function in Excel categorized as a statistical function in excel.
- If the argument is a range, each cell in the range that is not empty will be counted as 1.
- If the argument is a cell and the cell is not blank, it will be counted as 1.
- If the argument is a value and not a range or cell, it will be counted as 1.
- Excel COUNTA function is counted an empty string as a value.
- If space bar is used to delete the contents of a cell, then COUNTA function in excel will count it because space is considered a value. So when deleting data from cells, delete key should be used, and not the space bar.
- If only numerical values are to be counted, the COUNT function should be used.
- If we need to count only those cells that meet certain conditions, then COUNTIF or COUNTIFS function should be used.
You can also download this COUNTA Function Excel Template here – COUNTA Function Excel Template
This has been a guide to COUNTA Function in Excel. Here we discuss how to use COUNTA Function in Excel using formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –