How to Count Cells that Contain Negative Numbers in Excel

In order to count the number of cells that contain a negative number within a given range the COUNTIF function is used. The formula represented in the example figure shows the cell range that includes numbers.

Count cells that contain negative numbers

Figure 1: COUNTIF Function for counting negative numbers

Basic Syntax of COUNTIF

The basic syntax or the formula used for the operation is:

=COUNTIF (range, “<0”)

In the example figure, the explanation of the formula is given as:

=COUNTIF (B5: B10, “<0”)

Here, B5: B10 represents the range from which the negative numbers have to be sorted out and “<0” means that the criteria are less than zero or negative.

Working of formula

The purpose of the COUNTIF function is to count the numbers in a range that matches specified criteria. In this example, the criteria have been specified as “<0”. This means that the criteria are less than zero. The total cells that fall into the range specified will be taken into account and returned by this function.


If there is another value that needs to be specified, it can be adjusted in the formula as shown in the following the example. Let’s assume we are counting the cells that have greater than the value of 10, so the formula becomes:

=COUNTIF (range, “>10”)

Hence the returned values will all be the ones that are greater than 10.

If there is a value that needs to be specified from another cell, then the can be adjusted by using an ampersand (&) to focus on that cell value. For example:

=COUNTIF (range, “<”& A2)

If the value in cell A2 is “-10”, the criteria itself will be set as counting of values less than -10 respectively.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment