Count cells that contain positive numbers

Generic formula 

=COUNTIF(rng,">0")

Related formulas 

Count cells between two numbers

Count cells greater than

Explanation 

To count positive numbers in a range of cells, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers.

In the example, the active cell contains this formula:

=COUNTIF(B2:B6,">0")

How this formula works

COUNTIF counts the number of cells in a range that match the supplied criteria. In this case, the criteria is supplied as ">0", which is evaluated as "values greater than zero". The total count of all cells in the range that meet this criteria is returned by the function.

You can easily adjust this formula to count cells based on other criteria. For example, to count all cells with a value greater than or equal to 100, use this formula:

=COUNTIF(rng,">=100")

Count cells that contain odd numbers

Generic formula 

=SUMPRODUCT(--(MOD(rng,2)=1))

Related formulas 

Count cells that contain numbers

Explanation 

To count cells that contain only odd numbers, you can use a formula based on the SUMPRODUCT function together with the MOD function.

In the example, the formula in cell E6 is:

=SUMPRODUCT(--(MOD(rng,2)=1))

This formula returns 4 since there are 4 odd numbers in the range B6:B11 (which is named range, "rng" in the formula).

How this formula works

The SUMPRODUCT function works directly with arrays.

One thing you can do quite easily with SUMPRODUCT is perform a test on an array using one or more criteria, then count the results.

In this case, we are running a test for an odd number, which uses the MOD function:

MOD(rng,2)=1

MOD returns a remainder after division. In this case, the divisor is 2, so MOD will return a remainder of 1 for any odd integer, and a remainder of zero for even numbers.

Inside SUMPRODUCT, this test is run on every cell in B6:B11, the result is an array of TRUE / FALSE values:

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

After we coerce the TRUE/FALSE values to numbers using the double negative, we have:

{0;1;1;1;0;1}

SUMPRODUCT then simply sums these numbers and returns 4.

Count cells that contain numbers

Generic formula 

=COUNT(rng)

Explanation 

To count the number of cells that are not blank, use the COUNT function. In the generic form of the formula (above) rng represents a range of cells.

In the example, the active cell contains this formula:

=COUNT(B4:B8)

How this formula works

The COUNT function is fully automatic. It counts the number of cells in the range that contain numbers and returns the result.

Count cells that do not contain numbers

To count the number of cells in a range that do not contain numbers, use this SUMPRODUCT formula:

=SUMPRODUCT(--NOT(ISNUMBER(B4:B8)))

Count cells that contain negative numbers

Generic formula 

=COUNTIF(rng,"<0")

Explanation 

To count the number of cells that contain negative numbers in a range of cells, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers.

In the example, the active cell contains this formula:

=COUNTIF(B2:B6,"<0")

How this formula works

COUNTIF counts the number of cells in a range that match the supplied criteria. In this case, the criteria is supplied as "<0", which is evaluated as "values less than zero". The total count of all cells in the range that meet this criteria is returned by the function.

You can easily adjust this formula to count cells based on other criteria. For example, to count all cells with a value less -10, use this formula:

=COUNTIF(rng,"<-10")

If you want to use a value in another cell as part of the criteria, use the ampersand (&) character to concatenate like this:

=COUNTIF(rng,"<"&a1)

If the value in cell a1 is "-5", the criteria will be "<-5" after concatenation.