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.
Explanation
In this example, the goal is to count the number of cells in a range that contain positive numbers. For convenience, the range B5:B15 is named data . This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below.
COUNTIF function
The COUNT function counts the number of cells in a range that meet supplied criteria. For example, you can use COUNTIF like this:
=COUNTIF(range,"red") // count cells equal to "red"
=COUNTIF(range,100) // count cells equal to 100
=COUNTIF(range,">10") // count cells greater than 10
To count positive numbers in this example, we need to use the “greater than” operator (>) together with a zero like this:
=COUNTIF(data,">0") // returns 7
The result is 7, since seven cells in B5:B15 contain numbers greater than zero. Note that cell B9 is excluded since it is equal to zero. Also notice that we supply the criteria in double quotes (">0"). COUNTIF is part of a group of Excel functions that split logical expressions into two parts. As a result, when the criteria argument includes logical operators , it must be entered as text surrounded by double quotes (").
Positive including zero
To include zero in the count, use the “greater than or equal to” operator (>=):
=COUNTIF(data,">=0") // returns 8
The result is 8, one more than the formula above since cell B9 is now included in the count.
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and Boolean algebra :
=SUMPRODUCT(--(data>0))
Working from the inside out, this expression checks if values in data (B5:B15) are greater than zero:
data>0
Because data contains eleven cells, the result from this expression is an array that contains 11 TRUE and FALSE values:
{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}
To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (–):
--{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}
The resulting array inside the SUMPRODUCT function looks like this:
=SUMPRODUCT({1;1;1;0;0;1;0;1;0;1;1}) // returns 7
With a single array to process, SUMPRODUCT sums the array and returns 7 as the result. To include zero, adjust the logical test like this:
=SUMPRODUCT(--(data>=0)) // returns 8
The result is now 8 since cell B9 is included in the count.
