COUNTA Function
An Excel Statistical Function that counts all the non-empty cells in a selected range.
What is the COUNTIF function?
The COUNTIF Function is categorized as a statistical function that counts the number of cells meeting a single criterion.
For example, suppose the criteria are to identify all the cells with numbers greater than 7. In that case, the function will easily identify those cells and return their count in the selected cell.
If the criteria is to identify how many cells contain similar text, let’s say Meta Platforms Inc., then the function can still identify the cells and return the number in the corresponding cell.
However, on a daily basis, you would hardly find similar numbers, text strings, or the database. One thing that is expected to be consistent, though, is the blank cells.
The COUNTIF function can also identify blank and non-blank cells and return their count. Why are such cells so important?
Well, data is everything. Why would you want to evaluate half-empty and inconsistent data sets? They may eventually lead to a chain of events that significantly affect the work.
As a result, it becomes essential to identify those blank cells or even their count, which can be accomplished with the COUNTIF function.
The syntax for the Countif Not Blank function is:
=COUNTIF(range, criteria)
where,
- range: (required) the range of cells that will be evaluated for blank and non-blank cells
- criteria: (required) the criteria that will define whether to look for blank or non-blank cells
For the function to be used efficiently, it becomes necessary that we look at the comparison operators that are used so often along with it. The different comparison operators are
| Operator | Description | Result |
|---|---|---|
| = | Checks if two numbers A & B are equal | (14 = 10) is FALSE |
| <> | Checks if two numbers A & B are not equal | (14 <> 10) is TRUE |
| > | Checks if number A is greater than number B | (14 > 10) is TRUE |
| < | Checks if number A is smaller than number B | (14 < 10) is FALSE |
| >= | Checks if number A is greater than or equal to number B | (14 >= 10 ) is TRUE |
| <= | Checks if number A is smaller than or equal to number B | (14 <= 10 ) is FALSE |
The most important operator for finding non-empty cells is the ‘<>.’
Key Takeaways
- The COUNTA function is an Excel Statistical Function that is used to count the number of cells in a range that are not empty.
- The COUNTA function is used to count the number of non-empty cells in a specified range. It counts cells that contain any type of data, including text, numbers, logical values, errors, and empty strings. The syntax for the COUNTA function typically includes one or more arguments representing the range of cells to be counted. It returns the total count of non-empty cells in the specified range.
- The COUNTA function does not typically encounter errors unless the specified range is invalid or contains cells with circular references. In such cases, it may return a #VALUE! or #REF! error indicating the nature of the problem.
How to get the count of non-blank cells in Excel?
As we iterated earlier, the non-blank cell count can be identified using the combination of the COUNTIF function and the comparison operators.
Suppose we have the data as illustrated below:
To calculate the non-blank cells in column C, we will use the formula =COUNTIF(C3:C12,"<>") in cell G5 which gives the count as 7.
Similarly, we will use the formula =COUNTIF(D3:D12,"<>"), which gives the result:
We know there are ten students, so column C is missing three values while column D is missing one.
It’s also possible to directly calculate the number of blank cells in columns C and D, respectively. We will use the formula =COUNTIF(C3:C12,"") in cell G5 and =COUNTIF(D3:D12,"") in cell G6, which gives the result as
Thus, you can easily calculate the number of blank and non-blank cells using the COUNTIF function in Excel.
COUNTA function - An alternative to COUNTIF
If the objective is to find the count of non-blank cells, then the COUNTA function can be used as an alternative.
COUNTA is categorized as a statistical function that counts all the non-empty cells in a selected range.
Unlike the COUNTIF function, which requires comparison operators and criteria to identify the non-blank cells, COUNTA can identify the count of such cells based on a single-range argument.
The syntax for the COUNTA function is:
=COUNTA(value1, [value2] …)
where,
- value1: (required) a cell or range of cells that will be evaluated for non-empty cells
- value2: (optional) a cell or range of cells that will be evaluated for non-empty cells
NOTE
The function can accept a total of 255 arguments. The function will count all the values, including text strings, numbers, errors, date & time values, and even the formula that eventually returns empty strings(“”). Only the ‘truly’ empty cells are completely ignored.
Suppose we have the data as illustrated below:
We will use the formula =COUNTA(C3:C12) in cell G5, which gives the number of non-blank cells in column C as 7.
In the same way, we will use the formula =COUNTA(D3:D12) in cell G6, which gives the result as:
Thus, calculating the number of non-blank cells becomes easier using the COUNTIF or the COUNTA function in Excel.
Let’s see another example of how the COUNTA function can return deviating results. Suppose we have the data as illustrated below:
As we can see, the entire table is empty except for the student’s name in column B. Therefore, we will use the COUNTA formula to see what count we get for the non-blank cells.
The formula will be =COUNTA(C3:C12) in cell G5 and =COUNTA(D3:D12) in cell G6, which gives the count of non-blank cells as 10 each.
Aren’t the cells empty? So why is the function still flagging them as non-empty cells?
Well, the reason is quite simple. All these non-empty cells are formulas that return empty strings as a result. Since empty strings are also identified as a value by the COUNTA function, it is counted as a non-blank cell.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?