## 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.