In a given range, we might only be interested in counting those cells that are not blank, while ignoring the blank ones. In such a case, we need the excel COUNTA function.
The range here specifies the column or row where the cells that are not blank are located.
The excel COUNTA function helps us count those cells that contain any form of information, including the error values or empty texts. The key thing here is to note that we are not counting blank cells.
An example of an empty text is where we enter a formula which returns an empty string. In such a case, the COUNTA function will count it as a non-empty cell.
Example 1: To count the total number of students in a class
Figure 1: How to count cells that are not blank in excel
We want to use the excel COUNTA function to count the number of cells that are not blank in the above datasheet. To do this, proceed as follows:
- Specify where you want the answer should be put;
- Insert the COUNTA function.
Figure 2: How to count cells that are not blank in excel
Then press “Enter”
This will give you the answer below:
Figure 3: How to count cells that are not blank in excel
As you can see, the COUNTA function returns 9, while ignoring all the blank cells within the range.
The use of Excel COUNTA function is essential, as it can help you get the number of cells that are not empty in a quick and easy way.
What if you want to avoid empty strings returned by formulas?
It can be inconveniencing is you use the COUNTA function then get all the cells that are non-blank, even if they contain empty strings returned by formulas. If you want to count those cells that contain empty strings, you can use the Excels SUMPRODUCT function. The syntax for the SUMPRODUCT function is as below:
The LEN function will return a character count of all the cells in the specified range.