# Count cells that contain negative numbers

=COUNTIF(rng,"<0")

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")

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.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.