Summary

To count the number of cells that contain negative numbers in a range, you can use the COUNTIF function. In the example shown, cell E6 contains this formula:

=COUNTIF(data,"<0")

where data is the named range B5:B15. The result is 3, since there are three cells in B5:B15 that contain numbers less than zero.

Generic formula

=COUNTIF(range,"<0")

Explanation 

In this example, the goal is to count the number of cells in a range that contain negative 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 match the 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 negative numbers in this example, we need to use the less than operator (<) with zero like this:

=COUNTIF(data,"<0") // returns 3

To include zero in the count, use the less than or equal to operator (>=):

=COUNTIF(data,"<=0") // returns 4

Notice that the criteria is enclosed in double quotes (""). COUNTIFS is in a group of eight functions that share this syntax.

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 less than zero:

data<0

Because data contains eleven cells, the result from this expression is an array that contains 11 TRUE and FALSE values:

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

To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (--):

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

The resulting array inside the SUMPRODUCT function looks like this:

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

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

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.