Excel allows a user to count all cells that contain any value, by using the COUNTA function. The function takes in count texts, numbers, spaces, results of other function, etc. This step by step tutorial will assist all levels of Excel users in counting non-blank cells in a range.
Figure 1. The result of the COUNTA function
Syntax of the COUNTA Formula
The generic formula for the COUNTA function is:
The parameter of the COUNTA function is:
- range – a range of cells where we want to count non-blank cells.
Counting Non-Blank Cells Using the COUNTA Function
In our example, we want to count all non-blank cells in the range B3:B10. In the cell B8, we put a space. In the cell B9, there is the formula which returns a space as a result.
The formula looks like:
The parameter range is B3:B10, while in the cell D3 we want to get a result of the COUNTA function.
To apply the COUNTA function, we need to follow these steps:
- Select cell D3 and click on it
- Insert the formula:
- Press enter
Figure 2. Using the COUNTA function to count non-blank cells in the range
There are 5 names in the range and also two spaces. Because of that, the result of the function in the cell D3 is 7.
Ignoring Empty Strings When Using the COUNTA Function
The COUNTA function takes in the count all cells that are not blank. This includes spaces and empty strings returned by other function, which can’t be seen in the range. If we want to ignore these empty strings, we can use the SUMPRODUCT function instead. We will show how it works in the same example. The formula looks like:
=SUMPRODUCT(--(LEN(B3:B10) > 0))
Figure 3. Counting non-blank cells with the SUMPRODUCT function
The function checks all cells in the range if their length is greater than 0. In the end, it counts all cells which are greater than 0. As you can see, the result of the function is 5.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.