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.