Count cells that contain numbers

In this example, the goal is to count the number of cells in a range that contain numbers. This problem can be solved with the the COUNT function or the SUMPRODUCT function. Both methods are explained below.

COUNT function

The COUNT function counts the number of cells in a range that contain numeric values. In this example, we simply need to give COUNT the range B5:B15:

=COUNT(B5:B15) // returns 8

The COUNT function is fully automatic, so there is nothing to configure. The result is 8, since there are eight cells in the range B5:B15 that contain numeric values. Cell B9 is not included because it is empty. Cells B11 and B12 are not included because they contain text values.

Note: the COUNTA function counts numbers and text, but does not count empty cells.

SUMPRODUCT function

Another way to solve this problem is with the SUMPRODUCT function and Boolean algebra:

=SUMPRODUCT(--ISNUMBER(B5:B15))

Working from the inside out, the ISNUMBER function is used to test the values in B5:B15:

ISNUMBER(B5:B15)

Because the range B5:B15 contains 11 values, the result is an array that contains 11 TRUE and FALSE values:

{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE}

The TRUE values correspond to cells that contain numbers, and the FALSE values represent cells that do not contain numbers. To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (--):

--{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE}

The resulting array inside the SUMPRODUCT function looks like this:

=SUMPRODUCT({1;1;1;1;0;1;0;0;1;1;1}) // returns 8

With a single array to process, SUMPRODUCT sums the array and returns 8 as the result.

Note: you might wonder why you should go to the trouble of using SUMPRODUCT and Boolean logic, when the COUNT function will do the job? The main reason is flexibility and extensibility. SUMPRODUCT can easily perform calculations that the COUNT function, or even COUNTIF or COUNTIFS simply can't perform. One example is the formula below, which adds the NOT function to reverse behavior. Another basic example is this formula, which uses the ISODD function to count odd numbers.

Count cells that do not contain numbers

To count the number of cells in a range that do not contain numbers, you can modify the SUMPRODUCT formula above to use the NOT function like this:

=SUMPRODUCT(--NOT(ISNUMBER(B5:B15)))

The NOT function reverses the output from ISNUMBER, and the final array inside SUMPRODUCT looks like this:

=SUMPRODUCT({0;0;0;0;1;0;1;1;0;0;0}) // returns 3

The result is 3, since there are three cells in B5:B15 that do not contain numbers.