COUNTA Function
It is categorized as a statistical function that counts all the non-empty cells in a selected range.
Tell me something that Excel cannot do. It’s crazy that Microsoft Excel is already capable of everything from pivoting huge data sets to building complicated financial models.
I often think, would something be able to replace Excel? Generally, an industry follows the rule of three, right?
One leads the way & other two follow. Excel has been leading the way for a long time, but its two followers are yet to make a case for themselves.
I mean, there are many options one can choose from - Apple Numbers, Zoho Sheets, Google Workspaces, etc. but none have been able to solidify that spot.
And if that wasn’t enough, Microsoft has now launched GPT plugins which will further automate the tedious work using the natural language prompts.
A decade down the line, the entire history of MS Excel would make a great case study as to how it shouldered the responsibility of mankind.
What this article represents is a tiny fraction of its power. I hardly believe anyone has ever witnessed Excel’s complete potential. In this article, we will see how to count the non-blank cells in Excel using comparison operators & COUNTIF function.
Key Takeaways
- The number of non-blank cells can be counted in Excel using the COUNTIF and the COUNT function.
- The COUNTIF function is a statistical function that calculates the number of ‘n’ cells fulfilling certain criteria.
- The function accepts two different arguments - the range which will be evaluated for the given criterion and the criteria upon which the range will be evaluated. The criterion can be finetuned using the different logical and comparison operators.
- To get the non-empty cells, the ‘<>’ operator is important along with the COUNTIF function.
- The COUNTA function is another function that belongs to the ‘statistical’ family and counts all the non-empty cells in a selected range.
- Contrary to COUNTIF, the COUNTA accepts a single argument: a cell or a range of cells that needs to be evaluated for non-blank cells to return their count.
- You can reference 255 different values for the COUNTA function, a cell or a range of cells to count the non-blank cells.
What is the COUNTIF function?
The COUNTIF 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 or 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 & inconsistent data sets? They may eventually lead to a chain of events affecting the work to a larger extent.
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 which will define whether to look for blank or the non-blank cells
For the function to be used efficiently, it becomes necessary that we look at the comparison operators which 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 ‘<>.’
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 while it will be =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 non-blank cell.
or Want to Sign up with your social account?