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 nos permite contar celdas en un rango seleccionado que presenten algún tipo de dato.
- Characteristics: It is a variation of the function COUNT que contará las celdas que contengan números, textos, valores lógicos y errores; omitiendo aquellas celdas en blanco. Si es que quieres contar solo celdas con texto puedes ir al siguiente 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 y usar como criterio “<>”, para indicar que queremos que cuente las celdas vacías.
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: Un problema común que puedes encontrar al trabajar con bases de datos es que en vez de tener celdas en blanco, existen celdas con un solo caracter, como un punto (“.”). Para resolver esto podemos aplicar el método recién visto, pero reemplazando el “>0” por un “>1”. Aquí hay que preocuparnos de que no hayan otros datos que nos sirvan que también tengan solo un caracter.