Excel WILL COUNT: Count cells that are not blank

Key information

The Excel COUNTA function allows us to count the cells of a selection of information that contain some type of data, that is, that are not blank. This is especially useful for when we are working with large databases and want to see which cells are missing information.

For more information about the function you can watch the following video:

The basics of the COUNT function in Excel

• Purpose: WILL COUNT It allows us to count cells in a selected range that present some type of data.
• Characteristics: It is a variation of the function COUNT which will count cells containing numbers, texts, logical values and errors; omitting those blank cells. If you want to count only cells with text you can go to the next article.
• Syntax:

=COUNT(value1; [value2]; …)

• Arguments:

value1 = Mandatory. First cell or selection of cells that will contain the values to be counted.

value2; … = Optional. The rest of the cells that will contain the values to be counted. There can be up to 255 extra arguments.

Counting non-blank cells with COUNTA in Excel

To use the Excel COUNTA function we only have to select the data on which we want to count how many cells have some content. This can be achieved in two ways: by selecting the sets of cells as a single argument or by selecting each cell as a different argument.

In the following example we want to count how many cells contain an “X”:

The first option is to select the entire square as a single argument:

The second option is to select each square as a different argument:

Regardless of the method, under both we will obtain the same result:

We can see that the COUNTA function counts the number of cells that are not blank, which corresponds to those that have an “X”. But as we said previously, these cells can also contain numbers, texts, logical values or errors and they will still be considered as non-blank cells. Now we replace some “X” with data of different types:

And we still get the same result for cells that are not blank:

Ninja Tip: An alternative to the Excel COUNTA function is to use the COUNT YES and use “<>” as a criterion, to indicate that we want it to count empty cells.

Ninja Tip: If we want to count blank cells, we can use the COUNTBLANK function, which works the same as COUNTA, but for blank cells.

Counting cells with at least 1 character

One problem with the Excel COUNTA function is that some cells may appear empty, but actually contain a function. To understand what we mean, let's look at the following example:

In this case we use a function YEAH to give us a blank cell if the number on the left is greater than 7 and the word “Minor” if it is not. Now if we use the COUNTA function in the “Function” column:

We see that 2 of the results are cells that are not blank, but the COUNTA function gives us the following result:

This occurs because Excel recognizes the cells as containing a function, so they do not technically count as blank cells. If we wanted to solve this we can use the function ADDITION and the function LONG:

The explanation is a bit complex, but basically what we are doing is using the LONG function to see which cells have more than one character (the “>0”), which gives us a logical value that is then converted by the double negative ( “ – -“) into a 1 or a 0. Then the SUM function gives us how many cells meet the condition. With this we obtain the following result:

We see that we now get 2 as a result, which is the true number of cells that are not blank.

Ninja Tip: A common problem you may encounter when working with databases is that instead of having blank cells, there are cells with a single character, such as a period (“.”). To solve this we can apply the method just seen, but replacing the “>0” with a “>1”. Here we have to worry that there are no other data that are useful to us that also have only one character.

​