Explanation
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 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.
Explanation
In this example, the goal is to count odd numbers in the range B5:B15, which is named data . This can be done with the SUMPRODUCT function together with the ISODD function. Instead of ISODD, the MOD function can also be used. Both approaches are explained below.
SUMPRODUCT with ISODD
The SUMPRODUCT function works directly with arrays. One thing you can do quite easily with SUMPRODUCT is perform a logical test on a range, then count the results. In this case, we want to count odd numbers, and the simplest way to do that is with the ISODD function . The ISODD function returns TRUE when given an odd number and FALSE when given an even number:
=ISODD(9)// returns FALSE
=ISODD(4)// returns TRUE
In the worksheet shown, the formula in E6 is:
=SUMPRODUCT(--ISODD(+data))
Working from the inside out, the logical test is based on the ISODD function:
ISODD(+data)
In this case, because data (B5:B15) contains 11 values, the ISODD function returns 11 results in an array like this*:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
- There are a small number of functions in Excel that will not spill when given a range of values. ISODD is one of these functions. The + operator is an easy way to make ISODD return all results.
In this array, the TRUE values correspond to cells that contain odd numbers, and the FALSE values represent cells that contain even numbers. To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (–):
--{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
The resulting array inside the SUMPRODUCT function looks like this:
=SUMPRODUCT({0;0;1;0;0;0;0;1;1;0;1}) // returns 4
With a single array to process, SUMPRODUCT sums the array and returns 4 as the result.
SUMPRODUCT with MOD
Another way to solve this problem is to use the MOD function like this:
=SUMPRODUCT(--(MOD(data,2)=1))
Here the logical test for odd numbers looks is this expression:
MOD(data,2)=1
MOD returns a remainder after division. Since, the divisor is 2, MOD will return a remainder of 1 for any odd integer, and a remainder of zero for an even integer. We therefore test for 1. Since there are 11 values in data (B5:B15), the result is an array of 11 TRUE / FALSE values:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (–):
--{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
The resulting array inside the SUMPRODUCT function looks like this:
=SUMPRODUCT({0;0;1;0;0;0;0;1;1;0;1}) // returns 4
With a single array to process, SUMPRODUCT sums the array and returns 4 as the result.
Counting even numbers
As you might guess, you can count even numbers in a range by making small adjustments to the formulas above. To adapt the ISODD version of the formula to count even numbers, simply replace the ISODD function with the ISEVEN function like this:
=SUMPRODUCT(--ISEVEN(+data)) // returns 7
To adapt the MOD function version of the formula, adjust the logic to check for zero instead of 1:
=SUMPRODUCT(--(MOD(data,2)=0)) // returns 7
The behavior of these formulas is the same as described previously, and both formulas return 7, since the range B5:B15 contains 7 even numbers.
